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(dot)keller(at)bvv(dot)bayern(dot)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(dot)Keller(at)bvv(dot)bayern(dot)de
80538 Muenchen : web: http://www.bayern.de/vermessung