Re: [GENERAL] big bad join problems

Dustin,

Check the digest of the pgsql-hackers list for the past couple of days. Some of the developers have been discussing this problem (which I was complaining about last week).

The issue is with the builtin settings for the GEQO optimizer. The default is set to "turn on" at queries of 8 tables or more. I changed that setting to 2, and things are moving along nicely (though now Postgres does now chew up a *lot* of RAM when processing joins of 7 or more tables). Queries that took 11 minutes are now finished in 6-8 seconds, which still seems a bit slow but is acceptable to me for the time being.

You can set GEQO in psql using the command

SET geqo TO 'on=n';

To check your current settings, do

SHOW geqo;

Beware that using the psql SET command only makes changes on a per_session basis, In order to change the default GEQO setting, I believe that you have to rebuild the binary and change the default setting in /src/include/optimizer/internal.h

We weren't able to find any documentation about command-line options to postmaster or other methods of changing GEQO on a permanent basis.

The line you want to modify in internal.h is:

/* GEQO switch according to number of relations in a query */
#define GEQO_RELS 8

I didn't do the recompile myself, but I think you have to rebuild the entire Postgres binary.

Charlie

At 11:18 AM 2/1/99 -0800, Dustin Sallings wrote:
>
> Two messages floated by here last week describing similar
>optimizer problems, and I didn't see any responses to them. One of them
>was mine and it's causing some bad delays in my first attempt at running
>Postgres in production at work. The following query sits in the optimizer
>(even just doing an explain) for an unreasonably long amount of time,
>regardless of the amount of data in any of the tables:
>
> select events.event_id, events.hostdate, events.ts, events.priority,
> tags.tag_name, events.stack,
> messages.message,
> usernames.user_name as user_name,
> wwwusers.user_name as wwwuser,
> files.file_name as filename,
> scripts.file_name as scriptname,
> events.linenum
> from events, tags, messages, usernames, usernames as wwwusers,
> files, files as scripts
> where events.tag_id=tags.tag_id
> and events.message_id=messages.message_id
> and usernames.user_id = events.user_id
> and wwwusers.user_id = events.wwwuser_id
> and files.file_id = events.file_id
> and scripts.file_id = events.script_id
>
>I sent a script to create the database (and this view) in my last message.
>Does anyone have any idea what's causing this problem, or how to fix it?
>I've done larger joins before... Anyway, I'm going to have to denormalize
>my database some to work around this.
>
>--
>SA, beyond.com My girlfriend asked me which one I like better.
>pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin(at)spy(dot)net>
>| Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE
>L_______________________ I hope the answer won't upset her. ____________
>
>
>