Since this is a large query, attachments for the explains / query.
Configuration:
dev_iqdb=# select version();
version
------------------------------------------------------------------------
PostgreSQL 7.4beta1 on i386-portbld-freebsd4.7, compiled by GCC
2.95.4
(1 row)
SET default_statistics_target = 1000;
ANALYZE;
set from_collapse_limit = 100;
set join_collapse_limit = 20;
The query is a segment of a report but shows the issue well enough.
Effectively, the planner has amazingly inaccurate row estimates. With
nestloop on, it estimates 1 row returned. With it off it estimates 12
rows returned. The query actually returns several hundred thousand
entries.
ANALYZE output is in the nestloopoff file. With nestloopon I actually
run out of memory prior to query completion (1GB ram to the single
process).
Any hints? I'm basically stuck. Oh, and I would like to ask for a
pgadmin feature -- visual explain :)
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Right Join (cost=62642.05..62651.25 rows=12 width=74) (actual time=682187.16..698880.37 rows=362126 loops=1)
Merge Cond: ("outer".service_id = "inner".service_id)
Filter: (("outer".account_id = "inner".account_id) OR ("outer".account_id IS NULL))
-> Index Scan using exposure_service_id_key on exposure tse (cost=0.00..8.27 rows=300 width=8) (actual time=0.47..18.46
rows=300 loops=1)
-> Sort (cost=62642.05..62642.08 rows=12 width=74) (actual time=676920.81..683084.38 rows=813179 loops=1)
Sort Key: tss.service_id
-> Hash Left Join (cost=62630.22..62641.84 rows=12 width=74) (actual time=590658.20..641520.35 rows=813179 loops=1)
Hash Cond: ("outer".service_order_detail_id = "inner".service_order_detail_id_new)
Filter: ("inner".service_order_detail_id_new IS NULL)
-> Merge Join (cost=62622.49..62626.34 rows=12 width=74) (actual time=590651.37..621488.04 rows=829688 loops=1)
Merge Cond: ("outer".account_id = "inner".parent_account_id)
Join Filter: ("inner".account_id = "outer".parent_account_id)
-> Index Scan using account_pkey on account aa (cost=0.00..1004.01 rows=19539 width=8) (actual time=0.43..0.88 rows=11 loops=1)
-> Sort (cost=62622.49..62622.89 rows=158 width=82) (actual time=577505.17..591090.69 rows=1659376 loops=1)
Sort Key: ab.parent_account_id
-> Hash Join (cost=61817.14..62616.72 rows=158 width=82) (actual time=459071.50..511843.21 rows=1659376 loops=1)
Hash Cond: ("outer".account_id = "inner".account_id)
-> Seq Scan on breakdown ab (cost=0.00..602.67 rows=39067 width=8) (actual time=4.64..493.15 rows=39067 loops=1)
-> Hash (cost=61816.94..61816.94 rows=79 width=78) (actual time=459061.75..459061.75 rows=0 loops=1)
-> Hash Join (cost=61803.77..61816.94 rows=79 width=78) (actual time=287563.65..450075.88 rows=829688 loops=1)
Hash Cond: ("outer".service_order_detail_type_id = "inner".service_order_detail_type_id)
-> Hash Join (cost=61802.74..61814.33 rows=158 width=82) (actual time=287562.87..419673.93 rows=1121988 loops=1)
Hash Cond: ("outer".service_semantic_type_id = "inner".service_semantic_type_id)
-> Hash Join (cost=61801.71..61810.93 rows=158 width=74) (actual time=287562.31..383292.28 rows=1121988 loops=1)
Hash Cond: ("outer".service_semantic_type_id = "inner".service_semantic_type_id)
-> Merge Join (cost=61800.67..61807.52 rows=158 width=66) (actual
time=287561.86..349895.76 rows=1121988 loops=1)
Merge Cond: ("outer".service_id = "inner".service_id)
-> Sort (cost=61660.77..61661.17 rows=158 width=66) (actual
time=287532.78..298241.29 rows=1121988 loops=1)
Sort Key: tsb.service_id
-> Hash Join (cost=60582.21..61655.00 rows=158 width=66) (actual time=166995.46..234437.41 rows=1121988 loops=1)
Hash Cond: ("outer".service_order_id = "inner".service_order_id)
-> Seq Scan on service_order bso (cost=0.00..908.14 rows=32614 width=16) (actual time=5.57..1434.72 rows=32614 loops=1)
-> Hash (cost=60581.82..60581.82 rows=158 width=54) (actual time=166989.39..166989.39 rows=0 loops=1)
-> Hash Join (cost=1230.79..60581.82 rows=158 width=54) (actual time=1262.35..151200.29 rows=1121988 loops=1)
Hash Cond: ("outer".account_id = "inner".account_id)
-> Hash Join (cost=1226.78..52863.43 rows=1542558 width=50) (actual time=1261.63..100418.30 rows=1573190 loops=1)
Hash Cond: ("outer".subservice_id = "inner".service_id)
-> Hash Join (cost=1128.58..29626.86 rows=1542558 width=42) (actual time=1075.57..43565.86 rows=1573190 loops=1)
Hash Cond: ("outer".service_id = "inner".service_id)
-> Seq Scan on service_order_detail bsod (cost=0.00..1973.39 rows=47739 width=30) (actual time=0.14..1600.43 rows=47739 loops=1)
-> Hash (cost=1061.26..1061.26 rows=26926 width=12) (actual time=1075.06..1075.06 rows=0 loops=1)
-> Seq Scan on breakdown tsb (cost=0.00..1061.26 rows=26926 width=12) (actual time=93.94..643.40 rows=26926 loops=1)
-> Hash (cost=95.96..95.96 rows=896 width=12) (actual time=185.74..185.74 rows=0 loops=1)
-> Seq Scan on service tss (cost=0.00..95.96 rows=896 width=12) (actual time=168.07..179.79 rows=896 loops=1)
-> Hash (cost=4.01..4.01 rows=1 width=4) (actual time=0.33..0.33 rows=0 loops=1)
-> Index Scan using single_null_parent_account_hack on account ap (cost=0.00..4.01 rows=1 width=4) (actual time=0.26..0.28 rows=1 loops=1)
Filter: (parent_account_id IS NULL)
-> Sort (cost=139.90..142.14 rows=896 width=8) (actual time=28.11..6577.04 rows=1122010 loops=1)
Sort Key: ss.service_id
-> Seq Scan on service ss (cost=0.00..95.96 rows=896 width=8) (actual time=8.06..20.39 rows=896 loops=1)
-> Hash (cost=1.03..1.03 rows=3 width=16) (actual time=0.13..0.13
rows=0 loops=1)
-> Seq Scan on semantic_type tsst (cost=0.00..1.03 rows=3 width=16) (actual time=0.03..0.07 rows=3 loops=1)
-> Hash (cost=1.03..1.03 rows=3 width=16) (actual time=0.26..0.26 rows=0 loops=1)
-> Seq Scan on semantic_type sst (cost=0.00..1.03 rows=3 width=16) (actual time=0.16..0.19 rows=3 loops=1)
-> Hash (cost=1.02..1.02 rows=1 width=4) (actual time=0.27..0.27 rows=0 loops=1)
-> Seq Scan on service_order_detail_type (cost=0.00..1.02 rows=1 width=4) (actual time=0.18..0.20 rows=1 loops=1)
Filter: ((service_order_detail_type_code)::text = 'create'::text)
-> Hash (cost=6.78..6.78 rows=378 width=4) (actual time=6.18..6.18 rows=0 loops=1)
-> Seq Scan on service_order_service_change bsosc (cost=0.00..6.78 rows=378 width=4) (actual time=0.17..3.66 rows=378 loops=1)
Total runtime: 700880.59 msec
(60 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=2488.35..2503.53 rows=1 width=74)
Filter: (("inner".account_id = "outer".account_id) OR ("inner".account_id IS NULL))
-> Nested Loop Left Join (cost=2488.35..2500.91 rows=1 width=74)
Join Filter: ("outer".service_order_detail_id = "inner".service_order_detail_id_new)
Filter: ("inner".service_order_detail_id_new IS NULL)
-> Hash Join (cost=2488.35..2489.40 rows=1 width=74)
Hash Cond: ("outer".service_semantic_type_id = "inner".service_semantic_type_id)
-> Seq Scan on semantic_type sst (cost=0.00..1.03 rows=3 width=16)
-> Hash (cost=2488.34..2488.34 rows=1 width=66)
-> Merge Join (cost=2430.84..2488.34 rows=1 width=66)
Merge Cond: ("outer".account_id = "inner".account_id)
Join Filter: (("outer".service_semantic_type_id = "inner".service_semantic_type_id) AND ("outer".subservice_id = "inner".service_id))
-> Nested Loop (cost=2290.94..27839.41 rows=485 width=78)
Join Filter: ("inner".service_id = "outer".service_id)
-> Nested Loop (cost=2290.94..6871.89 rows=15 width=74)
-> Nested Loop (cost=2290.94..6866.59 rows=5 width=58)
Join Filter: ("outer".service_order_detail_type_id = "inner".service_order_detail_type_id)
-> Merge Join (cost=2290.94..6856.21 rows=10 width=62)
Merge Cond: ("outer".account_id = "inner".parent_account_id)
Join Filter: ("inner".account_id = "outer".parent_account_id)
-> Nested Loop (cost=0.00..1223416.10 rows=190913 width=58)
-> Nested Loop (cost=0.00..718440.38 rows=95478 width=54)
-> Nested Loop (cost=0.00..335160.18 rows=95478 width=46)
-> Nested Loop (cost=0.00..3472.57 rows=65228 width=20)
-> Index Scan using account_pkey on account ap (cost=0.00..1004.01 rows=2 width=4)
Filter: (parent_account_id IS NULL)
-> Seq Scan on service_order bso (cost=0.00..908.14 rows=32614 width=16)
-> Index Scan using sod_service_order_id_idx on service_order_detail bsod (cost=0.00..5.06 rows=2 width=30)
Index Cond: ("outer".service_order_id = bsod.service_order_id)
-> Index Scan using service_pkey on service ss (cost=0.00..4.00 rows=1 width=8)
Index Cond: ("outer".service_id = ss.service_id)
-> Index Scan using breakdown_pkey on breakdown ab (cost=0.00..5.26 rows=2 width=8)
Index Cond: (ab.account_id = "outer".account_id)
-> Sort (cost=2290.94..2339.79 rows=19539 width=8)
Sort Key: aa.parent_account_id
-> Seq Scan on account aa (cost=0.00..898.39 rows=19539 width=8)
-> Seq Scan on service_order_detail_type (cost=0.00..1.02 rows=1 width=4)
Filter: ((service_order_detail_type_code)::text = 'create'::text)
-> Seq Scan on semantic_type tsst (cost=0.00..1.03 rows=3 width=16)
-> Seq Scan on breakdown tsb (cost=0.00..1061.26 rows=26926 width=12)
-> Sort (cost=139.90..142.14 rows=896 width=12)
Sort Key: tss.account_id
-> Seq Scan on service tss (cost=0.00..95.96 rows=896 width=12)
-> Seq Scan on service_order_service_change bsosc (cost=0.00..6.78 rows=378 width=4)
-> Index Scan using exposure_service_id_key on exposure tse (cost=0.00..2.60 rows=1 width=8)
Index Cond: ("outer".service_id = tse.service_id)
(46 rows)
SELECT bso.service_order_id
, service_order_detail_id
, bsod.service_id AS to_retail_service_id
, bsod.quantity AS to_retail_service_quantity
, sst.service_semantic_type_code AS to_retail_service_semantic_type_code
, tss.service_id AS to_contract_service_id
, tsb.quantity * bsod.quantity AS to_contract_service_quantity
, tsst.service_semantic_type_code AS to_contract_service_semantic_type_code
, bso.account_id AS client_account_id
, bsod.account_instance_id AS client_account_instance_id
, aa.account_id AS inquent_plc_account_id
, bso.status_timestamp AS timestamp
FROM billing.service_order AS bso
JOIN billing.service_order_detail AS bsod USING (service_order_id)
JOIN billing.service_order_detail_type USING (service_order_detail_type_id)
JOIN service.service AS ss USING (service_id)
JOIN service.semantic_type AS sst USING (service_semantic_type_id)
JOIN account.breakdown AS ab ON (ab.account_id = bso.account_id)
JOIN account.account AS aa ON (aa.account_id = ab.parent_account_id)
JOIN account.account AS ap
ON (ap.account_id = aa.parent_account_id AND ap.parent_account_id IS NULL)
JOIN service.breakdown AS tsb ON (ss.service_id = tsb.service_id)
JOIN service.service AS tss
ON (tsb.subservice_id = tss.service_id AND tss.account_id = ap.account_id)
JOIN service.semantic_type AS tsst
ON (tsst.service_semantic_type_id = tss.service_semantic_type_id)
LEFT JOIN billing.service_order_service_change AS bsosc
ON (bsod.service_order_detail_id = bsosc.service_order_detail_id_new)
LEFT JOIN service.exposure AS tse ON (tss.service_id = tse.service_id)
WHERE service_order_detail_type_code = 'create'
AND bsosc.service_order_detail_id_new IS NULL
AND (tse.account_id = aa.account_id OR tse.account_id IS NULL);
signature.asc
Description: This is a digitally signed message part
