Задача Дано: CREATE TABLE profile ( id BIGSERIAL PRIMARY KEY, nickname VARCHAR, registered_at TIMESTAMP ); CREATE TABLE post ( id BIGSERIAL PRIMARY KEY, owner_id BIGINT REFERENCES profile (id), body TEXT, inserted_at TIMESTAMP, likes_count INT -- кол-во лайков ); CREATE TABLE subscription_count ( profile_id BIGINT REFERENCES profile (id) UNIQUE, followers_count INT, -- кол-во подписчиков following_count INT -- кол-во подписок ); Требуется: select p.id, sc.followers_count from post p join subscription_count sc on sc.profile_id = p.owner_id where sc.followers_count > 500 -- Индекс:create index on post (owner_id) -- Запрос:select * from post where likes_count > ? AND owner_id = ?;

This task involves three tables: profile, post, and subscription_count.

Query:

select p.id, sc.followers_count
from post p
join subscription_count sc on sc.profile_id = p.owner_id
where sc.followers_count > 500

This query selects posts whose owners have more than 500 followers.

Index:

create index on post (owner_id);

This speeds up searching for posts by owner_id.

However, if the query:

select * from post where likes_count > ? AND owner_id = ?;

is performed frequently, it is advisable to create a composite index on (owner_id, likes_count) to accelerate filtering on both fields:

create index idx_post_owner_likes on post (owner_id, likes_count);

This will allow efficient use of the index for queries filtering by owner_id and likes_count.

Also, ensure that there is an index on profile_id in the subscription_count table (a UNIQUE constraint already implies an index), which will speed up joins.

Summary:

• An index on post.owner_id already exists to speed up join queries.
• For filtering by likes_count and owner_id, it is better to create a composite index (owner_id, likes_count).

This will improve the performance of the specified queries.