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

[Mark Cave-Ayland]

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.



  From MAILER-DAEMON  Fri Mar  9 12:02:22 2007
Return-Path: <>
X-Original-To: postgis-users at postgis.refractions.net
Delivered-To: postgis-users at netnation.refractions.net
Received: from mail.inet.dk (mail99.123hotel.dk [80.160.71.178])
	by netnation.refractions.net (Postfix) with ESMTP id E40C22C0790
	for <postgis-users at postgis.refractions.net>;
	Fri,  9 Mar 2007 12:02:01 -0800 (PST)
Received: from ([64.40.100.236])
	by mail.inet.dk (80.160.71.178=mail.inet.dk) with SMTP id OXL67218
	for <postgis-users at postgis.refractions.net>;
	Fri, 09 Mar 2007 21:02:18 +0100
Date: Fri, 09 Mar 2007 21:02:18 +0100
From: Martin Hoegh <mh at svaj.dk>
To: <postgis-users at postgis.refractions.net>
Message-Id: <912894025 at mail.inet.dk>
Content-Type: text/plain; charset="utf-8"
Subject: [postgis-users] Re: postgis-users Digest, Vol 53, Issue 9
X-BeenThere: postgis-users at postgis.refractions.net
X-Mailman-Version: 2.1.5
Precedence: list
Reply-To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
List-Id: PostGIS Users Discussion <postgis-users.postgis.refractions.net>
List-Unsubscribe: <http://postgis.refractions.net/mailman/listinfo/postgis-users>,
	<mailto:postgis-users-request at postgis.refractions.net?subject=unsubscribe>
List-Archive: <http://lists.refractions.net/pipermail/postgis-users>
List-Post: <mailto:postgis-users at postgis.refractions.net>
List-Help: <mailto:postgis-users-request at postgis.refractions.net?subject=help>
List-Subscribe: <http://postgis.refractions.net/mailman/listinfo/postgis-users>,
	<mailto:postgis-users-request at postgis.refractions.net?subject=subscribe>
X-List-Received-Date: Fri, 09 Mar 2007 20:02:22 -0000

Jeg er på forældreorlov indtil d. 14. maj. Mine kollegaer kan traeffes paa tlf. 98129300.

I am on parental leave until May 14. If you have any matter of urgency please do not hesitate to call my collegues on +45 98129300