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

Question

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

Rajanikant Hawaldar · 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 K · Answer

Got it thanks for your time both of you

Rajanikant Hawaldar · Answer

@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 K · Answer

I know, i was just curious as why it would prefer a seq scan over a index. The query is not that slow

Rajanikant Hawaldar · Answer

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 K · Answer

Why wouldn't autoanalyze kick in though? Even after after an analyze it still prefers a seq scan

Rajanikant Hawaldar · Answer

Yes

Chandu K · Answer

@raj I think it is tho : Vacuum counter 0 Autovacuum counter 0 Analyze counter 0 Autoanalyze counter 0Is this bad

Rajanikant Hawaldar · Answer

ok, try seq scan as @Pranam suggest, And the table is ANALYZEd?

Chandu K · Answer

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 Hawaldar · Answer

Tried WHERE NOT x instead?

Chandu K · Answer

Yes I do have index

Rajanikant Hawaldar · Answer

Well, do you have an index?

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

Answers (13)