CODEWITHSUNDEEP
ruby-on-railsperformancepostgresqlpostgresql-performance
goddamnyouryan
goddamnyouryan

Reputation: 6896

Postgres using slower index with small limit

I have an interesting conundrum.

I have a few different queries that slow down significantly in certain situations.

This one is fast:

SELECT
  "posts".*
FROM "posts"
WHERE "posts"."source_id" IN (29949, 29952, 29950, 33642, 33626, 33627, 33625)
AND "posts"."deleted_at" IS NULL
AND "posts"."rejected_at" IS NULL
ORDER BY POSITION ASC, external_created_at DESC;
LIMIT 100
OFFSET 0

This one is slow:

SELECT
  "posts".*
FROM "posts"
WHERE "posts"."source_id" IN (29949, 29952, 29950, 33642, 33626, 33627, 33625)
AND "posts"."deleted_at" IS NULL
AND "posts"."rejected_at" IS NULL
ORDER BY POSITION ASC, external_created_at DESC;
LIMIT 5
OFFSET 0

The only difference here is the limit.

The weirdest part is that a very similar query to #2 is fast:

SELECT
  "posts".*
FROM "posts"
WHERE "posts"."source_id" IN (5868, 5867)
AND "posts"."deleted_at" IS NULL
AND "posts"."rejected_at" IS NULL
ORDER BY POSITION ASC, external_created_at DESC;
LIMIT 100
OFFSET 0

Which is just looking through a smaller range of source_ids

Here are the query plans for all three:

EXPLAIN ANALYZE SELECT  "posts".* FROM "posts"  WHERE "posts"."source_id" IN (29949, 29952, 29950, 33642, 33626, 33627, 33625) AND "posts"."deleted_at" IS NULL AND "posts"."rejected_at" IS NULL  ORDER BY POSITION ASC, external_created_at DESC LIMIT 100 OFFSET 0;
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=36900.88..36901.13 rows=100 width=1051) (actual time=104.564..104.570 rows=28 loops=1)
   ->  Sort  (cost=36900.88..36926.01 rows=10052 width=1051) (actual time=104.559..104.563 rows=28 loops=1)
         Sort Key: "position", external_created_at
         Sort Method: quicksort  Memory: 53kB
         ->  Index Scan using index_posts_on_source_id on posts  (cost=0.44..36516.70 rows=10052 width=1051) (actual time=9.724..102.885 rows=28 loops=1)
               Index Cond: (source_id = ANY ('{29949,29952,29950,33642,33626,33627,33625}'::integer[]))
               Filter: ((deleted_at IS NULL) AND (rejected_at IS NULL))
               Rows Removed by Filter: 1737
 Total runtime: 105.774 ms


EXPLAIN ANALYZE SELECT  "posts".* FROM "posts"  WHERE "posts"."source_id" IN (29949, 29952, 29950, 33642, 33626, 33627, 33625) AND "posts"."deleted_at" IS NULL AND "posts"."rejected_at" IS NULL  ORDER BY POSITION ASC, external_created_at DESC LIMIT 5 OFFSET 0;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..18788.72 rows=5 width=1051) (actual time=79611.044..314266.666 rows=5 loops=1)
   ->  Index Scan using index_posts_on_position_and_external_created_at on posts  (cost=0.56..37771717.36 rows=10052 width=1051) (actual time=79610.677..314266.292 rows=5 loops=1)
         Filter: ((deleted_at IS NULL) AND (rejected_at IS NULL) AND (source_id = ANY ('{29949,29952,29950,33642,33626,33627,33625}'::integer[])))
         Rows Removed by Filter: 3665332
 Total runtime: 314269.266 ms


EXPLAIN ANALYZE SELECT  "posts".* FROM "posts"  WHERE "posts"."source_id" IN (5868, 5867) AND "posts"."deleted_at" IS NULL AND "posts"."rejected_at" IS NULL ORDER BY POSITION ASC, external_created_at DESC LIMIT 100 OFFSET 0;
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10587.37..10587.62 rows=100 width=1051) (actual time=1017.476..1017.498 rows=100 loops=1)
   ->  Sort  (cost=10587.37..10594.55 rows=2872 width=1051) (actual time=1017.474..1017.483 rows=100 loops=1)
         Sort Key: "position", external_created_at
         Sort Method: top-N heapsort  Memory: 112kB
         ->  Index Scan using index_posts_on_source_id on posts  (cost=0.44..10477.60 rows=2872 width=1051) (actual time=2.823..999.417 rows=4334 loops=1)
               Index Cond: (source_id = ANY ('{5868,5867}'::integer[]))
               Filter: ((deleted_at IS NULL) AND (rejected_at IS NULL))
               Rows Removed by Filter: 39
 Total runtime: 1017.669 ms

Here are my index definitions:

"posts_pkey" PRIMARY KEY, btree (id)
"index_posts_on_deleted_at" btree (deleted_at)
"index_posts_on_external_created_at" btree (external_created_at)
"index_posts_on_external_id" btree (external_id)
"index_posts_on_position" btree ("position")
"index_posts_on_position_and_external_created_at" btree ("position", external_created_at DESC)
"index_posts_on_rejected_at" btree (rejected_at)
"index_posts_on_source_id" btree (source_id)

I'm running Postgres version: 9.3.4

Why is the slow one using Index Scan using index_posts_on_position_and_external_created_at on posts when the other two are using Index Scan using index_posts_on_source_id on posts? And how can I fix it?

Upvotes: 0

Views: 90

Answers (1)

e4c5
e4c5

Reputation: 53734

Bit of a late answer, in case you still have this problem, why not simply drop index_posts_on_position_and_external_created_at? As you've stated, trouble arises when this particular index is used by the query planner.

You already have these two indexes:

"index_posts_on_external_created_at" btree (external_created_at)
"index_posts_on_position" btree ("position")

These two make the index_posts_on_position_and_external_created_at pretty much redundant because postgresql can use more than one index on a given query. If you are concerned about sort performance, you can add ordering to index_posts_on_external_created_at

Upvotes: 1

Related Questions