[postgis-users] PostGIS supresses the "LIMIT" optimization?

[Michael Smedberg]

I believe that Postgres has an optimization that "short-circuits" plan
execution when the SQL contains a LIMIT clause, but no "order by" or
other function that requires the whole result set.  It seems like
PostGIS turns this optimization off (I'm using PostGIS 1.2.1.)  Is this
a known bug?  Is there a workaround that would allow me to have poorly
constrained GIS queries return quickly (if it's poorly constrained, I
don't care about the results, I just want to know that there are too
many to display)?  In other words, is there a way for me to KNOW it's
poorly constrained without having to wait for the whole (large) result
set to be generated?

 

Here's an example of a query that uses the Postgres optimization:

 

explain analyze select search_id from property_search_keys limit 1;

                                                           QUERY PLAN

------------------------------------------------------------------------
--------------------------------------------------------

 Limit  (cost=0.00..0.06 rows=1 width=4) (actual time=0.005..0.005
rows=1 loops=1)

   ->  Seq Scan on property_search_keys  (cost=0.00..683165.13
rows=11507213 width=4) (actual time=0.004..0.004 rows=1 loops=1)

 Total runtime: 0.017 ms

 

 

And here's an example of a similar query that uses a GIST index, and
apparently does NOT use the optimization (it runs MUCH, MUCH slower):

 

explain analyze select search_id from property_search_keys where
GeomFromText('Polygon((47.339753313135965
-122.96722412109376,47.339753313135965
-121.63375854492188,47.877670790949324
-121.63375854492188,47.877670790949324
-122.96722412109376,47.339753313135965 -122.96722412109376))', 4269) &&
centroid limit 1;

 
QUERY PLAN                                 

------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
----------------------

 Limit  (cost=0.00..1.67 rows=1 width=4) (actual
time=11780.784..11780.785 rows=1 loops=1)

   ->  Seq Scan on property_search_keys  (cost=0.00..711933.16
rows=425818 width=4) (actual time=11780.782..11780.782 rows=1 loops=1)

         Filter:
('0103000020AD100000010000000500000035505C097DAB474001000000E7BD5EC03550
5C097DAB4740000000808F685EC012E4378457F04740000000808F685EC012E4378457F0
474001000000E7BD5EC035505C097DAB474001000000E7BD5EC0'::geometry &&
centroid)

 Total runtime: 11780.806 ms

 

 

 

In this case, the property_search_keys table has about 11m rows, and
looks like this:

 

\d property_search_keys;

                            Table "public.property_search_keys"

       Column       |            Type             |
Modifiers

--------------------+-----------------------------+---------------------
-------------------

 search_id          | integer                     | not null

[other columns]

 centroid           | geometry                    | not null

Indexes:

    "psk_i_centroid_lsd_lsp_br_ba" gist (centroid, last_sale_date,
last_sale_price, num_bedrooms, num_bathrooms) CLUSTER

[other indexes]

Check constraints:

    "enforce_dims_centroid" CHECK (ndims(centroid) = 2)

    "enforce_geotype_centroid" CHECK (geometrytype(centroid) =
'POINT'::text OR centroid IS NULL)

    "enforce_srid_centroid" CHECK (srid(centroid) = 4269)

 

I'm a newbie to this list, so please forgive me if this is a dumb
question...

 

Thanks!

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070816/551160f2/attachment.html>