Postgres Pro
Downloads
Home   >   mailing lists

Re: Query's fast standalone - slow as a subquery. - Mailing list pgsql-performance

From Tom Lane
Subject Re: Query's fast standalone - slow as a subquery.
Date
Msg-id 27481.1109949776@sss.pgh.pa.us
Whole thread Raw
In response to Query's fast standalone - slow as a subquery.  (Ron Mayer <rm_pg@cheapcomplexdevices.com>)
List pgsql-performance
Tree view 
Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
>    ->  Seq Scan on points  (cost=0.00..444.43 rows=1 width=82) (actual time=0.096..132.255 rows=15743 loops=1)
>          Filter: (the_geom &&
'010300000001000000050000009A99999999D95EC0295C8FC2F5A839409A99999999D95EC0F6285C8FC295454048E17A14AE7758C0F6285C8FC295454048E17A14AE7758C0295C8FC2F5A839409A99999999D95EC0295C8FC2F5A83940'::geometry)

>    ->  Seq Scan on points  (cost=0.00..444.43 rows=15794 width=82) (actual time=0.067..94.307 rows=15743 loops=1)
>          Filter: (the_geom &&
'010300000001000000050000009A99999999D95EC0295C8FC2F5A839409A99999999D95EC0F6285C8FC295454048E17A14AE7758C0F6285C8FC295454048E17A14AE7758C0295C8FC2F5A839409A99999999D95EC0295C8FC2F5A83940'::geometry)

Apparently the selectivity of the && condition is misestimated in the
first case (note the radically wrong rowcount estimate), leading to an
inefficient join plan choice.  I suppose this is a bug in the postgis
selectivity routines --- better complain to them.

            regards, tom lane

pgsql-performance by date:

Previous
From: Ron Mayer
Date:
Subject: Query's fast standalone - slow as a subquery.
Next
From: Richard Huxton
Date:
Subject: Re: Help with tuning this query (with explain analyze finally)