Partitioning: how to exclude unrelated partitions? - Mailing list pgsql-general

Hi,

I partitioned a table "events" into 31 tables, based on "day" of event_time.

I did 3 steps to setup partition, after creating partition tables:

1. Add the constraint to the 31 partition tables like:

ALTER TABLE events_day_1
  ADD CONSTRAINT events_day_1_event_time_check CHECK (date_part('day'::text, event_time) = 1::double precision);

2. Add partition rules  like:

CREATE OR REPLACE RULE events_insert_day_1 AS
    ON INSERT TO events
   WHERE date_part('day'::text, new.event_time) = 1::double precision
DO INSTEAD
INSERT INTO events_day_1 (id, event_number, event_source, event_type, event_time, event_message)
  VALUES (new.id, new.event_number, new.event_source, new.event_type, new.event_time, new.event_message);

3. Set constraint_exclusion = on

But when I run the following query:

explain analyze select *
        from events
        where event_time > '10/25/2007 20:00:00'
        order by event_time
        limit 100
        offset 3000;

I got the following query plan:

"Limit  (cost=12897.77..12898.02 rows=100 width=144) (actual time=365.976..366.143 rows=100 loops=1)"
"  ->  Sort  (cost=12890.27..13031.08 rows=56323 width=144) (actual time=362.225..364.929 rows=3100 loops=1)"
"        Sort Key: public.events.event_time"
"        ->  Result  (cost=0.00..4207.48 rows=56323 width=144) (actual time=0.099..156.586 rows=50091 loops=1)"
"              ->  Append  (cost=0.00..4207.48 rows=56323 width=144) (actual time=0.095..93.748 rows=50091 loops=1)"
"                    ->  Seq Scan on events  (cost=0.00..17.25 rows=193 width=106) (actual time=0.003..0.003 rows=0 loops=1)"
"                          Filter: (event_time > '2007-10-25 20:00:00-04'::timestamp with time zone)"
"                    ->  Seq Scan on events_day_1 events  (cost=0.00..17.25 rows=193 width=106) (actual time=0.001..0.001 rows=0 loops=1)"
"                          Filter: (event_time > '2007-10-25 20:00:00-04'::timestamp with time zone)"

<... repeated lines ignored here>

"                    ->  Index Scan using events_day_25_idx1 on events_day_25 events  (cost=0.00..3672.73 rows=50340 width=144) (actual time=0.053..53.129 rows=49984 loops=1)"
"                          Index Cond: (event_time > '2007-10-25 20:00:00-04'::timestamp with time zone)"

<... repeated lines ignored here>

"                    ->  Seq Scan on events_day_31 events  (cost=0.00..17.25 rows=193 width=106) (actual time=0.001..0.001 rows=0 loops=1)"
"                          Filter: (event_time > '2007-10-25 20:00:00-04'::timestamp with time zone)"



Every partition table is "Seq Scan"ned, I think unrelated tables are not excluded in the query. Only table events_day_25 should be included in scan I believe.

Do I miss anything?

Best,
Sean

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com