Skip site navigation (1) Skip section navigation (2)

Re: No hash join across partitioned tables?

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Bruce Momjian <bruce(at)momjian(dot)us>, Kris Jurka <books(at)ejurka(dot)com>, pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: No hash join across partitioned tables?
Date: 2010-10-16 05:03:02
Message-ID: 1287205016-sup-542@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
Excerpts from Robert Haas's message of mié jun 09 15:47:55 -0400 2010:

> In going back through emails I had marked as possibly needing another
> look before 9.0 is released, I came across this issue again.  As I
> understand it, analyze (or analyse) now collects statistics for both
> the parent individually, and for the parent and its children together.
>  However, as I further understand it, autovacuum won't actually fire
> off an analyze unless there's enough activity on the parent table
> considered individually to warrant it.  So if you have an empty parent
> and a bunch of children with data in it, your stats will still stink,
> unless you analyze by hand.

So, is there something we could now do about this, while there's still
time before 9.1?

I haven't followed this issue very closely, but it seems to me that what
we want is that we want an ANALYZE in a child table to be mutated into
an analyze of its parent table, if the conditions are right; and that an
ANALYZE of a parent removes the child tables from being analyzed on the
same run.

If we analyze the parent, do we also update the children stats, or is it
just that we keep two stats for the parent, one with children and one
without, both being updated when the parent is analyzed?

If the latter's the case, maybe we should modify ANALYZE a bit more, so
that we can analyze the whole hierarchy in one go, and store the lot of
stats with a single pass (each child alone, the parent alone, the parent
plus children).  However it's not real clear how would this work with
multiple inheritance levels.

-- 
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2010-10-16 05:22:42
Subject: Re: No hash join across partitioned tables?
Previous:From: AndyDate: 2010-10-16 03:46:49
Subject: Re: UUID performance as primary key

pgsql-hackers by date

Next:From: Tom LaneDate: 2010-10-16 05:22:42
Subject: Re: No hash join across partitioned tables?
Previous:From: Alvaro HerreraDate: 2010-10-16 03:56:49
Subject: Re: Extensions, this time with a patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group