Re: Partitions not Working as Expected

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.

CREATE INDEX part_test_1_idx ON part_test_1
USING btree (part_col);

CREATE INDEX part_test_2_idx ON part_test_2
USING btree (part_col);

Lloyd Albin
Statistical Center for HIV/AIDS Research and Prevention (SCHARP)
Vaccine and Infectious Disease Division (VIDD)
Fred Hutchinson Cancer Research Center (FHCRC)

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Shaun Thomas
Sent: Thursday, June 27, 2013 11:16 AM
To: Dave Johansen
Cc: bricklen; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Partitions not Working as Expected

On 06/27/2013 12:42 PM, Dave Johansen wrote:

> Or what about something like DATE_TRUNC("DAY", now())? Or would that
> run into the same optimization/planner problems as CURRENT_DATE?

Same issue. This seems to work, though I'm not entirely sure of the
implications:

UPDATE pg_proc
SET provolatile = 'i'
WHERE proname = 'date_in';

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

______________________________________________

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

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