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
thisproblem (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
ormore.  I changed that setting to 2, and things are moving along nicely  (though now Postgres does now chew up a *lot*
ofRAM when processing joins of 7 or more tables).  Queries that took 11 minutes are now finished in 6-8 seconds, which
stillseems 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
apermanent 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@spy.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. ____________
>
>
>