Postgres weird query plan when the number of records less than "limit"

Question

I have a query that is very fast for large date filter

EXPLAIN ANALYZE
SELECT "advertisings"."id",
       "advertisings"."page_id",
       "advertisings"."page_name",
       "advertisings"."created_at",
       "posts"."image_url",
       "posts"."thumbnail_url",
       "posts"."post_content",
       "posts"."like_count"
FROM "advertisings"
  INNER JOIN "posts" ON "advertisings"."post_id" = "posts"."id"
WHERE "advertisings"."created_at" >= '2020-01-01T00:00:00Z'
AND   "advertisings"."created_at" < '2020-12-02T23:59:59Z'
ORDER BY "like_count" DESC LIMIT 20

And the query plan is:

Limit  (cost=0.85..20.13 rows=20 width=552) (actual time=0.026..0.173 rows=20 loops=1)
  ->  Nested Loop  (cost=0.85..951662.55 rows=987279 width=552) (actual time=0.025..0.169 rows=20 loops=1)
        ->  Index Scan using posts_like_count_idx on posts  (cost=0.43..378991.65 rows=1053015 width=504) (actual time=0.013..0.039 rows=20 loops=1)
        ->  Index Scan using advertisings_post_id_index on advertisings  (cost=0.43..0.53 rows=1 width=52) (actual time=0.005..0.006 rows=1 loops=20)
              Index Cond: (post_id = posts.id)
              Filter: ((created_at >= '2020-01-01 00:00:00'::timestamp without time zone) AND (created_at < '2020-12-02 23:59:59'::timestamp without time zone))
Planning Time: 0.365 ms
Execution Time: 0.199 ms

However, when I narrow the filter (change "created_at" >= '2020-11-25T00:00:00Z') which returns 9 records (which is less than the limit 20), the query is very slow

EXPLAIN ANALYZE
SELECT "advertisings"."id",
       "advertisings"."page_id",
       "advertisings"."page_name",
       "advertisings"."created_at",
       "posts"."image_url",
       "posts"."thumbnail_url",
       "posts"."post_content",
       "posts"."like_count"
FROM "advertisings"
  INNER JOIN "posts" ON "advertisings"."post_id" = "posts"."id"
WHERE "advertisings"."created_at" >= '2020-11-25T00:00:00Z'
AND   "advertisings"."created_at" < '2020-12-02T23:59:59Z'
ORDER BY "like_count" DESC LIMIT 20

Query plan:

Limit  (cost=1000.88..8051.73 rows=20 width=552) (actual time=218.485..4155.336 rows=9 loops=1)
  ->  Gather Merge  (cost=1000.88..612662.09 rows=1735 width=552) (actual time=218.483..4155.328 rows=9 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Nested Loop  (cost=0.85..611461.80 rows=723 width=552) (actual time=118.170..3786.176 rows=3 loops=3)
              ->  Parallel Index Scan using posts_like_count_idx on posts  (cost=0.43..372849.07 rows=438756 width=504) (actual time=0.024..1542.094 rows=351005 loops=3)
              ->  Index Scan using advertisings_post_id_index on advertisings  (cost=0.43..0.53 rows=1 width=52) (actual time=0.006..0.006 rows=0 loops=1053015)
                    Index Cond: (post_id = posts.id)
                    Filter: ((created_at >= '2020-11-25 00:00:00'::timestamp without time zone) AND (created_at < '2020-12-02 23:59:59'::timestamp without time zone))
                    Rows Removed by Filter: 1
Planning Time: 0.394 ms
Execution Time: 4155.379 ms

I spent hours googling but couldn't find the right solution. And help would be greatly appreciated.

Updated

When I continue narrowing the filter to

WHERE "advertisings"."created_at" >= '2020-11-27T00:00:00Z'
AND   "advertisings"."created_at" < '2020-12-02T23:59:59Z'

which also returns the 9 records as the slow above query. However, this time, the query is really fast again.

Limit  (cost=8082.99..8083.04 rows=20 width=552) (actual time=0.062..0.065 rows=9 loops=1)
  ->  Sort  (cost=8082.99..8085.40 rows=962 width=552) (actual time=0.061..0.062 rows=9 loops=1)
        Sort Key: posts.like_count DESC
        Sort Method: quicksort  Memory: 32kB
        ->  Nested Loop  (cost=0.85..8057.39 rows=962 width=552) (actual time=0.019..0.047 rows=9 loops=1)
              ->  Index Scan using advertisings_created_at_index on advertisings  (cost=0.43..501.30 rows=962 width=52) (actual time=0.008..0.012 rows=9 loops=1)
                    Index Cond: ((created_at >= '2020-11-27 00:00:00'::timestamp without time zone) AND (created_at < '2020-12-02 23:59:59'::timestamp without time zone))
              ->  Index Scan using posts_pkey on posts  (cost=0.43..7.85 rows=1 width=504) (actual time=0.003..0.003 rows=1 loops=9)
                    Index Cond: (id = advertisings.post_id)
Planning Time: 0.540 ms
Execution Time: 0.096 ms

I have no idea what happens

score 3 · Answer 1 · answered Dec 03 '20 at 03:41

PostgreSQL follows two different strategies in the first two and the last query:

If there are many matching advertisings rows, it uses a nested loop join to fetch the rows in the order of the ORDER BY clause and discards rows that don't match the condition until it has found 20.
If there are few matching advertisings rows, it fetches those few rows, then the matching rows in posts, then sorts and takes the first 20 rows.

The second execution is slow because PostgreSQL overestimates the rows in advertisings that match the condition. See how it estimates 962 instead of 9 in the third query?

The solution is to improve PostgreSQL's estimate:

if running
```
ANALYZE advertisings;
```
is enough to make the slow query fast, tell PostgreSQL to collect statistics more often:
```
ALTER TABLE advertisings SET (autovacuum_analyze_scale_factor = 0.05);
```
if that is not enough, try collecting more detailed statistics:
```
SET default_statistics_target = 1000;
ANALYZE advertisings;
```
You can experiment with values up to 10000. Once you found the value that works, persist it:
```
ALTER TABLE advertisings ALTER created_at SET STATISTICS 1000;
```

Postgres weird query plan when the number of records less than "limit"

1 Answers1