The following bug has been logged online: Bug reference: 3235 Logged by: Christian Gonzalez Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.1 Operating system: Red Hat 4.1.1-30 Description: Partitioning has problem with timestamp and timestamptz data types Details:
When you use timestamp and timestamptz data type for partitioning implementation, your postgresql partitioning implementation doesen't work fine when you make a SELECT using this columns type. Using Example in PostgreSQL Partitioning page http://www.postgresql.org/docs/8.2/interactive/ddl-partitioning.html --Create Master Table CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int ); --Create Child Tables CREATE TABLE measurement_y2004m02 ( CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2004m03 ( CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2005m11 ( CHECK ( logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2005m12 ( CHECK ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2006m01 ( CHECK ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' ) ) INHERITS (measurement); -- Add two new column (timestamp and timestamptz) ALTER TABLE measurement ADD COLUMN logdatet timestamp; ALTER TABLE measurement ADD COLUMN logdatett timestamptz; -- Test SELECT in column type DATE SET constraint_exclusion = on; EXPLAIN SELECT * FROM measurement WHERE logdate = '2006-01-01' "Result (cost=0.00..50.75 rows=12 width=32)" " -> Append (cost=0.00..50.75 rows=12 width=32)" " -> Seq Scan on measurement (cost=0.00..25.38 rows=6 width=32)" " Filter: (logdate = '2006-01-01'::date)" " -> Seq Scan on measurement_y2006m01 measurement (cost=0.00..25.38 rows=6 width=32)" " Filter: (logdate = '2006-01-01'::date)" -- Test SELECT in column type timestamp SET constraint_exclusion = on; EXPLAIN SELECT * FROM measurement WHERE logdatet = '2006-01-01' "Result (cost=0.00..152.25 rows=36 width=32)" " -> Append (cost=0.00..152.25 rows=36 width=32)" " -> Seq Scan on measurement (cost=0.00..25.38 rows=6 width=32)" " Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with time zone)" " -> Seq Scan on measurement_y2004m02 measurement (cost=0.00..25.38 rows=6 width=32)" " Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with time zone)" " -> Seq Scan on measurement_y2004m03 measurement (cost=0.00..25.38 rows=6 width=32)" " Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with time zone)" " -> Seq Scan on measurement_y2005m11 measurement (cost=0.00..25.38 rows=6 width=32)" " Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with time zone)" " -> Seq Scan on measurement_y2005m12 measurement (cost=0.00..25.38 rows=6 width=32)" " Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with time zone)" " -> Seq Scan on measurement_y2006m01 measurement (cost=0.00..25.38 rows=6 width=32)" " Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with time zone)" -- Test SELECT in column type timestamp whit cast to DATE SET constraint_exclusion = on; EXPLAIN SELECT * FROM measurement WHERE logdatet::date = '2006-01-01'::date "Result (cost=0.00..170.70 rows=36 width=32)" " -> Append (cost=0.00..170.70 rows=36 width=32)" " -> Seq Scan on measurement (cost=0.00..28.45 rows=6 width=32)" " Filter: ((logdatet)::date = '2006-01-01'::date)" " -> Seq Scan on measurement_y2004m02 measurement (cost=0.00..28.45 rows=6 width=32)" " Filter: ((logdatet)::date = '2006-01-01'::date)" " -> Seq Scan on measurement_y2004m03 measurement (cost=0.00..28.45 rows=6 width=32)" " Filter: ((logdatet)::date = '2006-01-01'::date)" " -> Seq Scan on measurement_y2005m11 measurement (cost=0.00..28.45 rows=6 width=32)" " Filter: ((logdatet)::date = '2006-01-01'::date)" " -> Seq Scan on measurement_y2005m12 measurement (cost=0.00..28.45 rows=6 width=32)" " Filter: ((logdatet)::date = '2006-01-01'::date)" " -> Seq Scan on measurement_y2006m01 measurement (cost=0.00..28.45 rows=6 width=32)" " Filter: ((logdatet)::date = '2006-01-01'::date)" SET constraint_exclusion = on; EXPLAIN SELECT * FROM measurement WHERE CAST(logdatet AS DATE) = CAST('2006-01-01' AS DATE) "Result (cost=0.00..170.70 rows=36 width=32)" " -> Append (cost=0.00..170.70 rows=36 width=32)" " -> Seq Scan on measurement (cost=0.00..28.45 rows=6 width=32)" " Filter: ((logdatet)::date = '2006-01-01'::date)" " -> Seq Scan on measurement_y2004m02 measurement (cost=0.00..28.45 rows=6 width=32)" " Filter: ((logdatet)::date = '2006-01-01'::date)" " -> Seq Scan on measurement_y2004m03 measurement (cost=0.00..28.45 rows=6 width=32)" " Filter: ((logdatet)::date = '2006-01-01'::date)" " -> Seq Scan on measurement_y2005m11 measurement (cost=0.00..28.45 rows=6 width=32)" " Filter: ((logdatet)::date = '2006-01-01'::date)" " -> Seq Scan on measurement_y2005m12 measurement (cost=0.00..28.45 rows=6 width=32)" " Filter: ((logdatet)::date = '2006-01-01'::date)" " -> Seq Scan on measurement_y2006m01 measurement (cost=0.00..28.45 rows=6 width=32)" " Filter: ((logdatet)::date = '2006-01-01'::date)" We have similar results for timestamptz data type Why dosen't work? ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
