Re: [GENERAL] Partitioning: how to exclude unrelated partitions?

2007-10-30 Thread paul rivers 

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






---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Partitioning: how to exclude unrelated partitions?

2007-10-29 Thread Sean Z. 
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 Scanned, 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