Hi,
Just wondering if anyone else has thoughts on this?
I'm still suspicious that this is a bug.
If I run EXPLAIN (or the query itself) on a database that has all the
schemas and tables created, but just the relevant data touched by the
query loaded.. then everything is fine.
The query plan is still hundreds of lines long, but running it doesn't
use much RAM. So I think that eliminates work_mem-related issues.
It really does seem like it's purely the query plan itself that is
consuming all the memory.
Has anyone else seen this?
Thanks,
Toby
On 25/04/12 16:18, Toby Corkindale wrote:
Hi,
I'm hitting some peculiar behaviour. I'm currently on Pg 9.1.3 on a 64bit
Debian system.
I have a database which is moderately large - 20 GByte or so - and contains
that data split up over dozens of tables, which are themselves partitioned.
Queries are usually only run against fairly small, partitioned, sets of data.
These queries generally run fairly fast. Performance is not a problem.
However Postgres is chewing up huge amounts of memory just to create the query
plan!
For example, even if I just run
EXPLAIN SELECT a_column FROM a_view
WHERE partition_id = 1;
Then the postgres backend process takes several seconds to return, and in the
worst example here, is hogging more than 3Gbyte once it comes back. (It doesn't
free that up until you close the connection)
The query plan that comes back does seem quite convoluted, but then, the view
is a query run over about eight other views, each of which is pulling data from
a few other views. The actual underlying data being touched is only *a few
dozen* small rows.
As I said, the query runs fast enough.. however we only need a handful of these
queries to get run in separate connections, and the database server will be
exhausted of memory. Especially since the memory isn't returned until the end
of the connection, yet these connections typically stay up for a while.
I wondered if there's anything I can do to reduce this memory usage? And, is
this a bug?
I've posted the output of the query plan here: https://gist.github.com/2487097
Thanks in advance,
Toby
--
.signature
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
