0

I'm currently using PostgreSQL 14.0 for windows My target table has a 900k+ rows and my query is using single column filter (status_id). The column status_id is indexed via btree (default) When i execute 'explain analyze select * from trip_detail where status_id = 5', the query planner use sequential scan

Seq Scan on trip_detail  (cost=0.00..69261.82 rows=912875 width=511) (actual time=1.404..457.886 rows=913420 loops=1)
   Filter: (status_id = 5)
   Rows Removed by Filter: 19320
Planning Time: 0.216 ms
Execution Time: 493.398 ms

If i use a different value, it uses the Index scan

Index Scan using idx_tttdc_status_idh5 on trip_detail  (cost=0.42..131.10 rows=1400 width=511) (actual time=106.692..138.531 rows=1515 loops=1)
   Index Cond: (status_id = 12)
Planning Time: 0.236 ms
Execution Time: 138.764 ms

I already vacuumed, reindexed and analyzed the table and indexes, but it still uses a sequential scan for the large row groups. What might cause for this kind of issue?

Improve this question
0

1 Answer 1

Reset to default
2

The first query scans 932740 rows and returns all but 19320 of them. A sequential scan will be faster than an index scan for that query.

Improve this answer
5
  • sir @laurenz i'm just wondering that if use a different status_id value having small/few rows, it uses the index scanning versus the status_id 5 having a larger data. If i check the query cost value, when status_id = 5, the cost is 0.00 and if it is a different value, the query cost = 0.42
    – starting dba jr
    Feb 14 at 8:56
  • Sure; it is more expensive to return a lot of results than a few.
    – Laurenz Albe
    Feb 14 at 10:30
  • sir @laurenz correct me on my analysis. Postgresql would use sequential scanning over index scanning if the cost is less regardless how large the data is.
    – starting dba jr
    Feb 15 at 1:17
  • Sure! The idea is that the costs estimated by the optimizer correspond to the actual query execution time. So if the cost estimated for the sequential scan is lower than the cost estimated for the index scan, PostgreSQL will do a sequential scan. (By the way, I am not a nobleman.)
    – Laurenz Albe
    Feb 15 at 6:06
  • thank you for the explanation sir. it's a great help. For addressing you with a sir in your name is the way i addressed professor, teacher, instructor and professionals who share their knowledge and skills to other which is for me a very noble action. Again thank you very much sir @Laurenz
    – starting dba jr
    Feb 16 at 1:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.