explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oQPd

Settings
# exclusive inclusive rows x rows loops node
1. 1.471 6,363.628 ↑ 443.0 1 1

Subquery Scan on credit_control_analysis (cost=1,620,348.36..1,622,339.72 rows=443 width=72) (actual time=6,345.424..6,363.628 rows=1 loops=1)

  • Filter: (credit_control_analysis.id = 11068)
  • Rows Removed by Filter: 7620
2. 20.793 6,362.157 ↑ 11.6 7,621 1

Unique (cost=1,620,348.36..1,621,233.41 rows=88,505 width=76) (actual time=6,318.079..6,362.157 rows=7,621 loops=1)

3. 246.380 6,341.364 ↓ 1.0 91,850 1

Sort (cost=1,620,348.36..1,620,569.62 rows=88,505 width=76) (actual time=6,318.077..6,341.364 rows=91,850 loops=1)

  • Sort Key: partner.id, ccl.policy_id, ccl.currency_id, ccpl.level DESC, ccl.id
  • Sort Method: external sort Disk: 5040kB
4. 164.188 6,094.984 ↓ 1.0 91,850 1

Hash Left Join (cost=11,565.25..1,610,771.94 rows=88,505 width=76) (actual time=143.138..6,094.984 rows=91,850 loops=1)

  • Hash Cond: (ccl.policy_level_id = ccpl.id)
5. 126.941 970.872 ↓ 1.0 91,850 1

Hash Join (cost=11,548.50..46,325.00 rows=88,505 width=36) (actual time=142.949..970.872 rows=91,850 loops=1)

  • Hash Cond: (ccl.move_line_id = aml.id)
6. 86.358 703.279 ↑ 1.0 91,851 1

Merge Left Join (cost=106.58..31,491.59 rows=91,851 width=40) (actual time=1.364..703.279 rows=91,851 loops=1)

  • Merge Cond: (ccl.commercial_partner_id = partner.id)
7. 100.207 100.207 ↑ 1.0 91,851 1

Index Scan using credit_control_line_commercial_partner_id_index on credit_control_line ccl (cost=0.42..5,878.28 rows=91,851 width=24) (actual time=0.008..100.207 rows=91,851 loops=1)

8. 516.714 516.714 ↓ 1.1 272,445 1

Index Scan using res_partner_pkey on res_partner partner (cost=0.42..30,319.23 rows=237,673 width=20) (actual time=0.006..516.714 rows=272,445 loops=1)

9. 51.676 140.652 ↓ 1.0 185,994 1

Hash (cost=8,392.78..8,392.78 rows=185,851 width=4) (actual time=140.652..140.652 rows=185,994 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 4296kB
10. 88.976 88.976 ↓ 1.0 185,994 1

Seq Scan on account_move_line aml (cost=0.00..8,392.78 rows=185,851 width=4) (actual time=0.013..88.976 rows=185,994 loops=1)

  • Filter: (NOT reconciled)
  • Rows Removed by Filter: 6884
11. 0.014 0.024 ↑ 30.0 10 1

Hash (cost=13.00..13.00 rows=300 width=8) (actual time=0.024..0.024 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.010 0.010 ↑ 30.0 10 1

Seq Scan on credit_control_policy_level ccpl (cost=0.00..13.00 rows=300 width=8) (actual time=0.008..0.010 rows=10 loops=1)

13.          

SubPlan (for Hash Left Join)

14. 275.550 4,959.900 ↑ 1.0 1 91,850

Aggregate (cost=17.65..17.66 rows=1 width=32) (actual time=0.054..0.054 rows=1 loops=91,850)

15. 392.880 4,684.350 ↓ 6.0 6 91,850

Nested Loop (cost=15.41..17.65 rows=1 width=3) (actual time=0.028..0.051 rows=6 loops=91,850)

16. 551.100 2,112.550 ↓ 6.0 6 91,850

HashAggregate (cost=14.99..15.00 rows=1 width=4) (actual time=0.022..0.023 rows=6 loops=91,850)

  • Group Key: ccl2.move_line_id
17. 1,561.450 1,561.450 ↓ 13.0 13 91,850

Index Scan using credit_control_line_commercial_partner_id_index on credit_control_line ccl2 (cost=0.42..14.99 rows=1 width=4) (actual time=0.004..0.017 rows=13 loops=91,850)

  • Index Cond: (commercial_partner_id = partner.id)
  • Filter: ((((ccl.currency_id IS NULL) AND (currency_id IS NULL)) OR (currency_id = ccl.currency_id)) AND (policy_id = ccl.policy_id))
18. 2,178.920 2,178.920 ↑ 1.0 1 544,730

Index Scan using account_move_line_pkey on account_move_line aml_1 (cost=0.42..2.64 rows=1 width=7) (actual time=0.003..0.004 rows=1 loops=544,730)

  • Index Cond: (id = ccl2.move_line_id)
  • Filter: (NOT reconciled)
  • Rows Removed by Filter: 0
Planning time : 4.284 ms
Execution time : 6,365.233 ms