explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ac7w

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2.567 92,373.986 ↑ 7.9 1,210 1

Nested Loop Left Join (cost=124,602.63..636,796.65 rows=9,574 width=257) (actual time=2,947.846..92,373.986 rows=1,210 loops=1)

2. 649.625 92,370.209 ↑ 7.9 1,210 1

Hash Join (cost=124,602.21..629,531.56 rows=9,574 width=229) (actual time=2,947.824..92,370.209 rows=1,210 loops=1)

  • Hash Cond: (COALESCE(art.gl_dimension_id, gd.gl_dimension_id) = gld.id)
3. 2,830.639 91,719.580 ↓ 141.9 1,555,517 1

Nested Loop Left Join (cost=124,437.85..629,338.16 rows=10,965 width=221) (actual time=2,537.044..91,719.580 rows=1,555,517 loops=1)

  • Join Filter: (gh.gl_transaction_type_id = 7)
4. 21,775.008 85,777.907 ↓ 141.9 1,555,517 1

Hash Join (cost=124,437.42..601,609.94 rows=10,965 width=170) (actual time=2,537.016..85,777.907 rows=1,555,517 loops=1)

  • Hash Cond: (gd.gl_header_id = gh.id)
5. 37,181.205 62,746.709 ↓ 129.1 41,686,687 1

Nested Loop (cost=0.81..476,325.72 rows=322,901 width=103) (actual time=5.134..62,746.709 rows=41,686,687 loops=1)

6. 5.252 5.252 ↓ 87.0 87 1

Function Scan on load_properties load_prop (cost=0.25..1.00 rows=1 width=4) (actual time=5.111..5.252 rows=87 loops=1)

  • Filter: ((is_disabled = 0) AND (is_test = 0))
7. 25,560.252 25,560.252 ↓ 1.5 479,157 87

Index Scan using idx_gl_details_cid_property_id on gl_details gd (cost=0.56..473,095.71 rows=322,901 width=103) (actual time=0.014..293.796 rows=479,157 loops=87)

  • Index Cond: ((cid = 13531) AND (property_id = load_prop.property_id))
8. 462.175 1,256.190 ↑ 1.1 707,629 1

Hash (cost=115,140.50..115,140.50 rows=743,688 width=71) (actual time=1,256.189..1,256.190 rows=707,629 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 79536kB
9. 794.015 794.015 ↑ 1.1 707,629 1

Index Scan using idx_gl_headers_post_month on gl_headers gh (cost=0.44..115,140.50 rows=743,688 width=71) (actual time=0.021..794.015 rows=707,629 loops=1)

  • Index Cond: ((post_month >= '2020-01-01'::date) AND (post_month <= '2020-03-01'::date))
  • Filter: ((gl_transaction_type_id = ANY ('{1,7}'::integer[])) AND (gl_header_status_type_id <> ALL ('{2,5}'::integer[])) AND (cid = 13531))
  • Rows Removed by Filter: 991401
10. 3,111.034 3,111.034 ↑ 1.0 1 1,555,517

Index Scan using pk_ar_transactions on ar_transactions art (cost=0.43..2.52 rows=1 width=59) (actual time=0.002..0.002 rows=1 loops=1,555,517)

  • Index Cond: ((gh.cid = cid) AND (cid = 13531) AND (id = gh.reference_id))
11. 0.206 1.004 ↓ 1.0 422 1

Hash (cost=159.11..159.11 rows=420 width=16) (actual time=1.004..1.004 rows=422 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
12. 0.798 0.798 ↓ 1.0 422 1

Index Scan using pk_gl_dimensions on gl_dimensions gld (cost=0.27..159.11 rows=420 width=16) (actual time=0.077..0.798 rows=422 loops=1)

  • Index Cond: ((cid = 13531) AND (id = ANY ('{125,126,127,128,129,133,135,136,141,143,144,148,149,150,151,152,153,154,158,159,160,161,448,163,162,164,540,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,531,192,193,532,194,195,196,197,198,199,200,201,202,203,204,413,414,533,415,436,416,417,418,534,419,420,421,437,422,438,423,424,425,426,439,427,428,429,440,430,441,431,535,432,433,434,435,546,445,547,491,487,542,482,549,550,551,552,205,206,207,443,208,536,209,210,211,444,218,219,217,220,221,222,313,223,224,530,251,252,253,323,328,329,332,335,336,305,407,408,409,410,411,412,449,450,463,306,451,452,464,465,488,307,453,497,537,538,539,254,271,272,357,455,489,403,273,347,348,349,350,351,352,353,354,355,274,337,338,339,340,341,342,343,344,346,362,363,442,556,557,558,559,560,96,97,98,294,267,99,241,502,82,83,361,299,277,569,555,563,553,527,514,573,521,520,570,529,80,512,568,404,571,513,575,582,574,526,522,523,524,545,561,562,543,566,564,586,516,567,572,515,541,528,585,275,75,79,76,78,77,318,116,120,511,258,565,387,548,100,494,270,115,101,102,84,230,459,85,483,86,264,312,467,269,281,584,576,577,578,579,359,243,334,466,239,87,477,462,580,581,481,331,117,238,470,263,240,554,308,405,471,285,103,118,104,276,501,265,260,283,233,314,358,284,301,500,290,490,461,478,105,106,288,107,506,495,388,389,468,479,108,88,287,472,321,109,110,485,89,111,112,90,508,91,469,92,400,257,122,406,583,519,93,517,212,345,213,215,392,214,393,394,395,473,113,121,509,293,498,94,360,311,457,401,250,475,510,261,236,29,81,262,484,278,504,279,292,544,480,496,225,325,327,242,119,95,499,291,505,492,259,518,474,458,280,507,282,322,456,114,324,503,525,476,309,460,320}'::integer[])))
13. 1.210 1.210 ↓ 0.0 0 1,210

Index Scan using pk_cached_leases on cached_leases cl (cost=0.42..0.45 rows=1 width=55) (actual time=0.001..0.001 rows=0 loops=1,210)

  • Index Cond: ((cid = gd.cid) AND (cid = 13531) AND (id = gd.lease_id))