pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: No hash join across partitioned tables?
23+ messages / 6 participants
[nested] [flat]
* Re: No hash join across partitioned tables?
@ 2010-02-25 23:46 Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 23+ messages in thread
From: Bruce Momjian @ 2010-02-25 23:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Kris Jurka <books@ejurka.com>; pgsql-performance
Did this get addressed?
---------------------------------------------------------------------------
Tom Lane wrote:
> Kris Jurka <books@ejurka.com> writes:
> > The real problem is getting reasonable stats to pass through the partition
> > Append step, so it can make a reasonable estimate of the join output size.
>
> I dug around a bit and concluded that the lack of stats for the Append
> relation is indeed the main problem. It's not so much the bad join size
> estimate (although that could hurt for cases where you need to join this
> result to another table). Rather, it's that the planner is deliberately
> biased against picking hash joins in the absence of stats for the inner
> relation. Per the comments for estimate_hash_bucketsize:
>
> * If no statistics are available, use a default estimate of 0.1. This will
> * discourage use of a hash rather strongly if the inner relation is large,
> * which is what we want. We do not want to hash unless we know that the
> * inner rel is well-dispersed (or the alternatives seem much worse).
>
> While we could back off the default a bit here, I think it'd be better
> to fix it by not punting on the stats-for-append-relations problem.
> That doesn't seem like material for 8.4 at this point, though.
>
> regards, tom lane
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: No hash join across partitioned tables?
@ 2010-02-26 00:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 23+ messages in thread
From: Tom Lane @ 2010-02-26 00:03 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Kris Jurka <books@ejurka.com>; pgsql-performance
Bruce Momjian <bruce@momjian.us> writes:
> Did this get addressed?
Partially. There are stats now but autovacuum is not bright about
when to update them.
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: No hash join across partitioned tables?
@ 2010-03-02 16:16 Robert Haas <robertmhaas@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 23+ messages in thread
From: Robert Haas @ 2010-03-02 16:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <bruce@momjian.us>; Kris Jurka <books@ejurka.com>; pgsql-performance
On Thu, Feb 25, 2010 at 7:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Bruce Momjian <bruce@momjian.us> writes:
>> Did this get addressed?
>
> Partially. There are stats now but autovacuum is not bright about
> when to update them.
Is that something you're planning to fix for 9.0? If not, we at least
need to document what we intend for people to do about it.
...Robert
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: No hash join across partitioned tables?
@ 2010-03-02 16:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 2 replies; 23+ messages in thread
From: Tom Lane @ 2010-03-02 16:23 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Bruce Momjian <bruce@momjian.us>; Kris Jurka <books@ejurka.com>; pgsql-performance
Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Feb 25, 2010 at 7:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Partially. There are stats now but autovacuum is not bright about
>> when to update them.
> Is that something you're planning to fix for 9.0? If not, we at least
> need to document what we intend for people to do about it.
I want to look at it, but I'm not sure whether the fix will be small
enough that we want to put it in during beta.
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: No hash join across partitioned tables?
@ 2010-03-02 16:27 Grzegorz Jaśkiewicz <gryzman@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 23+ messages in thread
From: Grzegorz Jaśkiewicz @ 2010-03-02 16:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Robert Haas <robertmhaas@gmail.com>; Bruce Momjian <bruce@momjian.us>; Kris Jurka <books@ejurka.com>; pgsql-performance
On Tue, Mar 2, 2010 at 4:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
> > On Thu, Feb 25, 2010 at 7:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> Partially. There are stats now but autovacuum is not bright about
> >> when to update them.
>
> > Is that something you're planning to fix for 9.0? If not, we at least
> > need to document what we intend for people to do about it.
>
> I want to look at it, but I'm not sure whether the fix will be small
> enough that we want to put it in during beta.
>
> I am pretty sure many people will appreciate it, even if it isn't going to
be small.
Is that stat collection across child tables any useful by it self ?
--
GJ
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: No hash join across partitioned tables?
@ 2010-06-09 19:47 Robert Haas <robertmhaas@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 23+ messages in thread
From: Robert Haas @ 2010-06-09 19:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <bruce@momjian.us>; Kris Jurka <books@ejurka.com>; pgsql-performance
On Tue, Mar 2, 2010 at 12:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Thu, Feb 25, 2010 at 7:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Partially. There are stats now but autovacuum is not bright about
>>> when to update them.
>
>> Is that something you're planning to fix for 9.0? If not, we at least
>> need to document what we intend for people to do about it.
>
> I want to look at it, but I'm not sure whether the fix will be small
> enough that we want to put it in during beta.
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.
Assuming my understanding of the problem is correct, we could:
(a) fix it,
(b) document that you should consider periodic manual analyze commands
in this situation, or
(c) do nothing.
Thoughts?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: No hash join across partitioned tables?
@ 2010-06-09 20:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Robert Haas <robertmhaas@gmail.com>
1 sibling, 2 replies; 23+ messages in thread
From: Tom Lane @ 2010-06-09 20:11 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Bruce Momjian <bruce@momjian.us>; Kris Jurka <books@ejurka.com>; pgsql-performance
Robert Haas <robertmhaas@gmail.com> writes:
> 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.
Check.
> Assuming my understanding of the problem is correct, we could:
> (a) fix it,
> (b) document that you should consider periodic manual analyze commands
> in this situation, or
> (c) do nothing.
> Thoughts?
The objections to (a) are that it might result in excessive ANALYZE work
if not done intelligently, and that we haven't got a patch ready anyway.
I would have liked to get to this for 9.0 but I feel it's a bit late
now.
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: [PERFORM] No hash join across partitioned tables?
@ 2010-06-10 13:29 Robert Haas <robertmhaas@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 23+ messages in thread
From: Robert Haas @ 2010-06-10 13:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgresql.org
(moving to -hackers)
On Wed, Jun 9, 2010 at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> 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.
>
> Check.
>
>> Assuming my understanding of the problem is correct, we could:
>
>> (a) fix it,
>> (b) document that you should consider periodic manual analyze commands
>> in this situation, or
>> (c) do nothing.
>
>> Thoughts?
>
> The objections to (a) are that it might result in excessive ANALYZE work
> if not done intelligently, and that we haven't got a patch ready anyway.
> I would have liked to get to this for 9.0 but I feel it's a bit late
> now.
I guess I can't really disagree with that. Should we try to document
this in some way?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: [PERFORM] No hash join across partitioned tables?
@ 2010-06-14 03:47 Robert Haas <robertmhaas@gmail.com>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Robert Haas @ 2010-06-14 03:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgresql.org
On Thu, Jun 10, 2010 at 9:29 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> (moving to -hackers)
>
> On Wed, Jun 9, 2010 at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Robert Haas <robertmhaas@gmail.com> writes:
>>> 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.
>>
>> Check.
>>
>>> Assuming my understanding of the problem is correct, we could:
>>
>>> (a) fix it,
>>> (b) document that you should consider periodic manual analyze commands
>>> in this situation, or
>>> (c) do nothing.
>>
>>> Thoughts?
>>
>> The objections to (a) are that it might result in excessive ANALYZE work
>> if not done intelligently, and that we haven't got a patch ready anyway.
>> I would have liked to get to this for 9.0 but I feel it's a bit late
>> now.
>
> I guess I can't really disagree with that. Should we try to document
> this in some way?
Proposed patch attached.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
Attachments:
analyze-inherit-docs.patch (application/octet-stream, 1.0K) download
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index a4002d5..5ad1eaa 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -176,6 +176,17 @@ ANALYZE [ VERBOSE ] [ <replaceable class="PARAMETER">table</replaceable> [ ( <re
<command>ALTER TABLE ... ALTER COLUMN ... SET (n_distinct = ...)</>
(see <xref linkend="sql-altertable">).
</para>
+
+ <para>
+ If the table being analyzed has one or more children,
+ <command>ANALYZE</command> will gather statistics twice: once on the
+ rows of the parent table only, and a second time on the rows of the
+ parent table with all of its children. The autovacuum daemon, however,
+ will only consider inserts or updates on the parent table when deciding
+ whether to trigger an automatic analyze. If that table is rarely
+ inserted into or updated, the inheritance statistics will not be up to date
+ unless you run <command>ANALYZE</command> manually.
+ </para>
</refsect1>
<refsect1>
* Re: [PERFORM] No hash join across partitioned tables?
@ 2010-06-15 18:44 Robert Haas <robertmhaas@gmail.com>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 23+ messages in thread
From: Robert Haas @ 2010-06-15 18:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgresql.org
On Sun, Jun 13, 2010 at 11:47 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> Proposed patch attached.
Hearing no objections, I have committed this patch.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: No hash join across partitioned tables?
@ 2010-07-02 03:00 Bruce Momjian <bruce@momjian.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 23+ messages in thread
From: Bruce Momjian @ 2010-07-02 03:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Robert Haas <robertmhaas@gmail.com>; Kris Jurka <books@ejurka.com>; pgsql-performance
Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
> > 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.
>
> Check.
>
> > Assuming my understanding of the problem is correct, we could:
>
> > (a) fix it,
> > (b) document that you should consider periodic manual analyze commands
> > in this situation, or
> > (c) do nothing.
>
> > Thoughts?
>
> The objections to (a) are that it might result in excessive ANALYZE work
> if not done intelligently, and that we haven't got a patch ready anyway.
> I would have liked to get to this for 9.0 but I feel it's a bit late
> now.
What do we want to do about the above issue?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: No hash join across partitioned tables?
@ 2010-07-02 04:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 23+ messages in thread
From: Tom Lane @ 2010-07-02 04:05 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Robert Haas <robertmhaas@gmail.com>; Kris Jurka <books@ejurka.com>; pgsql-performance
Bruce Momjian <bruce@momjian.us> writes:
> Tom Lane wrote:
>> I would have liked to get to this for 9.0 but I feel it's a bit late
>> now.
> What do we want to do about the above issue?
TODO item.
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: No hash join across partitioned tables?
@ 2010-07-02 20:53 Bruce Momjian <bruce@momjian.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 23+ messages in thread
From: Bruce Momjian @ 2010-07-02 20:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Robert Haas <robertmhaas@gmail.com>; Kris Jurka <books@ejurka.com>; pgsql-performance
Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Tom Lane wrote:
> >> I would have liked to get to this for 9.0 but I feel it's a bit late
> >> now.
>
> > What do we want to do about the above issue?
>
> TODO item.
Added to TODO:
Have autoanalyze of parent tables occur when child tables are modified
* http://archives.postgresql.org/message-id/AANLkTinx8lLTEKWcyEQ1rxVz6WMJVKNezfXW5TKnNAU6@mail.gmail.c...
I am surprised there is no documentation update requirement for this.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: No hash join across partitioned tables?
@ 2010-07-02 20:58 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 23+ messages in thread
From: Tom Lane @ 2010-07-02 20:58 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Robert Haas <robertmhaas@gmail.com>; Kris Jurka <books@ejurka.com>; pgsql-performance
Bruce Momjian <bruce@momjian.us> writes:
> I am surprised there is no documentation update requirement for this.
Somebody put something about it in the docs a few days ago, IIRC.
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: No hash join across partitioned tables?
@ 2010-07-02 21:10 Robert Haas <robertmhaas@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 23+ messages in thread
From: Robert Haas @ 2010-07-02 21:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <bruce@momjian.us>; Kris Jurka <books@ejurka.com>; pgsql-performance
On Fri, Jul 2, 2010 at 4:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Bruce Momjian <bruce@momjian.us> writes:
>> I am surprised there is no documentation update requirement for this.
>
> Somebody put something about it in the docs a few days ago, IIRC.
That was me.
http://archives.postgresql.org/pgsql-committers/2010-06/msg00144.php
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: No hash join across partitioned tables?
@ 2010-07-02 21:12 Bruce Momjian <bruce@momjian.us>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 23+ messages in thread
From: Bruce Momjian @ 2010-07-02 21:12 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Kris Jurka <books@ejurka.com>; pgsql-performance
Robert Haas wrote:
> On Fri, Jul 2, 2010 at 4:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Bruce Momjian <bruce@momjian.us> writes:
> >> I am surprised there is no documentation update requirement for this.
> >
> > Somebody put something about it in the docs a few days ago, IIRC.
>
> That was me.
>
> http://archives.postgresql.org/pgsql-committers/2010-06/msg00144.php
Oh, thanks, I missed that.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: No hash join across partitioned tables?
@ 2010-10-16 05:03 Alvaro Herrera <alvherre@commandprompt.com>
parent: Robert Haas <robertmhaas@gmail.com>
1 sibling, 1 reply; 23+ messages in thread
From: Alvaro Herrera @ 2010-10-16 05:03 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Bruce Momjian <bruce@momjian.us>; Kris Jurka <books@ejurka.com>; pgsql-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@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: No hash join across partitioned tables?
@ 2010-10-16 05:22 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 2 replies; 23+ messages in thread
From: Tom Lane @ 2010-10-16 05:22 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Robert Haas <robertmhaas@gmail.com>; Bruce Momjian <bruce@momjian.us>; Kris Jurka <books@ejurka.com>; pgsql-performance
Alvaro Herrera <alvherre@commandprompt.com> writes:
> 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?
The latter.
The trick here is that we need to fire an analyze on the parent even
though only its children may have had any updates.
> 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.
It's also not clear how it works without blowing out memory...
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: No hash join across partitioned tables?
@ 2010-10-16 05:35 Samuel Gendler <sgendler@ideasculptor.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 23+ messages in thread
From: Samuel Gendler @ 2010-10-16 05:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; Robert Haas <robertmhaas@gmail.com>; Bruce Momjian <bruce@momjian.us>; Kris Jurka <books@ejurka.com>; pgsql-performance
On Fri, Oct 15, 2010 at 10:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > 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?
>
> The latter.
>
> The trick here is that we need to fire an analyze on the parent even
> though only its children may have had any updates.
>
> > 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.
>
An issue with automatically analyzing the entire hierarchy is 'abstract'
table definitions. I've got a set of tables for storing the same data at
different granularities of aggregation. Within each granularity, I've got
partitions, but because the set of columns is identical for each
granularity, I've got an abstract table definition that is inherited by
everything. I don't need or want statistics kept on that table because I
never query across the abstract table, only the parent table of each
aggregation granularity
create table abstract_fact_table (
time timestamp,
measure1 bigint,
measure2 bigint,
measure3 bigint,
fk1 bigint,
fk2 bigint
);
create table minute_scale_fact_table (
} inherits abstract_fact_table;
// Then there are several partitions for minute scale data
create table hour_scale_fact_table (
) inherits abstract_fact_table;
// then several partitions for hour scale data
etc. I do run queries on the minute_scale_fact_table and
hour_scale_fact_table but never do so on abstract_fact_table. I could
certainly modify my schema such that the abstract table goes away entirely
easily enough, but I find this easier for new developers to come in and
comprehend, since the similarity between the table definitions is explicit.
I'm glad this topic came up, as I was unaware that I need to run analyze on
the parent partitions separately - and no data is every inserted directly
into the top level of each granularity hierarchy, so it will never fire by
itself.
If I am using ORM and I've got functionality in a common baseclass in the
source code, I'll often implement its mapping in the database via a parent
table that the table for any subclass mapping can inherit from. Again, I
have no interest in maintaining statistics on the parent table, since I
never query against it directly.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: No hash join across partitioned tables?
@ 2010-10-16 15:29 Alvaro Herrera <alvherre@commandprompt.com>
parent: Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 1 reply; 23+ messages in thread
From: Alvaro Herrera @ 2010-10-16 15:29 UTC (permalink / raw)
To: Samuel Gendler <sgendler@ideasculptor.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Robert Haas <robertmhaas@gmail.com>; Bruce Momjian <bruce@momjian.us>; Kris Jurka <books@ejurka.com>; pgsql-performance
Excerpts from Samuel Gendler's message of sáb oct 16 02:35:46 -0300 2010:
> An issue with automatically analyzing the entire hierarchy is 'abstract'
> table definitions. I've got a set of tables for storing the same data at
> different granularities of aggregation. Within each granularity, I've got
> partitions, but because the set of columns is identical for each
> granularity, I've got an abstract table definition that is inherited by
> everything. I don't need or want statistics kept on that table because I
> never query across the abstract table, only the parent table of each
> aggregation granularity
Hmm, I think you'd be better served by using LIKE instead of regular
inheritance.
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: No hash join across partitioned tables?
@ 2010-10-18 06:13 Samuel Gendler <sgendler@ideasculptor.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 23+ messages in thread
From: Samuel Gendler @ 2010-10-18 06:13 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Robert Haas <robertmhaas@gmail.com>; Bruce Momjian <bruce@momjian.us>; Kris Jurka <books@ejurka.com>; pgsql-performance
On Sat, Oct 16, 2010 at 8:29 AM, Alvaro Herrera
<alvherre@commandprompt.com>wrote:
> Excerpts from Samuel Gendler's message of sáb oct 16 02:35:46 -0300 2010:
>
> > An issue with automatically analyzing the entire hierarchy is 'abstract'
> > table definitions. I've got a set of tables for storing the same data at
> > different granularities of aggregation. Within each granularity, I've
> got
> > partitions, but because the set of columns is identical for each
> > granularity, I've got an abstract table definition that is inherited by
> > everything. I don't need or want statistics kept on that table because I
> > never query across the abstract table, only the parent table of each
> > aggregation granularity
>
> Hmm, I think you'd be better served by using LIKE instead of regular
> inheritance.
>
>
Yep. I inherited the architecture, though, and changing it hasn't been a
high priority.
--sam
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: No hash join across partitioned tables?
@ 2010-10-18 14:44 Alvaro Herrera <alvherre@commandprompt.com>
parent: Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 0 replies; 23+ messages in thread
From: Alvaro Herrera @ 2010-10-18 14:44 UTC (permalink / raw)
To: Samuel Gendler <sgendler@ideasculptor.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Robert Haas <robertmhaas@gmail.com>; Bruce Momjian <bruce@momjian.us>; Kris Jurka <books@ejurka.com>; pgsql-performance
Excerpts from Samuel Gendler's message of lun oct 18 03:13:01 -0300 2010:
> On Sat, Oct 16, 2010 at 8:29 AM, Alvaro Herrera
> <alvherre@commandprompt.com>wrote:
>
> > Excerpts from Samuel Gendler's message of sáb oct 16 02:35:46 -0300 2010:
> >
> > > An issue with automatically analyzing the entire hierarchy is
> > > 'abstract' table definitions. I've got a set of tables for
> > > storing the same data at different granularities of aggregation.
> > > Within each granularity, I've got partitions, but because the set
> > > of columns is identical for each granularity, I've got an abstract
> > > table definition that is inherited by everything. I don't need or
> > > want statistics kept on that table because I never query across
> > > the abstract table, only the parent table of each aggregation
> > > granularity
> >
> > Hmm, I think you'd be better served by using LIKE instead of regular
> > inheritance.
>
> Yep. I inherited the architecture, though, and changing it hasn't been a
> high priority.
I understand that; my point is merely that maybe we shouldn't work
through many hoops to solve this particular facet of the problem,
because it seems to be pilot error. (If you really needed to avoid the
extra I/O that would be caused by unnecessary analyzes, you could turn
autovac off for the abstract tables).
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: No hash join across partitioned tables?
@ 2010-10-26 12:23 Robert Haas <robertmhaas@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 23+ messages in thread
From: Robert Haas @ 2010-10-26 12:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; Bruce Momjian <bruce@momjian.us>; Kris Jurka <books@ejurka.com>; pgsql-performance
On Sat, Oct 16, 2010 at 1:22 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> 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?
>
> The latter.
>
> The trick here is that we need to fire an analyze on the parent even
> though only its children may have had any updates.
Can we execute a SQL query at the point where we need this
information? Because it doesn't seem too hard to work up a query that
totals the inserts, updates, and reltuples across all children of each
table.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 23+ messages in thread
end of thread, other threads:[~2010-10-26 12:23 UTC | newest]
Thread overview: 23+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-25 23:46 Re: No hash join across partitioned tables? Bruce Momjian <bruce@momjian.us>
2010-02-26 00:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-03-02 16:16 ` Robert Haas <robertmhaas@gmail.com>
2010-03-02 16:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-03-02 16:27 ` Grzegorz Jaśkiewicz <gryzman@gmail.com>
2010-06-09 19:47 ` Robert Haas <robertmhaas@gmail.com>
2010-06-09 20:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-06-10 13:29 ` Robert Haas <robertmhaas@gmail.com>
2010-06-14 03:47 ` Robert Haas <robertmhaas@gmail.com>
2010-06-15 18:44 ` Robert Haas <robertmhaas@gmail.com>
2010-07-02 03:00 ` Bruce Momjian <bruce@momjian.us>
2010-07-02 04:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-07-02 20:53 ` Bruce Momjian <bruce@momjian.us>
2010-07-02 20:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-07-02 21:10 ` Robert Haas <robertmhaas@gmail.com>
2010-07-02 21:12 ` Bruce Momjian <bruce@momjian.us>
2010-10-16 05:03 ` Alvaro Herrera <alvherre@commandprompt.com>
2010-10-16 05:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-10-16 05:35 ` Samuel Gendler <sgendler@ideasculptor.com>
2010-10-16 15:29 ` Alvaro Herrera <alvherre@commandprompt.com>
2010-10-18 06:13 ` Samuel Gendler <sgendler@ideasculptor.com>
2010-10-18 14:44 ` Alvaro Herrera <alvherre@commandprompt.com>
2010-10-26 12:23 ` Robert Haas <robertmhaas@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox