Message-ID: <51CC8FD4.10401@optionshouse.com>
Date: Thu, 27 Jun 2013 14:17:40 -0500
From: Shaun Thomas <sthomas@optionshouse.com>
Reply-To: <sthomas@optionshouse.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20130510 Thunderbird/17.0.6
MIME-Version: 1.0
To: "Albin, Lloyd P" <lalbin@scharp.org>
CC: Dave Johansen <davejohansen@gmail.com>, bricklen <bricklen@gmail.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Partitions not Working as Expected
References: <51CC652F.3010304@optionshouse.com>
 <A76B25F2823E954C9E45E32FA49D70EC1BBD8EEA@mail.corp.perceptron.com>
 <51CC73B6.2050406@optionshouse.com>
 <CAGrpgQ_3pCLt3CJnap3z+nWe3-Q=A3sMH7OKP0sUeo79kViD8w@mail.gmail.com>
 <CAAcYxUfCaYbpAv-sQaNGKEVK2UZA1BtL87jM8LBd81ZomaLUuA@mail.gmail.com>
 <51CC8160.2080905@optionshouse.com>
 <AE011E7AE62117479360E1E2BD341F4E08EE5B@adama.fhcrc.org>
In-Reply-To: <AE011E7AE62117479360E1E2BD341F4E08EE5B@adama.fhcrc.org>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 06/27/2013 01:45 PM, Albin, Lloyd P wrote:

> We have also run into this with our production databases. We worked
> around the issue by adding an index to each child table so that it
> scans all the child index's instead of the child table's. For us
> this made a large performance improvement.

Haha. Yeah, that's assumed. I'd never use a partition set without the 
constraint column in at least one index. The proof of concept was just 
to illustrate that the planner doesn't even get that far in ignoring 
"empty" partitions. Sure, scanning the inapplicable child tables has a 
low cost, but it's not zero. With about a dozen of them, query times 
increase from 0.130ms to 0.280ms for my test case. Not a lot in the long 
run, but in a OLTP system, it can be fairly noticeable.

-- 
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance