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

[Paul Ramsey]

This tweaks my memory to an old optimization problem we had for a 
client.  In the occasional rare case, the planner made the wrong 
decision just like this one... (I am assuming at this point that you 
have run VACUUM ANALYZE recently so your planner has good information to 
build the plan from).

- Try lowering your random_page_cost a bit. You can actually do this at 
run time, and if you find a value that works you can change this in 
postgresql.conf.

The default is '4'. Try "SET RANDOM_PAGE_COST = 3;" and then see if your 
query makes use of the indexes you expect. Then try 2. Don't go much 
lower than that.

Paul


Josh Livni wrote:
> 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
>>   
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users


-- 

   Paul Ramsey
   Refractions Research
   http://www.refractions.net
   pramsey at refractions.net
   Phone: 250-383-3022
   Cell: 250-885-0632