why would it still do a seq scan? I don't get it

Chandu K

5y741Databases & DBA

I have a several millions long table with ~800 entries that have a very specific value (FALSE), o

If i do a SELECT * FROM table WHERE x = FALSE, why would it still do a seq scan? I don't get it

13 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar5y ago
Accepted answer
Compare the plans with or without enable_seqscan. since column is boolean is good to be part of partial index in your case your index will be much smaller and may be used by planner.
Chandu K5y ago
Got it thanks for your time both of you
Rajanikant Hawaldar5y ago
@Pranam, avoid external links that doesn't help C#Corner. And if you want to copy and paste here Font will change so paste it in notepad first and copy it then paste here to maintain default font in db.
Chandu K5y ago
I know, i was just curious as why it would prefer a seq scan over a index. The query is not that slow
Rajanikant Hawaldar5y ago
For "slow query" questions (and anything related to planning, in general), it's better to show:
1. PostgreSQL version.
2. \d of every used table.
3. The query.
4. At least EXPLAIN (ANALYZE, BUFFERS) — even more options might be useful in recent versions, here.
Chandu K5y ago
Why wouldn't autoanalyze kick in though? Even after after an analyze it still prefers a seq scan
Rajanikant Hawaldar5y ago
Yes
Chandu K5y ago
@raj I think it is tho : Vacuum counter 0 Autovacuum counter 0 Analyze counter 0 Autoanalyze counter 0Is this bad
Rajanikant Hawaldar5y ago
ok, try seq scan as @Pranam suggest, And the table is ANALYZEd?
Chandu K5y ago
I think pg is already clever to figure that out since the explain gives me "WHERE NOT x" as the filter of the seq scan
Rajanikant Hawaldar5y ago
Tried WHERE NOT x instead?
Chandu K5y ago
Yes I do have index
Rajanikant Hawaldar5y ago
Well, do you have an index?