explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OtF : Optimization for: plan #tJMI

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 26.173 4,211.502 ↑ 1.6 31,608 1

Append (cost=210,615.68..421,613.53 rows=50,244 width=381) (actual time=598.546..4,211.502 rows=31,608 loops=1)

2. 34.642 3,212.729 ↑ 1.2 27,550 1

Subquery Scan on "*SELECT* 1" (cost=210,615.68..228,018.62 rows=31,932 width=258) (actual time=598.545..3,212.729 rows=27,550 loops=1)

3. 2,672.492 3,178.087 ↑ 1.2 27,550 1

HashAggregate (cost=210,615.68..227,619.47 rows=31,932 width=645) (actual time=598.542..3,178.087 rows=27,550 loops=1)

  • Group Key: at.cid, at.id, lf.name, lf.details, lf.default_ledger_filter_id, pcm.cam_pool_type_id, ao.name, ac.id, ac.cid
  • Filter: ((COALESCE(at.transaction_amount, '0'::numeric) + COALESCE(sum(aa.allocation_amount), '0'::numeric)) <> '0'::numeric)
4. 52.189 505.595 ↑ 1.0 31,778 1

Nested Loop Left Join (cost=62.43..209,657.72 rows=31,932 width=802) (actual time=0.961..505.595 rows=31,778 loops=1)

  • Join Filter: ((pcm.cid = at.cid) AND (pcm.id = lcp.property_cam_pool_id))
5. 52.984 453.406 ↑ 1.0 31,778 1

Nested Loop Left Join (cost=58.27..208,532.46 rows=31,932 width=802) (actual time=0.956..453.406 rows=31,778 loops=1)

6. 24.817 345.322 ↑ 1.2 27,550 1

Hash Left Join (cost=57.83..69,522.37 rows=31,932 width=797) (actual time=0.946..345.322 rows=27,550 loops=1)

  • Hash Cond: ((at.cid = lcp.cid) AND (lcs.lease_cam_pool_id = lcp.id))
7. 25.049 320.502 ↑ 1.2 27,550 1

Hash Left Join (cost=46.51..69,341.44 rows=31,932 width=797) (actual time=0.937..320.502 rows=27,550 loops=1)

  • Hash Cond: ((at.cid = lcs.cid) AND (at.ar_origin_reference_id = lcs.id))
8. 25.750 295.448 ↑ 1.2 27,550 1

Hash Join (cost=35.18..69,162.19 rows=31,932 width=797) (actual time=0.926..295.448 rows=27,550 loops=1)

  • Hash Cond: (at.ar_origin_id = ao.id)
9. 47.231 269.681 ↑ 1.2 27,550 1

Hash Join (cost=33.98..69,037.70 rows=31,932 width=789) (actual time=0.903..269.681 rows=27,550 loops=1)

  • Hash Cond: (at.property_id = lp.property_id)
10. 66.832 222.046 ↓ 2.5 81,186 1

Nested Loop (cost=14.83..68,579.48 rows=31,932 width=789) (actual time=0.494..222.046 rows=81,186 loops=1)

  • Join Filter: (ac.ledger_filter_id = lf.id)
11. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on ledger_filters lf (cost=0.00..1.02 rows=1 width=419) (actual time=0.003..0.005 rows=1 loops=1)

  • Filter: (cid = 2,603)
  • Rows Removed by Filter: 1
12. 78.201 155.209 ↓ 1.3 81,186 1

Hash Join (cost=14.83..67,780.15 rows=63,865 width=382) (actual time=0.488..155.209 rows=81,186 loops=1)

  • Hash Cond: (at.ar_code_id = ac.id)
13. 76.611 76.611 ↓ 1.3 81,186 1

Index Scan using idx_ar_transactions_fully_allocated_post_month on ar_transactions at (cost=0.43..67,595.33 rows=63,865 width=54) (actual time=0.085..76.611 rows=81,186 loops=1)

  • Index Cond: (fully_allocated_post_month > '2020-01-01'::date)
  • Filter: ((NOT is_temporary) AND (transaction_amount >= '0'::numeric) AND (post_month <= '2020-01-01'::date) AND (cid = 2,603))
  • Rows Removed by Filter: 10,229
14. 0.141 0.397 ↑ 1.0 256 1

Hash (cost=11.20..11.20 rows=256 width=332) (actual time=0.397..0.397 rows=256 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 35kB
15. 0.256 0.256 ↑ 1.0 256 1

Seq Scan on ar_codes ac (cost=0.00..11.20 rows=256 width=332) (actual time=0.005..0.256 rows=256 loops=1)

  • Filter: (cid = 2,603)
16. 0.203 0.404 ↑ 1.0 451 1

Hash (cost=13.51..13.51 rows=451 width=4) (actual time=0.404..0.404 rows=451 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
17. 0.201 0.201 ↑ 1.0 451 1

Seq Scan on lp (cost=0.00..13.51 rows=451 width=4) (actual time=0.003..0.201 rows=451 loops=1)

18. 0.008 0.017 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=12) (actual time=0.016..0.017 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.009 0.009 ↑ 1.0 9 1

Seq Scan on ar_origins ao (cost=0.00..1.09 rows=9 width=12) (actual time=0.004..0.009 rows=9 loops=1)

20. 0.001 0.005 ↓ 0.0 0 1

Hash (cost=11.28..11.28 rows=3 width=12) (actual time=0.005..0.005 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
21. 0.001 0.004 ↓ 0.0 0 1

Bitmap Heap Scan on lease_cam_shares lcs (cost=4.17..11.28 rows=3 width=12) (actual time=0.004..0.004 rows=0 loops=1)

  • Recheck Cond: (cid = 2,603)
22. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on idx_lease_cam_shares_ap_code_id (cost=0.00..4.17 rows=3 width=0) (actual time=0.002..0.003 rows=0 loops=1)

  • Index Cond: (cid = 2,603)
23. 0.000 0.003 ↓ 0.0 0 1

Hash (cost=11.28..11.28 rows=3 width=12) (actual time=0.003..0.003 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
24. 0.001 0.003 ↓ 0.0 0 1

Bitmap Heap Scan on lease_cam_pools lcp (cost=4.17..11.28 rows=3 width=12) (actual time=0.002..0.003 rows=0 loops=1)

  • Recheck Cond: (cid = 2,603)
25. 0.002 0.002 ↓ 0.0 0 1

Bitmap Index Scan on uk_lease_cam_pools_cid_property_cam_pool_id_lease_id (cost=0.00..4.17 rows=3 width=0) (actual time=0.001..0.002 rows=0 loops=1)

  • Index Cond: (cid = 2,603)
26. 55.100 55.100 ↓ 0.0 0 27,550

Index Scan using idx_ar_allocations_charge_ar_transaction_id on ar_allocations aa (cost=0.43..4.34 rows=1 width=17) (actual time=0.002..0.002 rows=0 loops=27,550)

  • Index Cond: (at.id = charge_ar_transaction_id)
  • Filter: ((post_month < '2020-01-02 00:00:00'::timestamp without time zone) AND (cid = 2,603) AND (cid = at.cid) AND (lease_id = at.lease_id))
  • Rows Removed by Filter: 0
27. 0.000 0.000 ↓ 0.0 0 31,778

Materialize (cost=4.16..9.51 rows=2 width=12) (actual time=0.000..0.000 rows=0 loops=31,778)

28. 0.001 0.002 ↓ 0.0 0 1

Bitmap Heap Scan on property_cam_pools pcm (cost=4.16..9.50 rows=2 width=12) (actual time=0.002..0.002 rows=0 loops=1)

  • Recheck Cond: (cid = 2,603)
29. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on pk_property_cam_pools (cost=0.00..4.16 rows=2 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (cid = 2,603)
30. 4.523 972.600 ↑ 4.5 4,058 1

Subquery Scan on "*SELECT* 2" (cost=182,928.17..193,594.91 rows=18,312 width=258) (actual time=247.970..972.600 rows=4,058 loops=1)

31. 713.459 968.077 ↑ 4.5 4,058 1

GroupAggregate (cost=182,928.17..193,411.79 rows=18,312 width=673) (actual time=247.968..968.077 rows=4,058 loops=1)

  • Group Key: at_1.cid, at_1.id, lf_1.name, lf_1.details, lf_1.default_ledger_filter_id, pcm_1.cam_pool_type_id, ao_1.name, ac_1.id, ac_1.cid
  • Filter: ((COALESCE(at_1.transaction_amount, '0'::numeric) - COALESCE(sum(aa_1.allocation_amount), '0'::numeric)) <> '0'::numeric)
  • Rows Removed by Filter: 5
32. 65.441 254.618 ↑ 1.1 15,926 1

Sort (cost=182,928.17..182,973.95 rows=18,312 width=802) (actual time=247.663..254.618 rows=15,926 loops=1)

  • Sort Key: at_1.id, lf_1.name, lf_1.details, lf_1.default_ledger_filter_id, pcm_1.cam_pool_type_id, ao_1.name, ac_1.id
  • Sort Method: quicksort Memory: 16,558kB
33. 25.201 189.177 ↑ 1.1 15,926 1

Nested Loop Left Join (cost=48.12..181,631.63 rows=18,312 width=802) (actual time=0.951..189.177 rows=15,926 loops=1)

  • Join Filter: ((pcm_1.cid = at_1.cid) AND (pcm_1.id = lcp_1.property_cam_pool_id))
34. 25.126 163.976 ↑ 1.1 15,926 1

Nested Loop Left Join (cost=43.96..180,982.27 rows=18,312 width=802) (actual time=0.941..163.976 rows=15,926 loops=1)

  • Join Filter: ((lcp_1.cid = at_1.cid) AND (lcs_1.lease_cam_pool_id = lcp_1.id))
35. 25.207 138.850 ↑ 1.1 15,926 1

Nested Loop Left Join (cost=39.79..180,012.02 rows=18,312 width=802) (actual time=0.932..138.850 rows=15,926 loops=1)

  • Join Filter: ((lcs_1.cid = at_1.cid) AND (lcs_1.id = at_1.ar_origin_reference_id))
36. 16.066 113.643 ↑ 1.1 15,926 1

Nested Loop Left Join (cost=35.62..179,039.69 rows=18,312 width=802) (actual time=0.921..113.643 rows=15,926 loops=1)

37. 4.006 69.136 ↑ 4.5 4,063 1

Hash Join (cost=35.18..68,509.13 rows=18,312 width=797) (actual time=0.901..69.136 rows=4,063 loops=1)

  • Hash Cond: (at_1.ar_origin_id = ao_1.id)
38. 6.003 65.108 ↑ 4.5 4,063 1

Hash Join (cost=33.98..68,437.22 rows=18,312 width=789) (actual time=0.871..65.108 rows=4,063 loops=1)

  • Hash Cond: (at_1.property_id = lp_1.property_id)
39. 7.406 58.686 ↑ 2.1 8,907 1

Nested Loop (cost=14.83..68,166.28 rows=18,312 width=789) (actual time=0.447..58.686 rows=8,907 loops=1)

  • Join Filter: (ac_1.ledger_filter_id = lf_1.id)
40. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on ledger_filters lf_1 (cost=0.00..1.02 rows=1 width=419) (actual time=0.006..0.008 rows=1 loops=1)

  • Filter: (cid = 2,603)
  • Rows Removed by Filter: 1
41. 8.781 51.272 ↑ 4.1 8,907 1

Hash Join (cost=14.83..67,707.46 rows=36,624 width=382) (actual time=0.437..51.272 rows=8,907 loops=1)

  • Hash Cond: (at_1.ar_code_id = ac_1.id)
42. 42.126 42.126 ↑ 4.1 8,907 1

Index Scan using idx_ar_transactions_fully_allocated_post_month on ar_transactions at_1 (cost=0.43..67,595.33 rows=36,624 width=54) (actual time=0.066..42.126 rows=8,907 loops=1)

  • Index Cond: (fully_allocated_post_month > '2020-01-01'::date)
  • Filter: ((NOT is_temporary) AND (transaction_amount < '0'::numeric) AND (post_month <= '2020-01-01'::date) AND (cid = 2,603))
  • Rows Removed by Filter: 82,508
43. 0.154 0.365 ↑ 1.0 256 1

Hash (cost=11.20..11.20 rows=256 width=332) (actual time=0.365..0.365 rows=256 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 35kB
44. 0.211 0.211 ↑ 1.0 256 1

Seq Scan on ar_codes ac_1 (cost=0.00..11.20 rows=256 width=332) (actual time=0.008..0.211 rows=256 loops=1)

  • Filter: (cid = 2,603)
45. 0.205 0.419 ↑ 1.0 451 1

Hash (cost=13.51..13.51 rows=451 width=4) (actual time=0.419..0.419 rows=451 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
46. 0.214 0.214 ↑ 1.0 451 1

Seq Scan on lp lp_1 (cost=0.00..13.51 rows=451 width=4) (actual time=0.006..0.214 rows=451 loops=1)

47. 0.007 0.022 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=12) (actual time=0.021..0.022 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
48. 0.015 0.015 ↑ 1.0 9 1

Seq Scan on ar_origins ao_1 (cost=0.00..1.09 rows=9 width=12) (actual time=0.010..0.015 rows=9 loops=1)

49. 28.441 28.441 ↓ 4.0 4 4,063

Index Scan using idx_ar_allocations_credit_ar_transaction_id on ar_allocations aa_1 (cost=0.43..6.03 rows=1 width=17) (actual time=0.003..0.007 rows=4 loops=4,063)

  • Index Cond: (at_1.id = credit_ar_transaction_id)
  • Filter: ((post_month < '2020-01-02 00:00:00'::timestamp without time zone) AND (cid = 2,603) AND (cid = at_1.cid) AND (lease_id = at_1.lease_id))
  • Rows Removed by Filter: 0
50. 0.000 0.000 ↓ 0.0 0 15,926

Materialize (cost=4.17..11.30 rows=3 width=12) (actual time=0.000..0.000 rows=0 loops=15,926)

51. 0.001 0.005 ↓ 0.0 0 1

Bitmap Heap Scan on lease_cam_shares lcs_1 (cost=4.17..11.28 rows=3 width=12) (actual time=0.005..0.005 rows=0 loops=1)

  • Recheck Cond: (cid = 2,603)
52. 0.004 0.004 ↓ 0.0 0 1

Bitmap Index Scan on idx_lease_cam_shares_ap_code_id (cost=0.00..4.17 rows=3 width=0) (actual time=0.003..0.004 rows=0 loops=1)

  • Index Cond: (cid = 2,603)
53. 0.000 0.000 ↓ 0.0 0 15,926

Materialize (cost=4.17..11.30 rows=3 width=12) (actual time=0.000..0.000 rows=0 loops=15,926)

54. 0.001 0.004 ↓ 0.0 0 1

Bitmap Heap Scan on lease_cam_pools lcp_1 (cost=4.17..11.28 rows=3 width=12) (actual time=0.004..0.004 rows=0 loops=1)

  • Recheck Cond: (cid = 2,603)
55. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on uk_lease_cam_pools_cid_property_cam_pool_id_lease_id (cost=0.00..4.17 rows=3 width=0) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (cid = 2,603)
56. 0.000 0.000 ↓ 0.0 0 15,926

Materialize (cost=4.16..9.51 rows=2 width=12) (actual time=0.000..0.000 rows=0 loops=15,926)

57. 0.001 0.004 ↓ 0.0 0 1

Bitmap Heap Scan on property_cam_pools pcm_1 (cost=4.16..9.50 rows=2 width=12) (actual time=0.003..0.004 rows=0 loops=1)

  • Recheck Cond: (cid = 2,603)
58. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on pk_property_cam_pools (cost=0.00..4.16 rows=2 width=0) (actual time=0.002..0.003 rows=0 loops=1)

  • Index Cond: (cid = 2,603)