On Thu, Oct 24, 2002 at 03:31:57PM -0500, Ben McMahan wrote:
> I'm looking at different ways of optimizing queries with a large number of
> joins. I write the same query in a number of different ways and compare
> the running times. Now the problem is I do not want the optimizer
> changing the queries. So I explicit state the order of the joins in the
> FROM clause. I also turn off everything I can except for one type of join
> (say hash join), and I've turned off geqo. But I find that the PLANNER
> still takes an enormous amount of time for some queries. It doesn't look
> like the Planner is actually optimizing (changing) anything, but just in
> case, I was wondering if there was a way to turn off the PLANNER.
Umm, you are analysing, right? Secondly, you can get improvements by playing
with the Genetic Query Optimiser. Thirdly, what does the EXPLAIN output
show. Fourthly, if you use SQL functions you can arrange for the planning to
be only done once.
Hope this helps,
>
> Note, when I say an enormous amount of time, I mean at least double the
> time the EXECUTOR takes to actually answer the query.
>
> Thanks for your help,
>
> Ben McMahan
>
> ps. here is a small example of what my queries look like (so you can see
> if there is something else it might be deciding on):
>
> SELECT DISTINCT c0.x1 , c1.x2 , c0.x3 , c0.x4 , c2.x5
> FROM r1 c4 (x4,x2,x5) JOIN (r0 c3 (x2,x3,x5) JOIN (r2 c2 (x3,x1,x5) JOIN (r1 c1
> (x4,x1,x2) JOIN r1 c0 (x1,x3,x4)
> ON ( c0.x4 = c1.x4 AND c0.x1 = c1.x1 ))
> ON ( c0.x3 = c2.x3 AND c0.x1 = c2.x1 ))
> ON ( c1.x2 = c3.x2 AND c0.x3 = c3.x3 AND c2.x5 = c3.x5 ))
> ON ( c0.x4 = c4.x4 AND c1.x2 = c4.x2 AND c2.x5 = c4.x5 );
>
> A quick reminder, FROM r1 c4 (x4,x2,x5) just renames a table r1 into c4
> where it also renames the columns to x4, x2, and x5 respectively.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.
