Re: Proximity query with GIST and row estimation

On 2/15/07, Guillaume Smet <guillaume(dot)smet(at)gmail(dot)com> wrote:
> The use of PostGIS is slower than the previous cube/earthdistance
> approach (on a similar query and plan).

For the record, here are new information about my proximity query work.

Thanks to Tom Lane, I found the reason of the performance drop. The
problem is that the gist index for operator && is lossy (declared as
RECHECK in the op class).
AFAICS, for the && operator it's done to prevent problems when SRIDs
are not compatible: it forces the execution of the filter and so even
with a "should be non lossy" bitmap index scan, it throws an error as
if we use a seqscan (Paul, correct me if I'm wrong) because it forces
the execution of the filter.

As I'm sure I won't have this problem (I will write a wrapper stored
procedure so that the end users won't see the SRID used), I created a
different opclass without the RECHECK clause:
CREATE OPERATOR CLASS gist_geometry_ops_norecheck FOR TYPE geometry
USING gist AS
OPERATOR 3 &&,
FUNCTION 1 LWGEOM_gist_consistent (internal,
geometry, int4),
FUNCTION 2 LWGEOM_gist_union (bytea, internal),
FUNCTION 3 LWGEOM_gist_compress (internal),
FUNCTION 4 LWGEOM_gist_decompress (internal),
FUNCTION 5 LWGEOM_gist_penalty (internal,
internal, internal),
FUNCTION 6 LWGEOM_gist_picksplit (internal, internal),
FUNCTION 7 LWGEOM_gist_same (box2d, box2d, internal);

UPDATE pg_opclass
SET opckeytype = (SELECT oid FROM pg_type
WHERE typname = 'box2d'
AND typnamespace = (SELECT oid FROM pg_namespace
WHERE nspname=current_schema()))
WHERE opcname = 'gist_geometry_ops_norecheck'
AND opcnamespace = (SELECT oid from pg_namespace
WHERE nspname=current_schema());

As I use only the && operator, I put only this one.

And I recreated my index using:
CREATE INDEX idx_lieu_earthpoint ON lieu USING gist(earthpoint
gist_geometry_ops_norecheck);

In the case presented before, the bitmap index scan is then non lossy
and I have similar performances than with earthdistance method.

--
Guillaume