Re: EXPLAIN SELECT .. does not return

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