From: Guillaume Smet <guillaume.smet@gmail.com>
To: Paul Ramsey <pramsey@refractions.net>
Cc: Pg Performance list <pgsql-performance@postgresql.org>
Subject: Re: Proximity query with GIST and row estimation
Date: Thu, 15 Feb 2007 17:13:34 +0100
Message-ID: <1d4e0c10702150813p19df3108sf2a7c82c38360a0a@mail.gmail.com> (raw)
In-Reply-To: <0BD9E842-2DA7-4F90-A732-AFD60C1F50C7@refractions.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: guillaume.smet@gmail.com, pramsey@refractions.net
Subject: Re: Proximity query with GIST and row estimation
In-Reply-To: <1d4e0c10702150813p19df3108sf2a7c82c38360a0a@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox