[postgis-users] Re: GiST index seems to be ignored?

[Obe, Regina]

Do you have a gist index on your p.the_geom.
 
I think expand is much more efficient than buffer for your && and I think your centroid && instead of just using p.the_geom is totally wasteful.  So I would change to do the falling
 
1) Add a btree index on fips on your block groups  - a attribute index is almost always more selective than a spatial index.
2) I think you can drop that buffer index you have going, but if you do need to keep it change it to expand(b.the_geom, 90) - for this particular query, it probably won't be used anyway if you have an index on fips.
2) Make sure you have an index on p.the_geom
3) Change your query to 
 
 SELECT
   count(j."lfc_code") as count,
    sum(J."gross_sqft") as Sum_Gross_Sqft,
    sum(j."units") as Sum_Units,
    j."lfc_code"
   FROM kc_block_groups b, parcels_06 p inner join lfc_comm j on
 p.pin_key = j.pin_key
    WHERE expand(b.the_geom,90) && p.the_geom
    and within(centroid(p.the_geom), buffer(b.the_geom,1320))
    and b.fips = 530330001001
    GROUP BY j."lfc_code"


________________________________

From: postgis-users-bounces at postgis.refractions.net on behalf of Reid Priedhorsky
Sent: Fri 3/9/2007 12:40 PM
To: PostGIS Users Discussion
Subject: [postgis-users] Re: GiST index seems to be ignored?



Josh Livni wrote:

> I am basically trying to find points within a buffered polygon.  I've
> created a GiST index on my polygons:
> CREATE INDEX kc_block_groups_qm_buffer_the_geom_gist
>  ON kc_block_groups
>  USING gist
>  (buffer(the_geom, 1320::double precision));

> SELECT
>    count(j."lfc_code") as count,
>    sum(J."gross_sqft") as Sum_Gross_Sqft,
>    sum(j."units") as Sum_Units,
>    j."lfc_code"
>    FROM kc_block_groups b, parcels_06 p inner join lfc_comm j on
> p.pin_key = j.pin_key
>    WHERE buffer(b.the_geom,90) && centroid(p.the_geom)
>    and within(centroid(p.the_geom), buffer(b.the_geom,1320))
>    and b.fips = 530330001001
>    GROUP BY j."lfc_code"

within() won't take advantage of the index, and with the operator that
does (&&), your buffer() call uses a different buffer distance. What
happens if you create the index on buffer(the_geom, 90)?

My other guess is that you use a typecast in the index creation but not
the query.

Good luck,

Reid
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users





-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070309/00627967/attachment.html>