PostgreSQL optimization: Sequantial Scan VS Index Scan

Question

I have one interesting case with select on postgres table:

advert (~2.5 million records)
    id serial,
    user_id integer (foreign key),
    ...

Here is my select:

select count(*) from advert where user_id in USER_IDS_ARRAY

And if USER_IDS_ARRAY length <= 100 I have next explain analyze:

Aggregate  (cost=18063.36..18063.37 rows=1 width=0) (actual time=0.362..0.362 rows=1 loops=1)
  ->  Index Only Scan using ix__advert__user_id on advert  (cost=0.55..18048.53 rows=5932 width=0) (actual time=0.030..0.351 rows=213 loops=1)
        Index Cond: (user_id = ANY ('{(...)}'))
        Heap Fetches: 213
Planning time: 0.457 ms
Execution time: 0.392 ms

But when USER_IDS_ARRAY length > 100:

Aggregate  (cost=424012.09..424012.10 rows=1 width=0) (actual time=867.438..867.438 rows=1 loops=1)
  ->  Seq Scan on advert  (cost=0.00..423997.11 rows=5992 width=0) (actual time=0.375..867.345 rows=213 loops=1)
        Filter: (user_id = ANY ('{(...)}'))
        Rows Removed by Filter: 2201318
Planning time: 0.261 ms
Execution time: 867.462 ms

No matter what user_ids in USER_IDS_ARRAY, only it's length matters.

Does anybody have ideas how to optimize this select for more then 100 user_ids?

Have you tried just selecting from advert without the join? Filter with > 100 user IDs and see if it uses the index or not. — Sevanteri
– Sevanteri, Commented Mar 22, 2016 at 8:13
@Sevanteri yes, you are right, the same situation with advert select. Simplified my question with one table only. — Vitalii Ponomar
– Vitalii Ponomar, Commented Mar 22, 2016 at 8:17
Right. It seems that the query planner just thinks that it is better to do a seq scan instead. You could run analyze for the tables and see if it helps. — Sevanteri
– Sevanteri, Commented Mar 22, 2016 at 8:21

Jakub Kania · Accepted Answer · 2016-03-22 08:47:04Z

3

If SET enable_seqscan = OFF still doesn't force index scan it means the index scan is not possible. It turns out here the index was partial.

answered Mar 22, 2016 at 8:47

Jakub Kania

16.6k2 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL optimization: Sequantial Scan VS Index Scan

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related