Query Optimization with Partitioned Tables

Поиск
Список
Период
Сортировка
От Kong Mansatiansin
Тема Query Optimization with Partitioned Tables
Дата
Msg-id 1283370592.1084.36.camel@annapurna.mongonet.net
обсуждение исходный текст
Ответы Re: Query Optimization with Partitioned Tables  (Willy-Bas Loos <willybas@gmail.com>)
Список pgsql-admin
I recently shrunk over 90% of two huge tables on our production database
by partitioning 3-month recent data into 3 separate partitions each and
got rid of the rest of the data.  Despite the smaller sizes, our queries
now run slower.  The following complex query/view with 8 joined tables,
especially, takes over 3 times longer than it used to be.

Questions:
 * What is the best approach to hint the query planner to come up with
an optimal plan?
 * With join_collapse_limit=1, I was able to rearrange the join order in
the way I think is optimal, but still unable to make the plan use my
only predicate as a filter for selective rows in the first place.  How
to make the query planner use the last filter of the below plan as the
first action, which is what I believe to be the optimal plan?

Note: I just learned from this change that with the 3x2 additional
tables from the 2 new partitioned table the query of 8 becomes one with
14 joined tables and crosses the default geqo_threshold of 12.  This
causes the query to crawl over an hour, instead of a few seconds.  When
I set geqo_threshold to 14, it finishes in 30 seconds, which is still
over 3 times longer than it used to.

Release: PostgreSQL Server 8.2.5
Partitioned Tables:
  xjob_message
  xjob_message_nv

Query:
SET geqo_threshold = 16;
SET
SET join_collapse_limit = 1;
SET
EXPLAIN ANALYZE
 SELECT xtx.xid_seqid, xtx.xid, xtx.xid_date
  , tpl.name AS triplet, att.name AS attribute
  , mnv.value, xjb.xjob_seqid, xjb.xid_ref
  , xjb.start_time, xjb.end_time
  , xjb.acct_seqid, xjb.stacker
  , xjb.dest_phone_ref, xjb.src_phone_ref
  , src.phone_number AS src_phone, dst.phone_number AS dst_phone
 FROM xids xtx JOIN
  (xjob  xjb JOIN
    ((xjob_message msg JOIN
       xjob_message_nv mnv
        ON (msg.xjob_message_seqid = mnv.xjob_message_ref)
     ) JOIN lvalue_lu tpl ON (msg.triplet_ref = tpl.name_id)
      JOIN lvalue_lu att ON (mnv.name_id = att.name_id)
    ) ON (xjb.xjob_seqid = msg.xjob_ref)
    JOIN phonenumbers src ON (xjb.src_phone_ref = src.phone_seqid)
   JOIN phonenumbers dst ON (xjb.dest_phone_ref = dst.phone_seqid)
  ) ON (xtx.xid_seqid = xjb.xid_ref)
 WHERE xid = '2010.08.24.06.59.46-3232238101-010';

QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=315359340.99..511370148619.48 rows=191260 width=750)
(actual time=2577633.855..2757154.912 rows=36 loops=1)
   Hash Cond: (xjb.xid_ref = xtx.xid_seqid)
   ->  Hash Join  (cost=315359329.18..504126347706.90 rows=1931679730182
width=696) (actual time=1880998.113..2726039.838 rows=26788491 loops=1)
         Hash Cond: (xjb.dest_phone_ref = dst.phone_seqid)
         ->  Hash Join  (cost=315324005.11..467907317441.91
rows=1931679730182 width=681) (actual time=1878605.989..2650974.533
rows=26788491 loops=1)
               Hash Cond: (xjb.src_phone_ref = src.phone_seqid)
               ->  Hash Join  (cost=315288681.04..431688287176.93
rows=1931679730182 width=666) (actual time=1876430.631..2574354.447
rows=26889080 loops=1)
                     Hash Cond: (msg.xjob_ref = xjb.xjob_seqid)
                     ->  Hash Join  (cost=308498699.12..104201231123.46
rows=1931679730182 width=560) (actual time=695399.794..1180175.001
rows=26889080 loops=1)
                           Hash Cond: (mnv.name_id = att.name_id)
                           ->  Hash Join
(cost=308498681.31..76156965143.36 rows=2327324976123 width=550) (actual
time=695399.139..1112463.270 rows=26889080 loops=1)
                                 Hash Cond: (msg.triplet_ref =
tpl.name_id)
                                 ->  Merge Join
(cost=308498663.49..42368692881.83 rows=2804005995329 width=540) (actual
time=695398.629..1045250.141 rows=26889080 loops=1)
                                       Merge Cond:
(msg.xjob_message_seqid = mnv.xjob_message_ref)
                                       ->  Sort
(cost=112341184.27..112393328.48 rows=20857681 width=24) (actual
time=155624.502..231644.023 rows=20993357 loops=1)
                                             Sort Key:
msg.xjob_message_seqid
                                             ->  Append
(cost=100000000.00..109306471.13 rows=20857681 width=24) (actual
time=25.252..94521.657 rows=20993357 loops=1)
                                                   ->  Seq Scan on
xjob_message msg  (cost=100000000.00..100000022.30 rows=1230 width=24)
(actual time=0.003..0.003 rows=0 loops=1)
                                                   ->  Index Scan using
xjob_message_p201007_pkey on xjob_message_p201007 msg
(cost=0.00..327643.28 rows=9551599 width=24) (actual
time=25.244..20871.816 rows=9551599 loops=1)
                                                   ->  Index Scan using
xjob_message_p201008_pkey on xjob_message_p201008 msg
(cost=0.00..2135648.37 rows=9549512 width=24) (actual
time=19.564..22177.935 rows=9686418 loops=1)
                                                   ->  Index Scan using
ix_xjob_message_p201006_2 on xjob_message_p201006 msg
(cost=0.00..6843157.18 rows=1755340 width=24) (actual
time=19.432..5899.709 rows=1755340 loops=1)
                                       ->  Sort
(cost=196157479.22..196224696.81 rows=26887035 width=532) (actual
time=539774.100..721084.551 rows=26889080 loops=1)
                                             Sort Key:
mnv.xjob_message_ref
                                             ->  Append
(cost=100000000.00..186314731.34 rows=26887035 width=532) (actual
time=48.524..418540.359 rows=26889080 loops=1)
                                                   ->  Seq Scan on
xjob_message_nv mnv  (cost=100000000.00..100000011.40 rows=140
width=532) (actual time=0.005..0.005 rows=0 loops=1)
                                                   ->  Index Scan using
ix_xjob_message_nv_p201006_1 on xjob_message_nv_p201006 mnv
(cost=0.00..12091979.65 rows=3100747 width=51) (actual
time=48.515..105184.555 rows=3100747 loops=1)
                                                   ->  Index Scan using
ix_xjob_message_nv_p201007_1 on xjob_message_nv_p201007 mnv
(cost=0.00..46018341.15 rows=11793590 width=99) (actual
time=54.513..122517.400 rows=11793590 loops=1)
                                                   ->  Index Scan using
ix_xjob_message_nv_p201008_1 on xjob_message_nv_p201008 mnv
(cost=0.00..28204399.14 rows=11992558 width=101) (actual
time=9.237..132125.953 rows=11994743 loops=1)
                                 ->  Hash  (cost=15.74..15.74 rows=166
width=26) (actual time=0.479..0.479 rows=166 loops=1)
                                       ->  Index Scan using
uq_lvalue_lu_2 on lvalue_lu tpl  (cost=0.00..15.74 rows=166 width=26)
(actual time=0.008..0.224 rows=166 loops=1)
                           ->  Hash  (cost=15.74..15.74 rows=166
width=26) (actual time=0.597..0.597 rows=166 loops=1)
                                 ->  Index Scan using uq_lvalue_lu_2 on
lvalue_lu att  (cost=0.00..15.74 rows=166 width=26) (actual
time=0.093..0.332 rows=166 loops=1)
                     ->  Hash  (cost=6477316.38..6477316.38
rows=10068203 width=114) (actual time=1181023.780..1181023.780
rows=10074305 loops=1)
                           ->  Index Scan using xjob_pkey on xjob xjb
(cost=0.00..6477316.38 rows=10068203 width=114) (actual
time=39.068..1158862.644 rows=10074305 loops=1)
               ->  Hash  (cost=26512.52..26512.52 rows=704924 width=23)
(actual time=2173.384..2173.384 rows=705106 loops=1)
                     ->  Index Scan using ix_phonenumbers_2 on
phonenumbers src  (cost=0.00..26512.52 rows=704924 width=23) (actual
time=0.021..1132.041 rows=705106 loops=1)
         ->  Hash  (cost=26512.52..26512.52 rows=704924 width=23)
(actual time=2390.358..2390.358 rows=705106 loops=1)
               ->  Index Scan using ix_phonenumbers_2 on phonenumbers
dst  (cost=0.00..26512.52 rows=704924 width=23) (actual
time=0.096..1351.941 rows=705106 loops=1)
   ->  Hash  (cost=11.79..11.79 rows=1 width=54) (actual
time=31.754..31.754 rows=1 loops=1)
         ->  Index Scan using ix_xids_1 on xids xtx  (cost=0.00..11.79
rows=1 width=54) (actual time=31.730..31.734 rows=1 loops=1)
               Index Cond: ((xid)::text =
'2010.08.24.06.59.46-3232238101-010'::text)
 Total runtime: 2761143.201 ms
(42 rows)




В списке pgsql-admin по дате отправления:

Предыдущее
От: Willy-Bas Loos
Дата:
Сообщение: Re: Confused by 'timing' results
Следующее
От: "Josi Perez (3T Systems)"
Дата:
Сообщение: pgAgent on Windows