Efficient querying/indexing in Postgres for WHERE IN (...) and ORDER BY

Creating a test database...

CREATE TABLE posts( classroom_id INT NOT NULL, date FLOAT NOT NULL, foo TEXT );
INSERT INTO posts SELECT random()*100, random() FROM generate_series( 1,1500000 );
CREATE INDEX posts_cd ON posts( classroom_id, date );
CREATE INDEX posts_date ON posts( date );
VACUUM ANALYZE posts;

Note the "foo" column is there to avoid an index-only scan on posts which would be very fast on this test setup which only contains indexed columns classroom_id,date but would be useless for you since you will select other columns also.

If you have an index on date that you use for other things, like displaying the most recent posts for all classrooms, then you can use it here too:

EXPLAIN ANALYZE SELECT * FROM posts WHERE posts.classroom_id IN (1,2,6)
ORDER BY date desc LIMIT 30;
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.29..55.67 rows=30 width=44) (actual time=0.040..0.983 rows=30 loops=1)
   ->  Index Scan Backward using posts_date on posts  (cost=0.29..5447.29 rows=2951 width=44) (actual time=0.039..0.978 rows=30 loops=1)
         Filter: (classroom_id = ANY ('{1,2,6}'::integer[]))
         Rows Removed by Filter: 916
 Planning time: 0.117 ms
 Execution time: 1.008 ms

This one is a bit risky since the condition on classroom is not indexed: since it will scan the date index backwards, if many classrooms that are excluded by the WHERE condition have recent posts it may have to skip lots of rows in the index before finding the requested rows. My test data distribution is random, but this query may have different performance if your data distribution is different.

Now, without the index on date.

                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10922.61..10922.69 rows=30 width=44) (actual time=41.038..41.049 rows=30 loops=1)
   ->  Sort  (cost=10922.61..11028.44 rows=42331 width=44) (actual time=41.036..41.040 rows=30 loops=1)
         Sort Key: date DESC
         Sort Method: top-N heapsort  Memory: 26kB
         ->  Bitmap Heap Scan on posts  (cost=981.34..9672.39 rows=42331 width=44) (actual time=10.275..33.056 rows=44902 loops=1)
               Recheck Cond: (classroom_id = ANY ('{1,2,6}'::integer[]))
               Heap Blocks: exact=8069
               ->  Bitmap Index Scan on posts_cd  (cost=0.00..970.76 rows=42331 width=0) (actual time=8.613..8.613 rows=44902 loops=1)
                     Index Cond: (classroom_id = ANY ('{1,2,6}'::integer[]))
 Planning time: 0.145 ms
 Execution time: 41.086 ms

Note I've adjusted the number of rows in the table so the bitmap scan finds about the same number as yours.

It's the same plan you had, including the Top-N heapsort which is much faster than a complete sort (and uses a lot less memory):

One more side question: in the explain+analyze, why does the sort node take so little time?

Basically what it does is only keep the top N rows in the heapsort buffer since the rest will be discarded by the LIMIT anyway, so it doesn't have to sort everything. As the rows are fetched they are pushed into the heapsort buffer (or discarded if they would be discarded by the LIMIT anyway). So the sort doesn't happen as a separate step after the data to be sorted is gathered, instead it happens while the data is gathered, which is why it takes the same time as retrieving the data.

Now, my query is a lot faster than yours, while they use the same plan. Several reasons could explain this, for example I run it on a SSD which is fast. But I think the most likely explanation is that your posts table probably contains ... posts ... which means large-ish TEXT data. This means a lot of data will have to be fetched, then discarded, keeping only 30 rows. In order to test this I just did:

UPDATE posts SET foo= 992 bytes of text
VACUUM ANALYZE posts;

...and the query is much slower, 360ms, and it says:

Heap Blocks: exact=41046

So that's probably your problem. In order to solve it, the query should not fetch large amounts of data then discard them, which means we're going to use the primary key... you must have one already but I forgot it, so here it is.

ALTER TABLE posts ADD post_id SERIAL PRIMARY KEY;
VACUUM ANALYZE posts;
DROP INDEX posts_cd;
CREATE INDEX posts_cdi ON posts( classroom_id, date, post_id );

I add the PK to the index, and drop the previous index, because I want an index-only scan in order to avoid fetching all the data from the table. Scanning only the index involves much less data since it doesn't contain the actual posts. Of course, we only get the PKs, so we have to JOIN back to the main table to get the posts, but that happens only after all the filtering is done, so it's only 30 rows.

EXPLAIN ANALYZE SELECT p.* FROM posts p 
JOIN (SELECT post_id FROM posts WHERE posts.classroom_id IN (1,2,6)
ORDER BY date desc LIMIT 30) pids USING (post_id)
ORDER BY date desc LIMIT 30;

 Limit  (cost=3212.05..3212.12 rows=30 width=1012) (actual time=38.410..38.421 rows=30 loops=1)
   ->  Sort  (cost=3212.05..3212.12 rows=30 width=1012) (actual time=38.410..38.419 rows=30 loops=1)
         Sort Key: p.date DESC
         Sort Method: quicksort  Memory: 85kB
         ->  Nested Loop  (cost=2957.71..3211.31 rows=30 width=1012) (actual time=38.108..38.329 rows=30 loops=1)
               ->  Limit  (cost=2957.29..2957.36 rows=30 width=12) (actual time=38.092..38.105 rows=30 loops=1)
                     ->  Sort  (cost=2957.29..3067.84 rows=44223 width=12) (actual time=38.092..38.104 rows=30 loops=1)
                           Sort Key: posts.date DESC
                           Sort Method: top-N heapsort  Memory: 26kB
                           ->  Index Only Scan using posts_cdi on posts  (cost=0.43..1651.19 rows=44223 width=12) (actual time=0.023..22.186 rows=44902 loops=1)
                                 Index Cond: (classroom_id = ANY ('{1,2,6}'::integer[]))
                                 Heap Fetches: 0
               ->  Index Scan using posts_pkey on posts p  (cost=0.43..8.45 rows=1 width=1012) (actual time=0.006..0.006 rows=1 loops=30)
                     Index Cond: (post_id = posts.post_id)
 Planning time: 0.305 ms
 Execution time: 38.468 ms

OK. Much faster now. This trick is pretty useful: when the table contains lots of data, or even lots of columns, that will have to be lugged around inside the query engine then filtered and most of it discarded, sometimes it is faster to do the filtering and sorting on only the few small columns that are actually used, then fetching the rest of the data only for the rows that remain after the filtering is done. Sometimes it is worth it to split the table in two even, with the columns used for filtering and sorting in one table, and all the rest in the other table.

To go even faster we can make the query ugly:

SELECT p.* FROM posts p
    JOIN (
      SELECT * FROM (SELECT post_id, date FROM posts WHERE posts.classroom_id=1 ORDER BY date desc LIMIT 30) a
      UNION ALL
      SELECT * FROM (SELECT post_id, date FROM posts WHERE posts.classroom_id=2 ORDER BY date desc LIMIT 30) b
      UNION ALL
      SELECT * FROM (SELECT post_id, date FROM posts WHERE posts.classroom_id=3 ORDER BY date desc LIMIT 30) c
      ORDER BY date desc LIMIT 30
    ) q USING (post_id)
    ORDER BY date desc LIMIT 30;

This exploits the fact that, if there is only one classroom_id in the WHERE condition, then postgres will use index scan backward on (classroom_id,date) directly. And since I've added post_id to it, it doesn't even need to touch the table. And since the three selects in the union have the same sort order, it combines them with a merge, which means it doesn't even need to sort or even fetch the rows that ate cut off by the outer LIMIT 30.

 Limit  (cost=257.97..258.05 rows=30 width=1012) (actual time=0.357..0.367 rows=30 loops=1)
   ->  Sort  (cost=257.97..258.05 rows=30 width=1012) (actual time=0.356..0.364 rows=30 loops=1)
         Sort Key: p.date DESC
         Sort Method: quicksort  Memory: 85kB
         ->  Nested Loop  (cost=1.73..257.23 rows=30 width=1012) (actual time=0.063..0.319 rows=30 loops=1)
               ->  Limit  (cost=1.31..3.28 rows=30 width=12) (actual time=0.050..0.085 rows=30 loops=1)
                     ->  Merge Append  (cost=1.31..7.24 rows=90 width=12) (actual time=0.049..0.081 rows=30 loops=1)
                           Sort Key: posts.date DESC
                           ->  Limit  (cost=0.43..1.56 rows=30 width=12) (actual time=0.024..0.032 rows=12 loops=1)
                                 ->  Index Only Scan Backward using posts_cdi on posts  (cost=0.43..531.81 rows=14136 width=12) (actual time=0.024..0.029 rows=12 loops=1)
                                       Index Cond: (classroom_id = 1)
                                       Heap Fetches: 0
                           ->  Limit  (cost=0.43..1.55 rows=30 width=12) (actual time=0.018..0.024 rows=9 loops=1)
                                 ->  Index Only Scan Backward using posts_cdi on posts posts_1  (cost=0.43..599.55 rows=15950 width=12) (actual time=0.017..0.023 rows=9 loops=1)
                                       Index Cond: (classroom_id = 2)
                                       Heap Fetches: 0
                           ->  Limit  (cost=0.43..1.56 rows=30 width=12) (actual time=0.006..0.014 rows=11 loops=1)
                                 ->  Index Only Scan Backward using posts_cdi on posts posts_2  (cost=0.43..531.81 rows=14136 width=12) (actual time=0.006..0.014 rows=11 loops=1)
                                       Index Cond: (classroom_id = 3)
                                       Heap Fetches: 0
               ->  Index Scan using posts_pkey on posts p  (cost=0.43..8.45 rows=1 width=1012) (actual time=0.006..0.007 rows=1 loops=30)
                     Index Cond: (post_id = posts.post_id)
 Planning time: 0.445 ms
 Execution time: 0.432 ms

The resulting speedup is pretty ridiculous. I think this should work.

To facilitate this, I tried adding a new index on [classroom_id, date DESC, created_at DESC], but the planner chose not to use it. Is Postgres just not quite clever enough to use the shortcut I describe?

It is just not clever enough. You could write it out explicitly to get the execution you envision. It is ugly, but it should be effective:

(SELECT * FROM posts WHERE classroom_id = 6691 ORDER BY date desc, created_at desc LIMIT 30)
union all
(SELECT * FROM posts WHERE classroom_id = 6693 ORDER BY date desc, created_at desc LIMIT 30)
union all
(SELECT * FROM posts WHERE classroom_id = 6695 ORDER BY date desc, created_at desc LIMIT 30)
union all
(SELECT * FROM posts WHERE classroom_id = 6697 ORDER BY date desc, created_at desc LIMIT 30)
order by date desc, created_at desc LIMIT 30;

One more side question: in the explain+analyze, why does the sort node take so little time? I would expect the sorting to be fairly slow/expensive.

CPUs are very fast, and 40,000 rows is just not very many. Unlike CPUs however, your storage is not nearly as fast, and stomping all over a very large table to collect 40,000 rows takes a lot of time. There are all kinds of ways try to address this (other than fixing the planner or rewriting your query). Get faster primary storage or more caching. Get it to use an index-only scan (is selecting * really necessary?), clustering or partitioning the table on classroom_id so that rows of the same classroom are located together, etc.

If you don't want to rearrange your data or change your hardware or rewrite your query, then maybe the simplest thing to try would be just to build an index on (date, created_at), which might lead to another plan which is less good than the perfect plan, but much better than the current plan. It could use the index to walk the data in already-ordered order, collecting rows which meet the IN condition until it has collected 30.