Search Postgresql Archives

Re: EXPLAIN SELECT .. does not return

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux