Multiple partition tables and faster queries - Mailing list pgsql-sql

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@host.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_00machines 
 
(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@host.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@host.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.90rows=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