Rolf Woll <rolf@anakon.no> writes:
> So. When the constraint is for index_type='G', a seq scan is used, and
> for other values of index_type the index is used. The table has 361000
> entries, with the following index_type values:
> count | index_type
> --------+------------
> 11080 | G
> 328 | M
> 349958 |
The system thinks a seqscan is cheaper. It might well be right; to
retrieve 3% of the tuples will probably mean hitting every page of the
table anyway, if the 'G' values are randomly scattered. Have you tried
doing actual timings both ways? (You can force use of the indexscan
with "set enable_seqscan to false".)
See also recent thread "again on index usage" in pgsql-hackers.
There's been some talk of reducing the default value of
random_page_cost, which would have the effect of making the planner
more willing to choose indexscans.
regards, tom lane