I have this query:

SELECT  "table_1".* FROM "table_1"  
WHERE "table_1"."deleted_at" IS NULL 
  AND "table_1"."table_2_id" = 15797104  
ORDER BY "table_1"."id" ASC LIMIT 1

The explain for the query looks like:

Limit  (cost=0.57..1208.61 rows=1 width=48) 
    ->  Index Scan using table_1_pkey on table_1  (cost=0.57..46047923.71 rows=38118 width=48)
            Filter: ((deleted_at IS NULL) AND (table_2_id = 15797104))

This is incredibly slow as this table is absolutely huge.

Now, if I increase the LIMIT to 10:

SELECT  "table_1".* FROM "table_1"  
WHERE "table_1"."deleted_at" IS NULL 
  AND "table_1"."table_2_id" = 15797104  
ORDER BY "table_1"."id" ASC LIMIT 10

This query runs a lot faster (from over 30minutes to less than a second) and the query plan looks like this:

Limit  (cost=11044.69..11044.71 rows=10 width=48)
    ->  Sort  (cost=11044.69..11139.98 rows=38118 width=48)"
        Sort Key: id
        ->  Index Scan using index_table_1_on_table_2_id on table_1  (cost=0.57..10220.97 rows=38118 width=48)
              Index Cond: (table_2_id = 15797104)
              Filter: (deleted_at IS NULL)

Note that instead of using the table_1_pkey index, it uses index_table_1_on_table_2_id. This makes much more sense. I imagine Postgresql is using the table_1_pkey to avoid doing a sort operation, but that actually ends up being a lot slower.

SELECT  count("table_1".*) FROM "table_1"  
WHERE "table_1"."deleted_at" IS NULL 
  AND "table_1"."table_2_id" = 15797104

returns 5000. And the whole table_1 table contains over 200M rows.

Is there a way to trick Posrgres into using the second query plan on the first query?

migrated from stackoverflow.com Aug 11 '14 at 0:40

This question came from our site for professional and enthusiast programmers.


Generally this kind of problem occurs because the query planner thinks it will find the one row you're looking within the first 1% of the table, but it doesn't. One solution for this can be to increase the stats collection on those columns, with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 5000.

However, in this particular case, I think simply changing the table_2_id index to a partial index will cause the query planner to favor it:

CREATE INDEX table_2_not_deleted ON table_1 ( table_2_id ) WHERE deleted_at IS NULL;

The query planner tends to favor partial indexes which match portions of the WHERE clause exactly. If that doesn't work, I'd try creating an index which covers everything:

CREATE INDEX problem_solver_dex
ON table_1 ( table_2_id, id )
WHERE deleted_at IS NULL

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy