Re: Problems with genetic optimizer

Hi
>
> Hmm.  The system's knowledge of selectivities for R-tree indexes is
> essentially nil; perhaps someone will be motivated to improve that
> someday.  In the meantime, the entirely bogus numbers returned by
> src/backend/utils/adt/geo_selfuncs.c are supposed to be small enough
> to ensure that R-trees are used if available.  What was your test
> query exactly, and what do you get from EXPLAIN with and without
> forcing enable_seqscan off?
>
>                         regards, tom lane


Ok, I created a table with only one column (box), inserted 120000
elements, created an index and run a vacuum:

convert=# select count(*) from box_tmp;
 count
--------
 120000
(1 row)


convert=# explain select * from box_tmp where ebre &&
box('(470758.555,354028.145),(470758.525,354028.115)'::box);
NOTICE:  QUERY PLAN:

Seq Scan on box_tmp  (cost=0.00..2500.00 rows=2400 width=32)

EXPLAIN


convert=#  set enable_seqscan = off;
SET VARIABLE


convert=# explain select * from box_tmp where ebre &&
box('(470758.555,354028.145),(470758.525,354028.115)'::box);
NOTICE:  QUERY PLAN:

Index Scan using idx on box_tmp  (cost=0.00..2503.28 rows=2400 width=32)

EXPLAIN

I'm running  PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc
egcs-2.91.66

The result of the query if I set the sequential search on is this:

convert=# select * from box_tmp where ebre &&
box('(470758.555,354028.145),(470758.525,354028.115)'::box);
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!#

Thanks for helping,
Peter


>
> Peter Keller <peter.keller@bvv.bayern.de> writes:
> > I generated a table with a column type 'box', inserted some values and
> > created an index (ops_box) on that column, run a vacuum and looked with
> > the command EXPLAIN if the select will use the index.
> > And the result was very astonishing:
> > if I had inserted 120000 elements postgres will _NOT_ use the index, if
> > I had inserted 100000 elements postgres will use the index!


--
        Bezirksfinanzdirektion Muenchen
              Vermessungsabteilung
.........................................................
 Peter Keller        :  Tel: (+49) 089-2190-2594
 Vermessungsrat     :  Fax: (+49) 089-2190-2459
 Alexandrastr. 3    :  mailto:Peter.Keller@bvv.bayern.de
 80538 Muenchen        :  web: http://www.bayern.de/vermessung