Optimise many-to-many join I have three tables: posts and tags and posts_tags which forms a many-to-many relationship between posts and tags.

Having a tag with milions of posts. How could I effectively retrieve the first 10 posts ordered by its name which contain the tag?

Schema:

CREATE TABLE posts (
    id          SERIAL PRIMARY KEY,
    post_time   TIMESTAMP
);

CREATE TABLE tags (
    id          SERIAL PRIMARY KEY,
    name        TEXT
);

CREATE TABLE posts_tags (
    post_id     INT REFERENCES posts(id),
    tag_id      INT REFERENCES tags(id)
);


I tried the query below but it's too slow.

WITH tag_id AS (SELECT id FROM tags WHERE name = 'tag_which_has_milions_of_posts'),
    post_ids AS (SELECT post_id FROM posts_tags WHERE tag_id = (SELECT id FROM tag_id)) 
SELECT * FROM posts WHERE id = ANY(SELECT post_id FROM post_ids)
ORDER BY post_time DESC LIMIT 10;




UPDATE:

I've change "post_time" to "name" to make it easier to generate test data.

Full schema (including indexes and test data):

CREATE TABLE tags (
    id          SERIAL PRIMARY KEY,
    name        TEXT
);

CREATE TABLE posts (
    id          SERIAL PRIMARY KEY,
    name        TEXT
);

CREATE TABLE posts_tags (
    tag_id    INT REFERENCES tags(id),
    post_id   INT REFERENCES posts(id)
);

CREATE INDEX ON posts(name);
CREATE INDEX ON tags(name);
CREATE INDEX ON posts_tags(post_id);
CREATE INDEX ON posts_tags(tag_id);

INSERT INTO posts (name) SELECT generate_series(1, 1000000)::TEXT;
INSERT INTO tags (name) SELECT generate_series(1, 1000000)::TEXT;
INSERT INTO posts_tags (tag_id, post_id) SELECT generate_series(1, 1000000), generate_series(1, 1000000);
INSERT INTO posts_tags (tag_id, post_id) SELECT 1, generate_series(2, 1000000);
INSERT INTO posts_tags (tag_id, post_id) SELECT 2, generate_series(400000, 600000);


Query (from McNets's answers):

select posts.* from posts_tags pt 
    inner join tags on tags.id = pt.tag_id 
    inner join posts on posts.id = pt.post_id 
where tags.name = '2' 
order by posts.name desc 
limit 10;


Query plan:

Limit  (cost=288.72..288.73 rows=2 width=10) (actual time=893.348..893.349 rows=10 loops=1)
-&gt;  Sort  (cost=288.72..288.73 rows=2 width=10) (actual time=893.346..893.348 rows=10 loops=1)
        Sort Key: posts.name DESC
        Sort Method: top-N heapsort  Memory: 25kB
        -&gt;  Nested Loop  (cost=1.28..288.71 rows=2 width=10) (actual time=0.061..694.762 rows=200002 loops=1)
            -&gt;  Nested Loop  (cost=0.85..287.78 rows=2 width=4) (actual time=0.051..78.175 rows=200002 loops=1)
                    -&gt;  Index Scan using tags_name_idx on tags  (cost=0.42..8.44 rows=1 width=4) (actual time=0.038..0.038 rows=1 loops=1)
                        Index Cond: (name = '2'::text)
                    -&gt;  Index Scan using posts_tags_tag_id_idx on posts_tags pt  (cost=0.43..278.44 rows=89 width=8) (actual time=0.009..54.474 rows=200002 loops=1)
                        Index Cond: (tag_id = tags.id)
            -&gt;  Index Scan using posts_pkey on posts  (cost=0.42..0.46 rows=1 width=10) (actual time=0.002..0.003 rows=1 loops=200002)
                    Index Cond: (id = pt.post_id)
Planning time: 0.562 ms
Execution time: 893.428 ms
(14 rows)
Has a standard join structure less performance?

SELECT posts.*
FROM posts_tags pt
     inner join tags ON tags.id = pt.tags_id
     inner join posts ON posts.id = pt.post_id
WHERE tags.name = 'some tag'
ORDER by posts.name DESC
LIMIT 10;