Re: Proximity query with GIST and row estimation

On 2/15/07, Guillaume Smet <guillaume.smet@gmail.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