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

Obe, Regina robe.dnd at cityofboston.gov
Fri Mar 9 10:44:08 PST 2007


Interesting.  So was it not using the fips index at all? Just curious can you try this query
 
SELECT p.*
FROM (SELECT buffer(kc_block_groups.the_geom, 1320) as thebuffer FROM kc_block_groups AND kc_block_groups.fips = 530330001001)  b INNER JOIN parcels_06  p   ON b.thebuffer && centroid(p.the_geom)  
WHERE within(centroid(p.the_geom), b.thebuffer)


________________________________

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



Regina,

Thanks a lot for your detailed response (and to Reid too).

First, the 90 was a typo in my message, sorry -- the query was always
using just 1320 with it's slowness.

Also, I did have both a btree index on fips, and a GiST index on
centroid(parcels_06.the_geom)

I tried your query, and it too, took a couple minutes.

Moving on to some good news for me (and my not having to use my hack
workaround of temp tables with pre-calculated buffers):

Turns out, Mark Cave-Ayland was exceedingly helpful on IRC and managed
to solve this little issue, where it looks like forcing the planner's
hand was the key.

He narrowed it down, so we noticed that:
SELECT p.*
FROM kc_block_groups b, parcels_06 p   
WHERE buffer(b.the_geom,1320) && centroid(p.the_geom)  
and b.fips = 530330001001;

took > 110,000 ms to run.


whereas:
SELECT *
FROM parcels_06 AS p
WHERE  
     (SELECT buffer(the_geom, 1320)    AS the_geomx  
                   FROM kc_block_groups     WHERE fips = 530330001001)  
     && centroid(p.the_geom)
AND within(centroid(p.the_geom),
    (SELECT buffer(the_geom, 1320)  AS the_geomx
                  FROM kc_block_groups  WHERE fips = 530330001001) );

took ~300ms to run.

Nice!

I put this back in my orig query, and it took about 1500ms:
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
   (SELECT buffer(the_geom, 1320)
   AS the_geomx
   FROM kc_block_groups
    WHERE fips = 530330001001)    && centroid(p.the_geom)
   and within(centroid(p.the_geom), (SELECT buffer(the_geom, 1320)
   AS the_geomx
   FROM kc_block_groups
    WHERE fips = 530330001001) )
   and b.fips = 530330001001
   GROUP BY j."lfc_code" ;

However, this compared to a ~350ms return time for the query on my temp
table of pre-buffered polygons:
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_bg_qm_buffer b, parcels_06 p inner join lfc_comm j on
p.pin_key = j.pin_key
   WHERE b.the_geom && centroid(p.the_geom)
   and within(centroid(p.the_geom), b.the_geom)
   and b.fips = 530330001001
   GROUP BY j."lfc_code" ;

As I think Mark noted, since my queries are pretty much spatial, further
subselects to ensure the spatial stuff is done first might help.  This
is my next step, but meanwhile here is a really significant improvement
- I hope the notes above might aid someone in a similar situation.  
Thanks Mark!

Cheers,


  -Josh

Obe, Regina wrote:
> 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.
> *
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>  


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


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


More information about the postgis-users mailing list