Re: [HACKERS] Optimizer badness in 7.0 beta

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
before.)

> > 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@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden