X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 987EA9DCBCC for ; Fri, 17 Mar 2006 12:56:12 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 12903-01 for ; Fri, 17 Mar 2006 12:56:09 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.204]) by postgresql.org (Postfix) with ESMTP id 8CC549DCB94 for ; Fri, 17 Mar 2006 12:56:08 -0400 (AST) Received: by zproxy.gmail.com with SMTP id 8so568855nzo for ; Fri, 17 Mar 2006 08:56:04 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=KAwj15CAbCC2qi1IVTWRxcqHeIXoZYu64jwoK33OHAfoX9Yggy3Hv0Z1yVUGw3zJ3ooyL64yf944N0KFOxP/MMN/HzOXFoKCGdpsJCdi7yj96BRe4fCo42eBuOvjqJ2o4loeA1Ge5NSkPRIusO973Bqicl+j5/MVbYLDJdStnD0= Received: by 10.65.152.4 with SMTP id e4mr93040qbo; Fri, 17 Mar 2006 08:56:03 -0800 (PST) Received: by 10.65.135.10 with HTTP; Fri, 17 Mar 2006 08:56:03 -0800 (PST) Message-ID: Date: Fri, 17 Mar 2006 11:56:03 -0500 From: "Merlin Moncure" To: "Dan Harris" Subject: Re: Help optimizing a slow index scan Cc: "PostgreSQL Performance" In-Reply-To: <4419DB50.10404@drivefaster.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <4419DB50.10404@drivefaster.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.136 required=5 tests=[AWL=0.136] X-Spam-Score: 0.136 X-Spam-Level: X-Archive-Number: 200603/279 X-Sequence-Number: 17716 On 3/16/06, Dan Harris wrote: > explain analyze > select distinct eventmain.incidentid, eventmain.entrydate, > eventgeo.long, eventgeo.lat, eventgeo.geox, eventgeo.geoy > from eventmain, eventgeo > where > eventmain.incidentid =3D eventgeo.incidentid and > ( long > -104.998027962962 and long < -104.985957781349 ) and > ( lat > 39.7075542720006 and lat < 39.7186195832938 ) and > eventmain.entrydate > '2006-1-1 00:00' and > eventmain.entrydate <=3D '2006-3-17 00:00' > order by > eventmain.entrydate; As others will probably mention, effective queries on lot/long which is a spatial problem will require r-tree or gist. I don't have a lot of experience with exotic indexes but this may be the way to go. One easy optimization to consider making is to make an index on either (incidentid, entrydate) or (incident_id,long) which ever is more selective. This is 'yet another query' that would be fun to try out and tweak using the 8.2 upcoming row-wise comparison. merlin