On 22/04/14 16:39, Albe Laurenz wrote:
> Could you run EXPLAIN ANALYZE for the query with enable_seqscan
> on and off? I'd be curious
> a) if the index can be used
> b) if it can be used, if that is actually cheaper
> c) how the planner estimates compare with reality.
>
Using the index:
Limit (cost=0.57..2.95 rows=1 width=0)
(actual time=0.095..0.095 rows=1 loops=1)
-> Index Scan ... (cost=0.57..14857285.83 rows=6240539 width=0)
(actual time=0.095..0.095 rows=1 loops=1)
Index Cond:...
Filter: ...
Rows Removed by Filter: 4
Total runtime: 0.147 ms
seq scan:
Limit (cost=0.00..1.12 rows=1 width=0)
(actual time=0.943..0.944 rows=1 loops=1)
-> Seq Scan ... (cost=0.00..6967622.77 rows=6240580 width=0)
(actual time=0.940..0.940 rows=1 loops=1)
Filter: ...
Rows Removed by Filter: 215
Total runtime: 0.997 ms
In these cases all the stuff comes from cache hits. When I first tried
the query it used a seq scan and it took several seconds. In this case
only setting random_page_cost less than seq_page_cost would make the
planner use the index.
I think if we had separate filter nodes, just like SORT nodes, then it
would be clearer that the setup cost of the seq scan with filter cannot
be 0.
Torsten