Re: Weird query plans for my queries,

I suppose you meant running this:

explain SELECT F_Topics.TopicID,F_Topics.StatusID, F_Users.UserID
FROM F_Topics JOIN F_Users ON F_Topics.UserID = F_Users.UserID
WHERE ((F_Topics.StatusID IN(1) AND F_Topics.Deleted = false AND
F_Topics.ForumID = 15) OR (F_Topics.Lastmessage > '2002-08-01
23:27:03+02' AND F_Topics.Deleted =
false AND F_Topics.ForumID = 15))

Did indeed produce the required query plan, even after adding a second
(left) join to the F_Users table.
You might like to know that mysql takes around 330ms to complete the
above query, after allowing it to fetch and cache anything it wants
(initial runs take over half a second, which is why we chose the
separate queries) while postgresql on the same machines finishes it in
12ms...

The mysql-multi-query variant takes around 45ms of time for the queries,
while the single query postgresvariant (including more advanced results
and another join) does the same thing in the same time.
Though with much less code-iterations involved.

Thanks for your help,

Regards,

Arjen van der Meijden

> -----Oorspronkelijk bericht-----
> Van: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Verzonden: vrijdag 31 januari 2003 16:31
> Aan: Arjen van der Meijden
> CC: pgsql-general(at)postgresql(dot)org
> Onderwerp: Re: [GENERAL] Weird query plans for my queries,
> causing terrible performance.
>
>
> Arjen van der Meijden <acm(at)tweakers(dot)net> writes:
> > That is weird, a copy&paste of your command into my psql
> results in:
> > [different results]
>
> Hm, there must be some difference on this query between 7.3
> and CVS tip then; I was not expecting that. [ ... a debugger
> is fired up ... time passes ... ]
>
> Ah hah. There's a rather shaky heuristic in
> canonicalize_qual that prefers DNF if certain things are
> true, one of them being that the qual condition mentions only
> one relation. This test is being fooled because you wrote
> some of the variables with "F_Topics." and some without
> (which means that, according to the letter of the SQL spec,
> they refer to the join relation's result and not the original
> table). I find that 7.3 will produce the desired plan if I
> write all the variables in the WHERE clause the same way,
> either with or without "F_Topics.". CVS tip doesn't show
> this effect because it handles join variables differently.
>
> So that's your workaround for the moment. As I was saying,
> this code could use some fresh ideas...
>
> regards, tom lane
>