Re: Bug? Query plans / EXPLAIN using gigabytes of memory

Stacking views is a bad practice. It usually means that you are making the
db do a lot of unnecessary work, scanning tables more than once when you
don't even need them.
According to your description, you have 3 layers of views on partitioned
tables.
I can imagine that that leaves the planner with a lot of possible query
plans, a lot of interaction and a lot of statistics to read.

do you have any special settings for the statistics on these tables?
and could you please post the non-default settings in your postgresql.conf
file?
$ grep ^[^#] /etc/postgresql/9.1/main/postgresql.conf|grep -e ^[^[:space:]]

Would be helpful to see if you have any statistics or planner stuff altered.

Cheers,

WBL

On Wed, Apr 25, 2012 at 8:18 AM, Toby Corkindale <
toby(dot)corkindale(at)strategicdata(dot)com(dot)au> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth