explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tJMI

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 26.501 7,958.948 ↑ 1.5 31,608 1

Append (cost=403,232.56..765,519.88 rows=48,372 width=381) (actual time=2,968.757..7,958.948 rows=31,608 loops=1)

2. 35.111 5,837.910 ↑ 1.1 27,550 1

Subquery Scan on "*SELECT* 1" (cost=403,232.56..420,986.07 rows=30,742 width=258) (actual time=2,968.756..5,837.910 rows=27,550 loops=1)

3. 2,818.098 5,802.799 ↑ 1.1 27,550 1

GroupAggregate (cost=403,232.56..420,601.79 rows=30,742 width=645) (actual time=2,968.753..5,802.799 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. 69.918 2,984.701 ↓ 1.0 31,778 1

Sort (cost=403,232.56..403,309.42 rows=30,742 width=802) (actual time=2,967.556..2,984.701 rows=31,778 loops=1)

  • Sort Key: at.id, lf.name, lf.details, lf.default_ledger_filter_id, pcm.cam_pool_type_id, ao.name, ac.id
  • Sort Method: quicksort Memory: 32624kB
5. 51.473 2,914.783 ↓ 1.0 31,778 1

Nested Loop Left Join (cost=46.49..400,941.07 rows=30,742 width=802) (actual time=0.887..2,914.783 rows=31,778 loops=1)

  • Join Filter: ((pcm.cid = at.cid) AND (pcm.id = lcp.property_cam_pool_id))
6. 51.188 2,863.310 ↓ 1.0 31,778 1

Nested Loop Left Join (cost=42.33..399,857.38 rows=30,742 width=802) (actual time=0.880..2,863.310 rows=31,778 loops=1)

  • Join Filter: ((lcp.cid = at.cid) AND (lcs.lease_cam_pool_id = lcp.id))
7. 51.464 2,812.122 ↓ 1.0 31,778 1

Nested Loop (cost=38.15..398,236.18 rows=30,742 width=802) (actual time=0.871..2,812.122 rows=31,778 loops=1)

  • Join Filter: (at.ar_origin_id = ao.id)
  • Rows Removed by Join Filter: 37091
8. 32.113 2,728.880 ↓ 1.0 31,778 1

Nested Loop Left Join (cost=38.15..394,453.83 rows=30,742 width=794) (actual time=0.861..2,728.880 rows=31,778 loops=1)

9. 44.182 2,614.117 ↑ 1.1 27,550 1

Nested Loop Left Join (cost=37.72..258,580.77 rows=30,742 width=789) (actual time=0.841..2,614.117 rows=27,550 loops=1)

  • Join Filter: ((lcs.cid = at.cid) AND (lcs.id = at.ar_origin_reference_id))
10. 49.843 2,569.935 ↑ 1.1 27,550 1

Hash Join (cost=33.55..256,956.11 rows=30,742 width=789) (actual time=0.833..2,569.935 rows=27,550 loops=1)

  • Hash Cond: (at.property_id = lp.property_id)
11. 67.937 2,519.676 ↓ 2.6 81,186 1

Nested Loop (cost=14.40..256,514.26 rows=30,742 width=789) (actual time=0.407..2,519.676 rows=81,186 loops=1)

  • Join Filter: (ac.ledger_filter_id = lf.id)
12. 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 = 2603)
  • Rows Removed by Filter: 1
13. 76.566 2,451.734 ↓ 1.3 81,186 1

Hash Join (cost=14.40..255,744.67 rows=61,485 width=382) (actual time=0.401..2,451.734 rows=81,186 loops=1)

  • Hash Cond: (at.ar_code_id = ac.id)
14. 2,374.796 2,374.796 ↓ 1.3 81,186 1

Seq Scan on ar_transactions at (cost=0.00..255,566.20 rows=61,485 width=54) (actual time=0.018..2,374.796 rows=81,186 loops=1)

  • Filter: ((NOT is_temporary) AND (transaction_amount >= '0'::numeric) AND (fully_allocated_post_month > '2020-01-01'::date) AND (post_month <= '2020-01-01'::date) AND (cid = 2603))
  • Rows Removed by Filter: 5299848
15. 0.153 0.372 ↑ 1.0 256 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
16. 0.219 0.219 ↑ 1.0 256 1

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

  • Filter: (cid = 2603)
17. 0.212 0.416 ↑ 1.0 451 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
18. 0.204 0.204 ↑ 1.0 451 1

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

19. 0.000 0.000 ↓ 0.0 0 27,550

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

20. 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 = 2603)
21. 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 = 2603)
22. 82.650 82.650 ↓ 0.0 0 27,550

Index Scan using idx_ar_allocations_charge_ar_transaction_id on ar_allocations aa (cost=0.43..4.41 rows=1 width=17) (actual time=0.002..0.003 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 = 2603) AND (cid = at.cid) AND (lease_id = at.lease_id))
  • Rows Removed by Filter: 0
23. 31.770 31.778 ↑ 4.5 2 31,778

Materialize (cost=0.00..1.14 rows=9 width=12) (actual time=0.000..0.001 rows=2 loops=31,778)

24. 0.008 0.008 ↑ 1.0 9 1

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

25. 0.000 0.000 ↓ 0.0 0 31,778

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

26. 0.003 0.005 ↓ 0.0 0 1

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

  • Recheck Cond: (cid = 2603)
27. 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 = 2603)
28. 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)

29. 0.000 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 = 2603)
30. 0.002 0.002 ↓ 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.002 rows=0 loops=1)

  • Index Cond: (cid = 2603)
31. 4.561 2,094.537 ↑ 4.3 4,058 1

Subquery Scan on "*SELECT* 2" (cost=334,264.34..344,533.81 rows=17,630 width=258) (actual time=1,283.499..2,094.537 rows=4,058 loops=1)

32. 799.752 2,089.976 ↑ 4.3 4,058 1

GroupAggregate (cost=334,264.34..344,357.51 rows=17,630 width=673) (actual time=1,283.497..2,089.976 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
33. 64.381 1,290.224 ↑ 1.1 15,926 1

Sort (cost=334,264.34..334,308.41 rows=17,630 width=802) (actual time=1,283.151..1,290.224 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: 16558kB
34. 13.956 1,225.843 ↑ 1.1 15,926 1

Hash Left Join (cost=36,434.83..333,020.92 rows=17,630 width=802) (actual time=136.900..1,225.843 rows=15,926 loops=1)

  • Hash Cond: ((at_1.cid = pcm_1.cid) AND (lcp_1.property_cam_pool_id = pcm_1.id))
35. 13.823 1,211.882 ↑ 1.1 15,926 1

Hash Left Join (cost=36,425.30..332,917.75 rows=17,630 width=802) (actual time=136.885..1,211.882 rows=15,926 loops=1)

  • Hash Cond: ((at_1.cid = lcp_1.cid) AND (lcs_1.lease_cam_pool_id = lcp_1.id))
36. 13.812 1,198.054 ↑ 1.1 15,926 1

Hash Left Join (cost=36,413.97..332,812.78 rows=17,630 width=802) (actual time=136.869..1,198.054 rows=15,926 loops=1)

  • Hash Cond: ((at_1.cid = lcs_1.cid) AND (at_1.ar_origin_reference_id = lcs_1.id))
37. 15.929 1,184.235 ↑ 1.1 15,926 1

Nested Loop Left Join (cost=36,402.65..332,708.74 rows=17,630 width=802) (actual time=136.847..1,184.235 rows=15,926 loops=1)

38. 3.847 1,135.802 ↑ 4.3 4,063 1

Hash Join (cost=36,402.21..224,208.80 rows=17,630 width=797) (actual time=136.821..1,135.802 rows=4,063 loops=1)

  • Hash Cond: (at_1.ar_origin_id = ao_1.id)
39. 6.056 1,131.934 ↑ 4.3 4,063 1

Hash Join (cost=36,401.01..224,139.52 rows=17,630 width=789) (actual time=136.793..1,131.934 rows=4,063 loops=1)

  • Hash Cond: (at_1.property_id = lp_1.property_id)
40. 8.607 1,125.459 ↑ 2.0 8,907 1

Hash Join (cost=36,381.86..223,877.96 rows=17,630 width=789) (actual time=136.369..1,125.459 rows=8,907 loops=1)

  • Hash Cond: (at_1.ar_code_id = ac_1.id)
41. 1,003.691 1,116.172 ↑ 4.0 8,907 1

Bitmap Heap Scan on ar_transactions at_1 (cost=36,365.93..223,553.51 rows=35,259 width=54) (actual time=135.682..1,116.172 rows=8,907 loops=1)

  • Recheck Cond: ((cid = 2603) AND (transaction_amount < '0'::numeric))
  • Filter: ((NOT is_temporary) AND (fully_allocated_post_month > '2020-01-01'::date) AND (post_month <= '2020-01-01'::date))
  • Rows Removed by Filter: 1955036
  • Heap Blocks: exact=123023
42. 112.481 112.481 ↓ 1.0 1,972,473 1

Bitmap Index Scan on idx_ar_transactions_cache_charge_master_offset (cost=0.00..36,357.11 rows=1,960,579 width=0) (actual time=112.481..112.481 rows=1,972,473 loops=1)

  • Index Cond: (cid = 2603)
43. 0.220 0.680 ↓ 2.0 256 1

Hash (cost=14.33..14.33 rows=128 width=739) (actual time=0.679..0.680 rows=256 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 138kB
44. 0.303 0.460 ↓ 2.0 256 1

Hash Join (cost=1.04..14.33 rows=128 width=739) (actual time=0.039..0.460 rows=256 loops=1)

  • Hash Cond: (ac_1.ledger_filter_id = lf_1.id)
45. 0.146 0.146 ↑ 1.0 256 1

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

  • Filter: (cid = 2603)
46. 0.005 0.011 ↑ 1.0 1 1

Hash (cost=1.02..1.02 rows=1 width=419) (actual time=0.010..0.011 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
47. 0.006 0.006 ↑ 1.0 1 1

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

  • Filter: (cid = 2603)
  • Rows Removed by Filter: 1
48. 0.204 0.419 ↑ 1.0 451 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
49. 0.215 0.215 ↑ 1.0 451 1

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

50. 0.007 0.021 ↑ 1.0 9 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
51. 0.014 0.014 ↑ 1.0 9 1

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

52. 32.504 32.504 ↓ 4.0 4 4,063

Index Scan using idx_ar_allocations_credit_ar_transaction_id on ar_allocations aa_1 (cost=0.43..6.14 rows=1 width=17) (actual time=0.004..0.008 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 = 2603) AND (cid = at_1.cid) AND (lease_id = at_1.lease_id))
  • Rows Removed by Filter: 0
53. 0.001 0.007 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
54. 0.002 0.006 ↓ 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.006 rows=0 loops=1)

  • Recheck Cond: (cid = 2603)
55. 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.004..0.004 rows=0 loops=1)

  • Index Cond: (cid = 2603)
56. 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: 1024 Batches: 1 Memory Usage: 8kB
57. 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 = 2603)
58. 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.002..0.003 rows=0 loops=1)

  • Index Cond: (cid = 2603)
59. 0.001 0.005 ↓ 0.0 0 1

Hash (cost=9.50..9.50 rows=2 width=12) (actual time=0.005..0.005 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
60. 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.004..0.004 rows=0 loops=1)

  • Recheck Cond: (cid = 2603)
61. 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 = 2603)
Planning time : 14.179 ms