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
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
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
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(at)gmx(dot)net 75262 Uppsala
http://yi.org/peter-e/ Sweden

In response to

Responses

Browse pgsql-hackers by date

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