Slow query when adding additional where clause Here's my setup (Postgres 9.3)

Posts


project_id


Messages


post_id
kind
updated_at


I'm trying to get the most recently updated 100 messages that belong to posts (on a specific project) that are a specific kind. My query looks like:

SELECT "messages".* FROM "messages" 
INNER JOIN "posts" ON "posts"."id" = "messages"."post_id" 
WHERE "posts"."project_id" = '418fdd03-ab90-4efd-b04d-5d5563d58972' AND "messages"."kind" IN (10, 11, 12) 
ORDER BY updated_at DESC LIMIT 100 OFFSET 0;

Limit  (cost=0.17..2372.06 rows=100 width=648) (actual time=17.731..308.603 rows=100 loops=1)
 -&gt;  Nested Loop  (cost=0.17..44164.77 rows=1862 width=648) (actual time=17.730..308.559 rows=100 loops=1)
       -&gt;  Index Scan Backward using index_messages_on_updated_at on messages  (cost=0.08..20924.10 rows=151337 width=648) (actual time=0.034..93.817 rows=83481 loops=1)
             Filter: (kind = ANY ('{10,11,12}'::integer[]))
             Rows Removed by Filter: 130238
       -&gt;  Index Scan using posts_pkey on posts  (cost=0.08..0.15 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=83481)
             Index Cond: (id = messages.post_id)
             Filter: (project_id = '418fdd03-ab90-4efd-b04d-5d5563d58972'::uuid)
             Rows Removed by Filter: 1
Total runtime: 308.660 ms


If I run without the kind clause:

SELECT "messages".* FROM "messages" 
INNER JOIN "posts" ON "posts"."id" = "messages"."post_id" 
WHERE "posts"."project_id" = '418fdd03-ab90-4efd-b04d-5d5563d58972' 
ORDER BY updated_at DESC LIMIT 100 OFFSET 0;

Limit  (cost=0.17..1320.02 rows=100 width=648) (actual time=0.501..23.613 rows=100 loops=1)
 -&gt;  Nested Loop  (cost=0.17..69371.34 rows=5256 width=648) (actual time=0.500..23.583 rows=100 loops=1)
       -&gt;  Index Scan Backward using index_messages_on_updated_at on messages  (cost=0.08..20603.62 rows=427305 width=648) (actual time=0.010..3.256 rows=7893 loops=1)
       -&gt;  Index Scan using posts_pkey on posts  (cost=0.08..0.11 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=7893)
             Index Cond: (id = messages.post_id)
             Filter: (project_id = '418fdd03-ab90-4efd-b04d-5d5563d58972'::uuid)
             Rows Removed by Filter: 1
Total runtime: 23.667 ms


I think the main slowdown is that the majority of the rows are not of the kind I'm looking for. I tried adding a partial index on kind, but that didn't have any effect.

CREATE INDEX index_messages_on_kind ON messages USING btree (kind) WHERE (kind = ANY (ARRAY[10, 11, 12]));


Any ideas to speed this query up?

Edit:

Indexes that currently exist:

Posts


index on project_id and updated_at


Messages


indexes on post_id, updated_at and partial index on kind IN (10, 11, 12)
@Joishi already provided an explanation for what you saw.

Here is a solution to make it fast.

Your query (unchanged) after trimming some noise:

SELECT m.*
FROM   posts    p
JOIN   messages m ON m.post_id = p.id
WHERE  p.project_id = '418fdd03-ab90-4efd-b04d-5d5563d58972'
AND    m.kind = ANY ('{10,11,12}')
ORDER  BY m.updated_at DESC
LIMIT  100
OFFSET 0;

1. Multicolumn index on posts

This is only a minor improvement and not strictly necessary. Should give you an Index Only Scan instead of an Index Scan. Saves the lookup on the heap (the table)

 CREATE UNIQUE INDEX index_posts ON posts (project_id, id);


UNIQUE is also not necessary, but it better documents that the index is, in fact, unique.

2. Partial multicolumn index on messages done right

This is the major part.


Use the same predicate in the partial index and in the query. Postgres only applies very rudimentary tests to determine whether an partial index is applicable. Else, the cost would get out of hand quickly.
Since kind IN (10, 11, 12) is rewritten to kind = ANY ('{10,11,12}'::integer[]) internally, it should still work in this case, but it's better to be clear.
It would be useless to make kind the indexed column here. It's only relevant in the predicate, not as column to retrieve from the index. You need an equality check on post_id (so it comes first) and a range check on  updated_at (so it comes after that). Details:


Multicolumn index and performance


DESC is not strictly necessary, but still better here.


-- DROP INDEX index_messages_on_kind;
CREATE INDEX index_messages_on_kind ON messages (post_id, updated_at DESC)
WHERE kind = ANY ('{10,11,12}');

Then run ANALYZE on both tables and try EXPLAIN ANALYZE again. Are we fast, yet?