Thread

Re: [HACKERS] subselect and optimizer

Boersenspielteam <boersenspiel@vocalweb.de> — 1998-04-12T14:32:16Z

Hi Bruce,


> > > > But this patch still didn't help for a simple join without a where 
> > > > clause. The query plan says it uses two sequential scans, where 6.2.1 
> > > > uses two index scans.
> > >
> > > But we didn't have subselcts in 6.2.1?
> > 
> > No, but in the more general case of a simple join over two tables 
> > with fields with an index declared on them.
> > 
> > say: Select * from Trans, Spieler where 
> > Spieler.spieler_nr=Trans.spieler_nr
> > 
> > Uses indices in 6.2.1, doesn't use them in 6.3.1 (two seq scans).
> > 
> > I just wanted to remind you, that these problems are not restricted 
> > to subqueries, but seem to be a more general 'flaw' in 6.3.x .
> 
> Ah, but that is fixed in 6.3.2 beta.  We particularly waited for a fix
> for this before releasing a new beta.  But you say you have Vadim's fix
> that is in 6.3.2, and it still doesn't work?

Yep, exactly. The query with the where clause is fixed after 
applying Vadim's prune.c patch, simple join still uses two seq scans 
:-(

I uploaded test data and Vadim fixed one file, but asked you 
(Bruce) to look over other files of the optimizer code. There seem 
to be other bugs in the optimizer code, which were introduced between 
6.2.1 and 6.3. We have seen about 5-6 error reports from different 
people, from the simpliest queries like my simple join to rather 
complex subqueries. But when a simple join doesn't work (ok, it 
works, but kind of crawls), this error is supposed to pop up under 
other circumstances too.

Hope you can find this nasty little bug, cause it makes postgres 
unusable. Especially before going into development again.

See the mailinglist archives for a post of mine. There is a link in 
it,where you can download the test data, it should still be 
there. (don't have access to this from home)

I greatly appreciate all the time and hard work all you 
PostgreSQL-hackers and contributors put into this fantastic freeware 
product. Just to let you know.

Ciao

Ulrich





Ulrich Voss                            \ \   / /__  / ___|__ _| |
VoCal web publishing                    \ \ / / _ \| |   / _` | |
voss@vocalweb.de                         \ V / (_) | |__| (_| | |
http://www.vocalweb.de                    \_/ \___/ \____\__,_|_|
http://www.boersenspiel.de                         web publishing