big bad join problems
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. ____________
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:
Show quoted text
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_idI 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. ____________