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)
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
