Why is plan (and performance) different on partitioned table? - Mailing list pgsql-performance

Hi,

I have recently implemented table partitioning in our postgres 8.1 db. Upon analyzing query performance, I have realized that, even when only a single one of the "partitions" has to be scanned, the plan is drastically different, and performs much worse, when I query against the master table (uses merge join), vs. a direct query against the partition directly (uses a hash join).  The majority of our queries only access a single partition.

Any insight into why this happens and what can be done to improve performance would be greatly appreciated.

br_1min is my partitioned table:

explain analyze
SELECT *
FROM br_1min br1 JOIN br_mods mod on br1.modules_id = mod.id 
WHERE ((end_time >= '2006-05-01 17:12:18-07' AND end_time < '2006-05-01 17:13:18-07'))
  AND mod.downloads_id IN (153226,153714,153730,153728,153727,153724,153713,153725,153739,153722) ;

----------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=73.99..223.43 rows=1 width=109) (actual time=2925.629..3082.188 rows=45 loops=1)
   Merge Cond: ("outer".id = "inner".modules_id)
   ->  Index Scan using br_mods_id_pkey on br_mods mod  (cost=0.00..40861.18 rows=282 width=77) (actual time=2922.223..3078.335 rows=45 loops=1)
         Filter: ((downloads_id = 153226) OR (downloads_id = 153714) OR (downloads_id = 153730) OR (downloads_id = 153728) OR (downloads_id = 153727) OR (downloads_id = 153724) OR (downloads_id = 153713) OR (downloads_id = 153725) OR (downloads_id = 153739) OR (downloads_id = 153722))
   ->  Sort  (cost=73.99..76.26 rows=906 width=32) (actual time=3.334..3.508 rows=348 loops=1)
         Sort Key: br1.modules_id
         ->  Append  (cost=0.00..29.49 rows=906 width=32) (actual time=0.133..2.169 rows=910 loops=1)
               ->  Index Scan using br_1min_end_idx on br_1min br1  (cost=0.00..2.02 rows=1 width=32) (actual time=0.029..0.029 rows=0 loops=1)
                     Index Cond: ((end_time >= '2006-05-01 17:12:18-07'::timestamp with time zone) AND (end_time < '2006-05-01 17:13:18-07'::timestamp with time zone))
               ->  Index Scan using br_1min_20557_end_idx on br_1min_20557 br1  (cost=0.00..27.48 rows=905 width=32) (actual time=0.101..1.384 rows=910 loops=1)
                     Index Cond: ((end_time >= '2006-05-01 17:12:18-07'::timestamp with time zone) AND (end_time < '2006-05-01 17:13:18-07'::timestamp with time zone))
 Total runtime: 3082.450 ms
(12 rows)



Now, If I query directly against br_1min_20557, my partition, I get:

explain analyze
SELECT *
FROM br_1min_20557 br1 JOIN br_mods mod on br1.modules_id = mod.id 
WHERE ((end_time >= '2006-05-01 17:12:18-07' AND end_time < '2006-05-01 17:13:18-07'))
  AND mod.downloads_id IN (153226,153714,153730,153728,153727,153724,153713,153725,153739,153722) ;


----------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=764.74..796.94 rows=1 width=109) (actual time=2.488..2.865 rows=45 loops=1)
   Hash Cond: ("outer".modules_id = "inner".id)
   ->  Index Scan using br_1min_20557_end_idx on br_1min_20557 br1  (cost=0.00..27.62 rows=914 width=32) (actual time=0.084..1.886 rows=910 loops=1)
         Index Cond: ((end_time >= '2006-05-01 17:12:18-07'::timestamp with time zone) AND (end_time < '2006-05-01 17:13:18-07'::timestamp with time zone))
   ->  Hash  (cost=764.03..764.03 rows=282 width=77) (actual time=0.284..0.284 rows=45 loops=1)
         ->  Bitmap Heap Scan on br_mods mod  (cost=20.99..764.03 rows=282 width=77) (actual time=0.154..0.245 rows=45 loops=1)
               Recheck Cond: ((downloads_id = 153226) OR (downloads_id = 153714) OR (downloads_id = 153730) OR (downloads_id = 153728) OR (downloads_id = 153727) OR (downloads_id = 153724) OR (downloads_id = 153713) OR (downloads_id = 153725) OR (downloads_id = 153739) OR (downloads_id = 153722))
               ->  BitmapOr  (cost=20.99..20.99 rows=282 width=0) (actual time=0.144..0.144 rows=0 loops=1)
                     ->  Bitmap Index Scan on br_mods_downloads_id_idx  (cost=0.00..2.10 rows=28 width=0) (actual time=0.031..0.031 rows=14 loops=1)
                           Index Cond: (downloads_id = 153226)
                     ->  Bitmap Index Scan on br_mods_downloads_id_idx  (cost=0.00..2.10 rows=28 width=0) (actual time=0.011..0.011 rows=2 loops=1)
                           Index Cond: (downloads_id = 153714)
                     ->  Bitmap Index Scan on br_mods_downloads_id_idx  (cost=0.00..2.10 rows=28 width=0) (actual time=0.007..0.007 rows=2 loops=1)
                           Index Cond: (downloads_id = 153730)
                     ->  Bitmap Index Scan on br_mods_downloads_id_idx  (cost=0.00..2.10 rows=28 width=0) (actual time=0.007..0.007 rows=2 loops=1)
                           Index Cond: (downloads_id = 153728)
                     ->  Bitmap Index Scan on br_mods_downloads_id_idx  (cost=0.00..2.10 rows=28 width=0) (actual time=0.008..0.008 rows=2 loops=1)
                           Index Cond: (downloads_id = 153727)
                     ->  Bitmap Index Scan on br_mods_downloads_id_idx  (cost=0.00..2.10 rows=28 width=0) (actual time=0.008..0.008 rows=2 loops=1)
                           Index Cond: (downloads_id = 153724)
                     ->  Bitmap Index Scan on br_mods_downloads_id_idx  (cost=0.00..2.10 rows=28 width=0) (actual time=0.008..0.008 rows=2 loops=1)
                           Index Cond: (downloads_id = 153713)
                     ->  Bitmap Index Scan on br_mods_downloads_id_idx  (cost=0.00..2.10 rows=28 width=0) (actual time=0.008..0.008 rows=2 loops=1)
                           Index Cond: (downloads_id = 153725)
                     ->  Bitmap Index Scan on br_mods_downloads_id_idx  (cost=0.00..2.10 rows=28 width=0) (actual time=0.041..0.041 rows=16 loops=1)
                           Index Cond: (downloads_id = 153739)
                     ->  Bitmap Index Scan on br_mods_downloads_id_idx  (cost=0.00..2.10 rows=28 width=0) (actual time=0.010..0.010 rows=1 loops=1)
                           Index Cond: (downloads_id = 153722)
 Total runtime: 3.017 ms
(29 rows)

The difference is night-and-day.  Any suggestions?

Thanks alot,

Mark