<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7638.1">
<TITLE>Re: [postgis-users] Re: GiST index seems to be ignored?</TITLE>
</HEAD>
<BODY>
<DIV id=idOWAReplyText1945 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>Just noticed something else
in your prior email which I think Reid may have alluded to.</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>Is your fips stored as an integer or as a
varchar or text? It looked from your query plan that it is probably being
stored as text which may explain why your fips index is not being used if it
really is not being used because it may be casting it to an int to match your
where clause thus not finding a suitable index.</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>What if you tried the original I gave you
with quoting the fips code instead of treating it as number. like below.
Note I also changed my original p.the_geom to centroid(p.the_geom) because I am
assuming you have an index on centroid(p.the_geom) and not necessarily on
p.the_geom.</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT size=2>SELECT<BR> count(j."lfc_code") as
count,<BR> sum(J."gross_sqft") as
Sum_Gross_Sqft,<BR> sum(j."units") as
Sum_Units,<BR> j."lfc_code"<BR> FROM
kc_block_groups b, parcels_06 p inner join lfc_comm j on<BR> p.pin_key =
j.pin_key<BR> WHERE expand(b.the_geom,1320) &&
centroid(p.the_geom)<BR> and
within(centroid(p.the_geom),
buffer(b.the_geom,1320))<BR> and b.fips =
'530330001001'<BR> GROUP BY j."lfc_code"</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV></DIV>
<DIV dir=ltr><BR>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net on behalf of Obe,
Regina<BR><B>Sent:</B> Fri 3/9/2007 1:44 PM<BR><B>To:</B> PostGIS Users
Discussion<BR><B>Subject:</B> RE: [postgis-users] Re: GiST index seems to be
ignored?<BR></FONT><BR></DIV>
<DIV>
<DIV id=idOWAReplyText11921 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>Interesting. So was it
not using the fips index at all? Just curious can you try this
query</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT size=2>SELECT p.*<BR>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) </FONT></DIV>
<DIV dir=ltr><FONT size=2>WHERE within(centroid(p.the_geom),
b.thebuffer)<BR></DIV></FONT></DIV>
<DIV dir=ltr><BR>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net on behalf of Josh
Livni<BR><B>Sent:</B> Fri 3/9/2007 1:29 PM<BR><B>To:</B> PostGIS Users
Discussion<BR><B>Subject:</B> Re: [postgis-users] Re: GiST index seems to be
ignored?<BR></FONT><BR></DIV>
<DIV>
<P><FONT size=2>Regina,<BR><BR>Thanks a lot for your detailed response (and to
Reid too).<BR><BR>First, the 90 was a typo in my message, sorry -- the query was
always<BR>using just 1320 with it's slowness.<BR><BR>Also, I did have both a
btree index on fips, and a GiST index
on<BR>centroid(parcels_06.the_geom)<BR><BR>I tried your query, and it too, took
a couple minutes.<BR><BR>Moving on to some good news for me (and my not having
to use my hack<BR>workaround of temp tables with pre-calculated
buffers):<BR><BR>Turns out, Mark Cave-Ayland was exceedingly helpful on IRC and
managed<BR>to solve this little issue, where it looks like forcing the
planner's<BR>hand was the key.<BR><BR>He narrowed it down, so we noticed
that:<BR>SELECT p.*<BR>FROM kc_block_groups b, parcels_06
p <BR>WHERE buffer(b.the_geom,1320) &&
centroid(p.the_geom) <BR>and b.fips = 530330001001;<BR><BR>took >
110,000 ms to run.<BR><BR><BR>whereas:<BR>SELECT *<BR>FROM parcels_06 AS
p<BR>WHERE <BR> (SELECT buffer(the_geom,
1320) AS
the_geomx <BR>
FROM kc_block_groups WHERE fips =
530330001001) <BR> &&
centroid(p.the_geom)<BR>AND within(centroid(p.the_geom),<BR>
(SELECT buffer(the_geom, 1320) AS
the_geomx<BR>
FROM kc_block_groups WHERE fips = 530330001001) );<BR><BR>took ~300ms to
run.<BR><BR>Nice!<BR><BR>I put this back in my orig query, and it took about
1500ms:<BR>SELECT<BR> count(j."lfc_code") as count,<BR>
sum(J."gross_sqft") as Sum_Gross_Sqft,<BR> sum(j."units") as
Sum_Units,<BR> j."lfc_code"<BR> FROM kc_block_groups b,
parcels_06 p inner join lfc_comm j on<BR>p.pin_key = j.pin_key<BR>
WHERE<BR> (SELECT buffer(the_geom, 1320)<BR> AS
the_geomx<BR> FROM kc_block_groups<BR> WHERE fips
= 530330001001) &&
centroid(p.the_geom)<BR> and within(centroid(p.the_geom), (SELECT
buffer(the_geom, 1320)<BR> AS the_geomx<BR> FROM
kc_block_groups<BR> WHERE fips = 530330001001)
)<BR> and b.fips = 530330001001<BR> GROUP BY
j."lfc_code" ;<BR><BR>However, this compared to a ~350ms return time for the
query on my temp<BR>table of pre-buffered polygons:<BR>SELECT<BR>
count(j."lfc_code") as count,<BR> sum(J."gross_sqft") as
Sum_Gross_Sqft,<BR> sum(j."units") as Sum_Units,<BR>
j."lfc_code"<BR> FROM kc_bg_qm_buffer b, parcels_06 p inner join
lfc_comm j on<BR>p.pin_key = j.pin_key<BR> WHERE b.the_geom
&& centroid(p.the_geom)<BR> and within(centroid(p.the_geom),
b.the_geom)<BR> and b.fips = 530330001001<BR> GROUP BY
j."lfc_code" ;<BR><BR>As I think Mark noted, since my queries are pretty much
spatial, further<BR>subselects to ensure the spatial stuff is done first might
help. This<BR>is my next step, but meanwhile here is a really significant
improvement<BR>- I hope the notes above might aid someone in a similar
situation. <BR>Thanks Mark!<BR><BR>Cheers,<BR><BR><BR>
-Josh<BR><BR>Obe, Regina wrote:<BR>> Do you have a gist index on your
p.the_geom.<BR>> <BR>> I think expand is much more efficient than
buffer for your && and I<BR>> think your centroid && instead
of just using p.the_geom is totally<BR>> wasteful. So I would change to
do the falling<BR>> <BR>> 1) Add a btree index on fips on your block
groups - a attribute index<BR>> is almost always more selective than a
spatial index.<BR>> 2) I think you can drop that buffer index you have going,
but if you<BR>> do need to keep it change it to expand(b.the_geom, 90) - for
this<BR>> particular query, it probably won't be used anyway if you have
an<BR>> index on fips.<BR>> 2) Make sure you have an index on
p.the_geom<BR>> 3) Change your query to<BR>> <BR>>
SELECT<BR>> count(j."lfc_code") as
count,<BR>> sum(J."gross_sqft") as
Sum_Gross_Sqft,<BR>> sum(j."units") as
Sum_Units,<BR>>
j."lfc_code"<BR>> FROM kc_block_groups b, parcels_06 p
inner join lfc_comm j on<BR>> p.pin_key =
j.pin_key<BR>> WHERE expand(b.the_geom,90) &&
p.the_geom<BR>> and within(centroid(p.the_geom),
buffer(b.the_geom,1320))<BR>> and b.fips =
530330001001<BR>> GROUP BY
j."lfc_code"<BR>><BR>>
------------------------------------------------------------------------<BR>>
*From:* postgis-users-bounces@postgis.refractions.net on behalf of<BR>> Reid
Priedhorsky<BR>> *Sent:* Fri 3/9/2007 12:40 PM<BR>> *To:* PostGIS Users
Discussion<BR>> *Subject:* [postgis-users] Re: GiST index seems to be
ignored?<BR>><BR>> Josh Livni wrote:<BR>><BR>> > I am basically
trying to find points within a buffered polygon. I've<BR>> > created
a GiST index on my polygons:<BR>> > CREATE INDEX
kc_block_groups_qm_buffer_the_geom_gist<BR>> > ON
kc_block_groups<BR>> > USING gist<BR>> >
(buffer(the_geom, 1320::double precision));<BR>><BR>> > SELECT<BR>>
> count(j."lfc_code") as count,<BR>>
> sum(J."gross_sqft") as Sum_Gross_Sqft,<BR>>
> sum(j."units") as Sum_Units,<BR>>
> j."lfc_code"<BR>> > FROM
kc_block_groups b, parcels_06 p inner join lfc_comm j on<BR>> > p.pin_key
= j.pin_key<BR>> > WHERE buffer(b.the_geom,90)
&& centroid(p.the_geom)<BR>> > and
within(centroid(p.the_geom), buffer(b.the_geom,1320))<BR>>
> and b.fips = 530330001001<BR>> >
GROUP BY j."lfc_code"<BR>><BR>> within() won't take advantage of the
index, and with the operator that<BR>> does (&&), your buffer() call
uses a different buffer distance. What<BR>> happens if you create the index
on buffer(the_geom, 90)?<BR>><BR>> My other guess is that you use a
typecast in the index creation but not<BR>> the query.<BR>><BR>> Good
luck,<BR>><BR>> Reid<BR>>
_______________________________________________<BR>> postgis-users mailing
list<BR>> postgis-users@postgis.refractions.net<BR>> <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR>>
------------------------------------------------------------------------<BR>><BR>>
*<BR>> The substance of this message, including any attachments, may
be<BR>> confidential, legally privileged and/or exempt from
disclosure<BR>> pursuant to Massachusetts law. It is intended solely for
the<BR>> addressee. If you received this in error, please contact the
sender<BR>> and delete the material from any computer.<BR>>
*<BR>><BR>>
------------------------------------------------------------------------<BR>><BR>>
_______________________________________________<BR>> postgis-users mailing
list<BR>> postgis-users@postgis.refractions.net<BR>> <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>> <BR><BR><BR>_______________________________________________<BR>postgis-users
mailing list<BR>postgis-users@postgis.refractions.net<BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></FONT></P></DIV></DIV>
</BODY>
</HTML>