Attached is some anonymized DDL for a fairly complex schema from a
PostgreSQL Experts client. Also attached is an explain query that runs
against the schema. The client's problem is that in trying to run the
explain, Postgres simply runs out of memory. On my untuned 9.3 test rig,
(Scientific Linux 6.4 with 24Gb of RAM and 24Gb of swap) vmstat clearly
shows the explain chewing up about 7Gb of memory. When it's done the
free memory jumps back to where it was. On a similar case on the clients
test rig we saw memory use jump lots more.
The client's question is whether this is not a bug. It certainly seems
like it should be possible to plan a query without chewing up this much
memory, or at least to be able to limit the amount of memory that can be
grabbed during planning. Going from humming along happily to OOM
conditions all through running "explain <somequery>" is not very friendly.
cheers
andrew