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. ____________
>
>
>