Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Optimizer badness in 7.0 beta

From: Peter Eisentraut <e99re41(at)DoCS(dot)UU(dot)SE>
To: Brian Hirt <bhirt(at)mobygames(dot)com>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] Optimizer badness in 7.0 beta
Date: 2000-03-06 07:05:52
Message-ID: Pine.GSO.4.02A.10003060759180.17581-100000@Svan.DoCS.UU.SE (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
On Sun, 5 Mar 2000, Brian Hirt wrote:

> Actually, the query you supply will not work, I'll get duplicate 
> rows because the relationship between game and game_developer is 
> a one to many.

Got me. I tried to read into it a little like 'one developer develops many
games' but apparently it's the other way around. In that case you could
use DISTINCT or maybe DISTINCT ON depending on the details.

> I dont know too much about the PG internals, but when I used sybase, 
> it would usually execute the sub-select independently and stuff the 
> results into a temp table and then do another query, joining to the 
> results of the sub-select.

Last time I checked PostgreSQL executes the subquery for each row.
Apparently it must still be doing that and I do suspect that it is right
in the overall sense because the subquery may have side effects. Consider

SELECT * FROM t1 WHERE id IN (select nextval('my_sequence'))

Of course this query makes absolutely no sense whatsoever but perhaps
there are similar ones where it does.

But I didn't mean to bash your query style, just pointing out a
work-around that's commonly suggested. (People have been caught by this

> > SELECT creation_timestamp, etc. 
          ^^ insert DISTINCT
> > FROM game, game_developer
> > WHERE game.game_id = game_developer.game_id
> >   AND approved = 1 AND developer_id = 3
> > ORDER BY copyright_year desc, game_title

Peter Eisentraut                  Sernanders väg 10:115
peter_e(at)gmx(dot)net                   75262 Uppsala            Sweden

In response to


pgsql-hackers by date

Next:From: Philip WarnerDate: 2000-03-06 07:27:34
Subject: Re: [HACKERS] DROP TABLE inside a transaction block
Previous:From: Peter EisentrautDate: 2000-03-06 06:59:00
Subject: Re: [HACKERS] DROP TABLE inside a transaction block

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group