Thread: Question on Type of Query Which Will Take Advantage On Table Partition

Hello all,

By referring to tutorial on
http://www.if-not-true-then-false.com/2009/11/howto-create-postgresql-table-partitioning-part-1/

I have several doubt, on the type of query, which will take advantage on table partition.


CREATE TABLE impressions_by_day (
    advertiser_id INTEGER NOT NULL,
    day DATE NOT NULL DEFAULT CURRENT_DATE,
    impressions INTEGER NOT NULL,
        PRIMARY KEY (advertiser_id, day)
);

CREATE TABLE impressions_by_day_y2009m1ms2 (
    PRIMARY KEY (advertiser_id, day),
    CHECK ( day >= DATE '2009-01-01' AND day < DATE '2009-03-01' )
) INHERITS (impressions_by_day);


SET constraint_exclusion = ON;


// This query doesn't take advantage of table partition.
// It need to scan through *every* child table.
SELECT * FROM impressions_by_day


// Will this takes advatage of table partition.
// Is PostgreSQL smart enough to know, it only need to look for
// impressions_by_day_y2009m1ms2 ???

SELECT * FROM impressions_by_day WHERE day  = DATE '2009-02-02'

// I am sure this will take advantage of table partition, isn't it ???

SELECT * FROM impressions_by_day WHERE day >= DATE '2009-01-01' AND day < DATE '2009-03-01'


Thanks and Regards
Yan Cheng CHEOK