Search Postgresql Archives

Re: Is this a planner bug?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux