explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ut32

Settings
# exclusive inclusive rows x rows loops node
1. 2.842 408,030.021 ↓ 3.5 703 1

Hash Left Join (cost=542,590.58..1,865,307.53 rows=200 width=40) (actual time=5,392.176..408,030.021 rows=703 loops=1)

  • Hash Cond: (concat('res.partner,', partner.id) = (prop_date.res_id)::text)
2.          

CTE unreconciled_aml

3. 511.032 3,564.002 ↑ 1.0 4,439,104 1

Hash Join (cost=4,327.94..437,895.30 rows=4,648,799 width=25) (actual time=9.482..3,564.002 rows=4,439,104 loops=1)

  • Hash Cond: (aml.account_id = account.id)
4. 997.324 3,052.922 ↑ 1.0 4,439,104 1

Hash Join (cost=4,313.81..425,425.80 rows=4,648,799 width=29) (actual time=9.432..3,052.922 rows=4,439,104 loops=1)

  • Hash Cond: (aml.move_id = move.id)
5. 2,046.268 2,046.268 ↑ 1.0 4,750,080 1

Seq Scan on account_move_line aml (cost=0.00..408,593.83 rows=4,768,226 width=33) (actual time=0.003..2,046.268 rows=4,750,080 loops=1)

  • Filter: ((reconciled IS NOT TRUE) AND (balance > '0'::numeric))
  • Rows Removed by Filter: 5,672,192
6. 2.964 9.330 ↑ 1.0 33,043 1

Hash (cost=3,900.71..3,900.71 rows=33,048 width=4) (actual time=9.330..9.330 rows=33,043 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 1,674kB
7. 6.366 6.366 ↑ 1.0 33,043 1

Seq Scan on account_move move (cost=0.00..3,900.71 rows=33,048 width=4) (actual time=0.003..6.366 rows=33,043 loops=1)

  • Filter: ((state)::text = 'posted'::text)
  • Rows Removed by Filter: 854
8. 0.014 0.048 ↑ 1.0 205 1

Hash (cost=11.56..11.56 rows=205 width=4) (actual time=0.048..0.048 rows=205 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
9. 0.034 0.034 ↑ 1.0 205 1

Seq Scan on account_account account (cost=0.00..11.56 rows=205 width=4) (actual time=0.003..0.034 rows=205 loops=1)

  • Filter: ((deprecated IS NOT TRUE) AND ((internal_type)::text = 'receivable'::text))
10. 113.122 408,027.173 ↓ 3.5 703 1

Nested Loop Left Join (cost=104,681.70..1,427,395.65 rows=200 width=16) (actual time=5,392.163..408,027.173 rows=703 loops=1)

11. 100.780 407,913.348 ↓ 3.5 703 1

Nested Loop Left Join (cost=104,666.27..1,422,749.18 rows=200 width=12) (actual time=5,392.158..407,913.348 rows=703 loops=1)

12. 1.471 407,807.647 ↓ 3.5 703 1

Hash Left Join (cost=104,623.53..1,412,604.38 rows=200 width=12) (actual time=5,392.136..407,807.647 rows=703 loops=1)

  • Hash Cond: ((SubPlan 3) = current_followup_level.id)
13. 1.026 4,874.690 ↓ 3.5 703 1

Hash Join (cost=104,604.76..104,850.58 rows=200 width=4) (actual time=4,873.100..4,874.690 rows=703 loops=1)

  • Hash Cond: (partner.id = unreconciled_aml.partner_id)
14. 0.599 0.599 ↑ 1.0 3,053 1

Index Only Scan using res_partner_pkey on res_partner partner (cost=0.28..238.07 rows=3,053 width=4) (actual time=0.006..0.599 rows=3,053 loops=1)

  • Heap Fetches: 5
15. 0.049 4,873.065 ↓ 3.5 703 1

Hash (cost=104,601.98..104,601.98 rows=200 width=4) (actual time=4,873.065..4,873.065 rows=703 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 33kB
16. 476.237 4,873.016 ↓ 3.5 704 1

HashAggregate (cost=104,597.98..104,599.98 rows=200 width=4) (actual time=4,872.967..4,873.016 rows=704 loops=1)

  • Group Key: unreconciled_aml.partner_id
17. 4,396.779 4,396.779 ↑ 1.0 4,439,104 1

CTE Scan on unreconciled_aml (cost=0.00..92,975.98 rows=4,648,799 width=4) (actual time=9.484..4,396.779 rows=4,439,104 loops=1)

18. 0.001 0.006 ↑ 48.8 8 1

Hash (cost=13.90..13.90 rows=390 width=8) (actual time=0.006..0.006 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.005 0.005 ↑ 48.8 8 1

Seq Scan on account_followup_followup_line current_followup_level (cost=0.00..13.90 rows=390 width=8) (actual time=0.003..0.005 rows=8 loops=1)

20.          

SubPlan (for Hash Left Join)

21. 1.406 402,931.480 ↑ 1.0 1 1,406

Limit (cost=125,142.05..125,142.06 rows=1 width=8) (actual time=286.580..286.580 rows=1 loops=1,406)

22. 721.278 402,930.074 ↑ 23,244.0 1 1,406

Sort (cost=125,142.05..125,200.16 rows=23,244 width=8) (actual time=286.579..286.579 rows=1 loops=1,406)

  • Sort Key: (COALESCE(next_ful_1.delay, ful.delay, 0)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
23. 0.000 402,208.796 ↑ 4.7 4,929 1,406

Hash Left Join (cost=37.55..125,025.83 rows=23,244 width=8) (actual time=0.127..286.066 rows=4,929 loops=1,406)

  • Hash Cond: ((SubPlan 2) = next_ful_1.id)
24. 950.453 364,416.922 ↑ 4.7 4,929 1,406

Hash Left Join (cost=18.77..104,678.44 rows=23,244 width=16) (actual time=0.119..259.187 rows=4,929 loops=1,406)

  • Hash Cond: (aml_1.followup_line_id = ful.id)
25. 363,466.466 363,466.466 ↑ 4.7 4,929 1,406

CTE Scan on unreconciled_aml aml_1 (cost=0.00..104,597.98 rows=23,244 width=12) (actual time=0.119..258.511 rows=4,929 loops=1,406)

  • Filter: (partner_id = partner.id)
  • Rows Removed by Filter: 4,434,175
26. 0.001 0.003 ↑ 48.8 8 1

Hash (cost=13.90..13.90 rows=390 width=8) (actual time=0.003..0.003 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
27. 0.002 0.002 ↑ 48.8 8 1

Seq Scan on account_followup_followup_line ful (cost=0.00..13.90 rows=390 width=8) (actual time=0.001..0.002 rows=8 loops=1)

28. 0.002 0.003 ↑ 48.8 8 1

Hash (cost=13.90..13.90 rows=390 width=8) (actual time=0.002..0.003 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
29. 0.001 0.001 ↑ 48.8 8 1

Seq Scan on account_followup_followup_line next_ful_1 (cost=0.00..13.90 rows=390 width=8) (actual time=0.001..0.001 rows=8 loops=1)

30.          

SubPlan (for Hash Left Join)

31. 0.000 38,097.024 ↑ 1.0 1 12,699,008

Limit (cost=17.04..17.04 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=12,699,008)

32. 25,398.016 38,097.024 ↑ 43.0 1 12,699,008

Sort (cost=17.04..17.15 rows=43 width=8) (actual time=0.003..0.003 rows=1 loops=12,699,008)

  • Sort Key: next_ful.delay
  • Sort Method: top-N heapsort Memory: 25kB
33. 12,699.008 12,699.008 ↑ 6.1 7 12,699,008

Seq Scan on account_followup_followup_line next_ful (cost=0.00..16.83 rows=43 width=8) (actual time=0.000..0.001 rows=7 loops=12,699,008)

  • Filter: ((delay > COALESCE(ful.delay, 0)) AND ((COALESCE(aml_1.date_maturity, aml_1.date) + delay) <= '2020-02-02'::date))
  • Rows Removed by Filter: 1
34. 0.000 4.921 ↑ 1.0 1 703

Index Only Scan using account_move_line_pkey on account_move_line in_need_of_action_aml (cost=42.74..50.72 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=703)

  • Index Cond: (id = (SubPlan 4))
  • Heap Fetches: 703
35.          

SubPlan (for Index Only Scan)

36. 0.703 98.420 ↑ 1.0 1 703

Limit (cost=0.15..42.30 rows=1 width=4) (actual time=0.140..0.140 rows=1 loops=703)

37. 1.359 97.717 ↑ 2,583.0 1 703

Nested Loop Left Join (cost=0.15..108,885.79 rows=2,583 width=4) (actual time=0.139..0.139 rows=1 loops=703)

  • Filter: ((COALESCE(ful_1.delay, 0) < current_followup_level.delay) AND ((COALESCE(aml_2.date_maturity, aml_2.date) + COALESCE(ful_1.delay, 0)) <= '2020-02-02'::date))
  • Rows Removed by Filter: 0
38. 95.608 95.608 ↑ 23,244.0 1 703

CTE Scan on unreconciled_aml aml_2 (cost=0.00..104,597.98 rows=23,244 width=16) (actual time=0.125..0.136 rows=1 loops=703)

  • Filter: (partner_id = partner.id)
  • Rows Removed by Filter: 2,173
39. 0.750 0.750 ↓ 0.0 0 750

Index Scan using account_followup_followup_line_pkey on account_followup_followup_line ful_1 (cost=0.15..0.17 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=750)

  • Index Cond: (id = aml_2.followup_line_id)
40. 0.000 0.703 ↑ 1.0 1 703

Index Only Scan using account_move_line_pkey on account_move_line exceeded_unreconciled_aml (cost=15.43..23.23 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=703)

  • Index Cond: (id = (SubPlan 5))
  • Heap Fetches: 703
41.          

SubPlan (for Index Only Scan)

42. 0.000 111.074 ↑ 1.0 1 703

Limit (cost=0.00..15.00 rows=1 width=4) (actual time=0.158..0.158 rows=1 loops=703)

43. 111.074 111.074 ↑ 7,748.0 1 703

CTE Scan on unreconciled_aml aml_3 (cost=0.00..116,219.98 rows=7,748 width=4) (actual time=0.158..0.158 rows=1 loops=703)

  • Filter: ((COALESCE(date_maturity, date) <= '2020-02-02'::date) AND (partner_id = partner.id))
  • Rows Removed by Filter: 2,173
44. 0.001 0.006 ↓ 0.0 0 1

Hash (cost=13.52..13.52 rows=4 width=24) (actual time=0.006..0.006 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
45. 0.005 0.005 ↑ 4.0 1 1

Index Scan using ir_property_name_index on ir_property prop_date (cost=0.28..13.52 rows=4 width=24) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: ((name)::text = 'payment_next_action_date'::text)