Re: [INTERFACES] Slow join query optimisation?

> Douglas Thomson <dougt(at)mugc(dot)cc(dot)monash(dot)edu(dot)au> writes:
> > My question for the list: Is there something in the query optimiser
> > that will take a time that is something like exponential on the number
> > of tables being joined?
>
> Yes. See the manual's discussion of genetic query optimization. You
> might try setting the GEQO threshold to something less than 10.

This seemed to make the query take *much* longer (like about five
times as long as before!) even with empty tables?

> FWIW, the time you are looking at here is strictly planning time and
> will not increase when you actually put data in the tables. Unless
> you foresee fairly small final tables, the planning time is not likely
> to be a significant factor in this range of query sizes.

That would be true if I were typically selecting a large proportion
of the table data. However, in my application, a typical query only
retrieves 50 tuples. The query optimisation therefore dominates the
total time. I don't think the absolute table size matters much, since
the tables are indexed.

Here is a summary of my 9-table query timings:

GEQO=11 GEQO=3
======= ======
Empty tables (hence 0 tuples selected) 0.94s 5.09s
Full tables (26 tuples selected) 0.98s 5.15s

These times include executing the SELECT, which (via libpq) means
that it also includes the fetch times for all the selected tuples).

> Still, if the purpose of the join is just to substitute descriptions
> for IDs, you'd be well advised to consider doing it via table lookup
> on the frontend side, assuming your frontend is coded in a language
> that makes that reasonable to do.

The drawback for me here is that either I must load up all the lookup
tables (when I may only need a few of the values from each of them),
or else I have to look up just the values I need (which is possible
but messy).

Is there any way to turn off the optimisation? Or perhaps some way to
work out the optimal strategy once, and then provide this information
directly? After all, presumably the optimiser will work out the same
answer every time (for the same table structures and the same SELECT)
so it seems pointless doing this work every time the SELECT gets
executed...

Doug.