Thread: Partitioning: how to exclude unrelated partitions?

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

Sean Z. wrote:
> 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);
>
> [snip]
>
> Do I miss anything?
>
> Best,
> Sean
>

I believe you can only partition on literal values.  You'll probably
need to include a derived 'day' column in your table that you can
populate in the rule.  Your query will then need to include the literal
day value in the where clause, rather than the event_time.

Check out the caveats section for partitioning here (bottom of page, 5.9.5):

http://www.postgresql.org/docs/8.2/interactive/ddl-partitioning.html

It's not terribly obvious at first reading, as the focus is more on
querying than designing the table.  Maybe that would be worth expanding
on a little in the docs?

Regards,
Paul