From: | David Link <dlink(at)soundscan(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: EXPLAIN SELECT .. does not return |
Date: | 2005-12-05 22:11:08 |
Message-ID: | 4394BAFC.5030406@soundscan.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Tom Lane wrote:
>David Link <dlink(at)soundscan(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-12-05 22:15:31 | Re: Strange VACUUM behaviour |
Previous Message | David Link | 2005-12-05 22:07:28 | Re: EXPLAIN SELECT .. does not return |