Multiple partition tables and faster queries

We've got three master tables: email, machines, and dests--each with seven
partition tables apiece. Each partitioned table is constrained to contain only
a single day's worth of data, with a CHECK contstraint
of the form

CHECK
(
***Timestamp >= 'start_constraint'
AND ***Timestamp < 'stop_constraint'
)
) INHERITS (dests);

and a rule

ON INSERT TO **** WHERE
(
****Timestamp >= 'start_constraint'
AND ****Timestamp < 'stop_constraint'
)
DO INSTEAD
INSERT INTO

When we try to do a query across all three tables, when we only know the time
constraint on a column from one of them, the access to that particular table is
very fast, but the query planner is doing sequential scans on partitions of the
other master tables.

Something like:

EXPLAIN ANALYZE
SELECT email
FROM dests JOIN (email JOIN machines ON email.guid=machines.emailRefGuid)
ON machines.qId=dests.machineRefQId
WHERE dests.destsTimestamp
BETWEEN '03-16-2007 00:00:00' AND '03-16-2007 12:00:00'
AND dests.recipient LIKE 'recipient(at)host(dot)com'
LIMIT 25;

yields
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=200000007.15..201462804.94 rows=25 width=32) (actual
time=26030.622..265488.189 rows=1 loops=1)
-> Nested Loop (cost=200000007.15..77226363225222.11 rows=1319836613
width=32) (actual time=26030.614..265488.174 rows=1 loops=1)
Join Filter: (("inner".guid)::text = ("outer".emailrefguid)::text)
-> Hash Join (cost=100000007.15..900597480.11 rows=85800 width=145)
(actual time=17778.694..58484.220 rows=9 loops=1)
Hash Cond: (("outer".qid)::text = ("inner".machinerefqid)::text)
-> Append (cost=100000000.00..900583744.97 rows=2573997
width=290) (actual time=0.032..48965.685 rows=2551618 loops=1)
-> Seq Scan on machines (cost=100000000.00..100000010.10
rows=10 width=290) (actual time=0.004..0.004 rows=0 loops=1)
-> Seq Scan on machines_2007_03_13_00 machines
(cost=100000000.00..100090684.70 rows=505670 width=65) (actual
time=0.018..3229.391 rows=505669 loops=1)
-> Seq Scan on machines_2007_03_14_00 machines
(cost=100000000.00..100071067.42 rows=714442 width=65) (actual
time=0.027..2559.969 rows=403298 loops=1)
-> Seq Scan on machines_2007_03_15_00 machines
(cost=100000000.00..100007727.51 rows=7651 width=290) (actual
time=0.022..558.653 rows=113585 loops=1)
-> Seq Scan on machines_2007_03_16_00 machines
(cost=100000000.00..100013156.26 rows=13026 width=290) (actual
time=0.023..2846.147 rows=195868 loops=1)
-> Seq Scan on machines_2007_03_09_00 machines
(cost=100000000.00..100069699.15 rows=239015 width=65) (actual
time=9.795..3677.025 rows=239015 loops=1)
-> Seq Scan on machines_2007_03_10_00 machines
(cost=100000000.00..100125947.99 rows=410299 width=65) (actual
time=8.051..7268.208 rows=410299 loops=1)
-> Seq Scan on machines_2007_03_11_00 machines
(cost=100000000.00..100123002.88 rows=403488 width=65) (actual
time=13.922..5966.851 rows=403488 loops=1)
-> Seq Scan on machines_2007_03_12_00 machines
(cost=100000000.00..100082448.96 rows=280396 width=65) (actual
time=0.410..4756.257 rows=280396 loops=1)
-> Hash (cost=7.15..7.15 rows=2 width=145) (actual
time=0.353..0.353 rows=9 loops=1)
-> Append (cost=0.00..7.15 rows=2 width=145) (actual
time=0.085..0.307 rows=9 loops=1)
-> Index Scan using dests_pkey on dests
(cost=0.00..3.13 rows=1 width=145) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: (recipient =
'recipient(at)host(dot)com'::text)
Filter: ((desttimestamp >= '2007-03-16
00:00:00'::timestamp without time zone) AND (desttimestamp <= '2007-03-16
12:00:00'::timestamp without time zone))
-> Index Scan using
dests_2007_03_16_00_time_recip_idx on dests_2007_03_16_00 dests
(cost=0.00..4.02 rows=1 width=145) (actual time=0.066..0.220 rows=9 loops=1)
Index Cond: ((recipient =
'recipient(at)host(dot)com'::text) AND (desttimestamp >= '2007-03-16
00:00:00'::timestamp without time zone) AND (desttimestamp <= '2007-03-16
12:00:00'::timestamp without time zone))
-> Append (cost=100000000.00..900046361.44 rows=1384444 width=177)
(actual time=0.030..17641.752 rows=1452762 loops=9)
-> Seq Scan on email (cost=100000000.00..100000010.90 rows=90
width=177) (actual time=0.004..0.004 rows=0 loops=9)
-> Seq Scan on email_2007_03_13_00 email
(cost=100000000.00..100009090.46 rows=279446 width=79) (actual
time=0.017..1375.027 rows=279446 loops=9)
-> Seq Scan on email_2007_03_14_00 email
(cost=100000000.00..100006521.78 rows=200078 width=79) (actual
time=0.012..981.457 rows=200078 loops=9)
-> Seq Scan on email_2007_03_15_00 email
(cost=100000000.00..100000815.32 rows=6732 width=177) (actual
time=0.010..162.921 rows=32778 loops=9)
-> Seq Scan on email_2007_03_16_00 email
(cost=100000000.00..100001303.64 rows=10764 width=177) (actual
time=0.011..257.008 rows=53126 loops=9)
-> Seq Scan on email_2007_03_09_00 email
(cost=100000000.00..100005112.27 rows=157327 width=79) (actual
time=0.011..794.660 rows=157327 loops=9)
-> Seq Scan on email_2007_03_10_00 email
(cost=100000000.00..100008897.63 rows=276663 width=79) (actual
time=2.762..1351.724 rows=276663 loops=9)
-> Seq Scan on email_2007_03_11_00 email
(cost=100000000.00..100008553.87 rows=265587 width=79) (actual
time=1.417..1319.414 rows=265587 loops=9)
-> Seq Scan on email_2007_03_12_00 email
(cost=100000000.00..100006055.57 rows=187757 width=79) (actual
time=1.575..937.279 rows=187757 loops=9)

There are indexes on all of the columns being compared in the other two
tables, so I'm wondering why the partitioned tables are being examined with a
sequential scan. Is there some other way the SQL can be formed to avoid this
and speed up the query?

--
Maya Nigrosh
Eddy Project/ISAM Group
Carnegie Mellon