Re: Indexing for geographic objects?

Oleg Bartunov <oleg@sai.msu.su> writes:
> We've done some work with GiST indices and found a little problem
> with optimizer.

> test=# set enable_seqscan = off;
> SET VARIABLE
> test=# explain select * from test where s @ '1.05 .. 3.95';
> NOTICE:  QUERY PLAN:

> Index Scan using test_seg_ix on test  (cost=0.00..369.42 rows=5000 width=12)

> EXPLAIN
> % ./bench.pl -d test -b 100  -i
> total: 1.71 sec; number: 100; for one: 0.017 sec; found 18 docs

I'd venture that the major problem here is bogus estimated selectivities
for rtree/gist operators.  Note the discrepancy between the estimated
row count and the actual (I assume the "found 18 docs" is the true
number of rows output by the query).  With an estimated row count even
half that (ie, merely two orders of magnitude away from reality ;-))
the thing would've correctly chosen the index scan over sequential.

5000 looks like a suspiciously round number ... how many rows are in
the table?  Have you done a vacuum analyze on it?
        regards, tom lane