From: | Torsten Förtsch <torsten(dot)foertsch(at)gmx(dot)net> |
---|---|
To: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Is this a planner bug? |
Date: | 2014-04-22 15:03:46 |
Message-ID: | 535684D2.1010108@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | basti | 2014-04-22 15:11:26 | Re: could not rename temporary statistics file "/run/shm/pgstat.tmp" to "/run/shm/pgstat.stat": No such file or directory |
Previous Message | Adrian Klaver | 2014-04-22 14:52:18 | Re: importing downloaded data |