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

[Josh Livni]

Regina,

Yeah - it seems to use the index on a query without the spatial stuff...

"Index Scan using bg_fips on kc_block_groups  (cost=0.00..8.02 rows=1 
width=2043) (actual time=0.023..0.027 rows=1 loops=1)"
"  Index Cond: ((fips)::text = '530330001001'::text)"
"Total runtime: 0.074 ms"

And, again it has similar conditions for newer fips_int column (with 
unique index):
"Index Scan using bg_fips_int on kc_block_groups  (cost=0.00..8.02 
rows=1 width=2043) (actual time=0.021..0.024 rows=1 loops=1)"
"  Index Cond: (fips_int = 530330001001::bigint)"
"Total runtime: 0.074 ms"


  -Josh

Obe, Regina wrote:
> I'm just really baffled its not using the fips index.  I do this thing 
> all the time and it always uses the attribute index first.
>  
> Although I guess usually when I do this kind of thing my index is the 
> primary key so I guess that helps a bit.
>  
> Does just doing a simple
> SELECT * FROM kc_block_groups  WHERE fips = '530330001001'
>  
> use the fips index.   If it doesn't I would be highly suspicious.
>
> ------------------------------------------------------------------------
> *From:* postgis-users-bounces at postgis.refractions.net on behalf of 
> Mark Cave-Ayland
> *Sent:* Fri 3/9/2007 2:57 PM
> *To:* PostGIS Users Discussion
> *Subject:* Re: [postgis-users] Re: GiST index seems to be ignored?
>
> On Fri, 2007-03-09 at 11:46 -0800, Josh Livni wrote:
> > Regina,
> >
> > Yeah - it seems like it's not wanting to limit the buffering to the
> > single fips result without some serious hand holding.
> >
> > Regarding your first post:
> > We actually tried a similar query earlier (I love IRC) - but in any
> > case, I swapped the AND in the query you posted in your last email for a
> > WHERE, and it took about 115 seconds.
> >
> > As for your point below:  You are right - my fips field is indeed a
> > varchar.  I tried the query you suggest below (quoting the fips) but it
> > did not help.  In addition, I made a fips_int column and added an index
> > - same thing (each of these took about 115s).
> >
> > Also, just want to reiterate thx again for everyone's input from this -
> > I've learned quite a bit about query optimizing today (still quite a
> > ways to go, however).
> >
> >   -Josh
>
> Yeah, as I understand it, the planner is pulling up the buffer() into
> the WHERE clause rather than materializing the table, maybe because it
> realises only a single result is being returned :(
>
> Thinking about this, I wonder if it is possible to use an ORDER BY to
> force the planner to materialize the dataset - something like this?
>
> SELECT p.* FROM (SELECT buffer(kc_block_groups.the_geom, 1320) as
> thebuffer FROM kc_block_groups AND kc_block_groups.fips = 530330001001
> ORDER BY kc_block_groups.fips)  b INNER JOIN parcels_06  p   ON
> b.thebuffer && centroid(p.the_geom)  WHERE within(centroid(p.the_geom),
> b.thebuffer)
>
> Josh, does an EXPLAIN ANALYZE on this query show a Materialize
> somewhere, and does this keep the 300ms execution time?
>
>
> Kind regards,
>
> Mark.
>
>
> _______________________________________________
> 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
>