DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=JcaWaNNBpD7Yxdg9Pt3N9x7+lB9a3aQuWWqFHPbE8+X1oCpSQMwYBgi2miczpqKUtlVHmELcbH7NBoJVnnO3JTgSac6/KZIIAZoEqdDsA09VV7QcE/H//wYXLRGKU9in+i8UOLPrbVv+uqP7Ww+zMPoRrb9Aex9wCkH3ts7zZtY=
Message-ID: <1d4e0c10702150813p19df3108sf2a7c82c38360a0a@mail.gmail.com>
Date: Thu, 15 Feb 2007 17:13:34 +0100
From: "Guillaume Smet" <guillaume.smet@gmail.com>
To: "Paul Ramsey" <pramsey@refractions.net>
Subject: Re: Proximity query with GIST and row estimation
Cc: "Pg Performance list" <pgsql-performance@postgresql.org>
In-Reply-To: <0BD9E842-2DA7-4F90-A732-AFD60C1F50C7@refractions.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <1d4e0c10702130909g3be0a10fpf2594574c84866c9@mail.gmail.com>
	<0BD9E842-2DA7-4F90-A732-AFD60C1F50C7@refractions.net>

On 2/14/07, Paul Ramsey <pramsey@refractions.net> wrote:
> You'll find that PostGIS does a pretty good job of selectivity
> estimation.

So I finally have a working PostGIS and I fixed the query to use PostGIS.

The use of PostGIS is slower than the previous cube/earthdistance
approach (on a similar query and plan). But you're right, it does a
pretty good job to calculate the selectivity and the estimations are
really good.
It helps to select a good plan (or a bad one if the previous false
numbers led to a better plan which is my case for certain queries).
I suppose it's normal to be slower as it's more precise. I don't know
which approach is better in my case as I don't need the precision of
PostGIS.

For the record, here is what I did:
select AddGeometryColumn('lieu','earthpoint',32631,'POINT',2);
update lieu set earthpoint=Transform(SetSRID(MakePoint(wgslon,
wgslat), 4327), 32631);
create index idx_lieu_earthpoint on lieu using gist(earthpoint
gist_geometry_ops);

analyze lieu;

select numlieu, nomlieu, wgslon, wgslat, astext(earthpoint) from lieu
where earthpoint && Expand(Transform(SetSRID(MakePoint(12.49244400,
41.89103400), 4326), 32631), 3000);

(3000 is the distance in meters)

--
Guillaume