Re: Indexing for geographic objects?

Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> writes:
>> 5000 looks like a suspiciously round number ... how many rows are in
>> the table? Have you done a vacuum analyze on it?

> about 10,000 rows,

So the thing is estimating 0.5 selectivity, which is a fallback for
operators it knows nothing whatever about.

[ ... digs in Selkov's scripts ... ]

CREATE OPERATOR @ (
LEFTARG = seg, RIGHTARG = seg, PROCEDURE = seg_contains,
COMMUTATOR = '~'
);

CREATE OPERATOR ~ (
LEFTARG = seg, RIGHTARG = seg, PROCEDURE = seg_contained,
COMMUTATOR = '@'
);

Sure 'nuff, no selectivity info attached to these declarations.
Try adding

RESTRICT = contsel, JOIN = contjoinsel

to them. That's still an entirely bogus estimate, but at least
it's a smaller bogus estimate ... small enough to select an indexscan,
one hopes (see utils/adt/geo_selfuncs.c).

I have not dug through Gene's stuff to see which other indexable
operators might be missing selectivity estimates, but I'll bet there
are others. If you have the time to look through it and submit a
patch, I can incorporate it into the version that will go into contrib.

regards, tom lane