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
