> -----Original Message----- > From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql- > performance-owner@xxxxxxxxxxxxxx] On Behalf Of Shaun Thomas > Sent: Thursday, June 27, 2013 12:16 PM > To: pgsql-performance@xxxxxxxxxxxxxx > Subject: Partitions not Working as Expected > > Hey guys, > > I suspect I'll get an answer equivalent to "the planner treats that like a > variable," but I really hope not because it renders partitions essentially > useless to us. This is as recent as 9.1.9 and constraint exclusion is enabled. > > What I have is this test case: > > CREATE TABLE part_test ( > fake INT, > part_col TIMESTAMP WITHOUT TIME ZONE > ); > > CREATE TABLE part_test_1 ( > CHECK (part_col >= '2013-05-01' AND > part_col < '2013-06-01') > ) INHERITS (part_test); > > CREATE TABLE part_test_2 ( > CHECK (part_col >= '2013-04-01' AND > part_col < '2013-05-01') > ) INHERITS (part_test); > > And this query performs a sequence scan across all partitions: > > EXPLAIN ANALYZE > SELECT * FROM part_test > WHERE part_col > CURRENT_DATE; > > The CURRENT_DATE value is clearly more recent than any of the partitions, > yet it checks them anyway. The only way to get it to properly constrain > partitions is to use a static value: > > EXPLAIN ANALYZE > SELECT * FROM part_test > WHERE part_col > '2013-06-27'; > > But developers never do this. Nor should they. I feel like an idiot even asking > this, because it seems so wrong, and I can't seem to come up with a > workaround other than, "Ok devs, hard code dates into all of your queries > from now on." > > -- > Shaun Thomas > OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 > 312-676-8870 > sthomas@xxxxxxxxxxxxxxxx > Doesn't have to be hardcoded. If executed as dynamic sql, it will be re-planned properly, e.g.: lQueryString := 'SELECT MAX(cycle_date_time) AS MaxDT FROM gp_cycle_' || partition_extension::varchar || ' WHERE cell_id = ' || i_n_Cell_id::varchar || ' AND part_type_id = ' || i_n_PartType_id::varchar || ' AND cycle_date_time <= TIMESTAMP ' || quote_literal(cast(i_t_EndDate AS VARCHAR)); IF (lQueryString IS NOT NULL) THEN EXECUTE lQueryString INTO lEndDate; Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
