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

Re: BUG #4721: All sub-tables incorrectly included in search plan for partitioned table

From: "Thompson, Eric" <eric(dot)thompson(at)salliemae(dot)com>
To: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #4721: All sub-tables incorrectly included in search plan for partitioned table
Date: 2009-03-25 18:00:39
Message-ID: ECDEA8C1251CEA4BB2CA2C3A76BD2C2C6A7AF34B39@VFIEXMBX01.us.ad.usa-ed.net (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
Interesting...  it looks like there is a balance between CPU cycles and disk I/O.  I set the MAX_BRANCHES_TO_TEST to 120 and recompiled, so for me everything is fast again.   I do not know everything involved, but if there was a way to flag the constraints used for partitioning and always check those to avoid scanning child tables, that may help.  Thank you for the quick feedback, and I am happy that I could achieve a quick resolution.

Thanks again,
Eric


-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us] 
Sent: Saturday, March 21, 2009 1:44 AM
To: Thompson, Eric
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #4721: All sub-tables incorrectly included in search plan for partitioned table 

"Eric Thompson" <eric(dot)thompson(at)salliemae(dot)com> writes:
> test=# -- remove any irrelevant constraint from the master table, and now
> the date partitioning works 

Hmm.  Tracing through this, it seems your child tables have exactly 101
separate constraint clauses; removing one from the parent table gets it
down to 100.  Which is where the cutoff installed by this patch is:

http://archives.postgresql.org/pgsql-committers/2008-11/msg00146.php

That patch was in response to this complaint:

http://archives.postgresql.org/pgsql-general/2008-11/msg00446.php

I'm not entirely sure about a better approach; just moving the cutoff
around doesn't seem like it will do anything except change who's
complaining...

			regards, tom lane

This E-Mail has been scanned for viruses.

In response to

pgsql-hackers by date

Next:From: Josh BerkusDate: 2009-03-25 18:19:57
Subject: Re: shut down pgsql-interfaces (was Re: [HACKERS] Function C and INOUT parameters)
Previous:From: Sam MasonDate: 2009-03-25 17:56:02
Subject: Re: improving concurrent transactin commit rate

pgsql-bugs by date

Next:From: Wayne ConradDate: 2009-03-25 18:05:08
Subject: Re: BUG #4730: Vacuum full verbose analyze "deadlock"
Previous:From: Wayne ConradDate: 2009-03-25 17:54:50
Subject: Re: BUG #4730: Vacuum full verbose analyze "deadlock"

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