Tom Lane wrote:
David Link <dlink@xxxxxxxxxxxxx> writes:
Certain SQL Queries, I believe those with many table joins, when run as
EXPLAIN plans, never return.
I'd guess that one or all of these settings are excessive:
geqo_threshold = 14
from_collapse_limit = 13
join_collapse_limit = 13
Keep in mind that the planning cost is exponential in these limits,
eg geqo_threshold = 14 probably allows planning times about 14 times
greater than geqo_threshold = 13.
While I'm looking:
shared_buffers = 2000
That seems extremely low for modern machines.
sort_mem = 1048576
That, on the other hand, is almost certainly way too high for a system-wide
setting. You're promising you have 1Gb available for *each* sort.
max_fsm_pages = 100000
And this way too low for a 100Gb database, unless most of the tables
never see any UPDATEs or DELETEs.
wal_buffers = 800
Seems a bit high, especially considering you have fsync disabled and
thus there is no benefit whatever to buffering WAL.
commit_delay = 100
commit_siblings = 50
Have you measured any benefit to having this turned on?
All in all it looks like your configuration settings were chosen by
throwing darts :-(
regards, tom lane
Thanks for your reply, Tom. Different folks have made different
suggestions. Can you suggest more reasonable values for these? But
more importantly, do you think the problem I am having is due to these
configuration short comings?
Thanks much.
David
