Re: Partitions not Working as Expected

> -----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 12:16 PM
> To: pgsql-performance(at)postgresql(dot)org
> Subject: [PERFORM] 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(at)optionshouse(dot)com
>

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