explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UFsE : Optimization for: plan #Ac7w

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 3.971 80,754.481 ↑ 688.4 1,210 1

Hash Left Join (cost=967,174.61..2,845,518.30 rows=832,966 width=257) (actual time=72,450.945..80,754.481 rows=1,210 loops=1)

  • Hash Cond: ((gd.cid = cl.cid) AND (gd.lease_id = cl.id))
2. 612.697 79,866.524 ↑ 688.4 1,210 1

Hash Join (cost=941,507.26..2,555,175.98 rows=832,966 width=229) (actual time=71,564.742..79,866.524 rows=1,210 loops=1)

  • Hash Cond: (COALESCE(art.gl_dimension_id, gd.gl_dimension_id) = gld.id)
3. 4,154.784 79,252.882 ↓ 1.6 1,555,517 1

Hash Left Join (cost=941,342.90..2,552,485.46 rows=953,944 width=221) (actual time=56,327.329..79,252.882 rows=1,555,517 loops=1)

  • Hash Cond: ((gh.cid = art.cid) AND (gh.reference_id = art.id))
  • Join Filter: (gh.gl_transaction_type_id = 7)
4. 1,458.018 52,434.693 ↓ 1.6 1,555,517 1

Hash Join (cost=124,440.78..1,552,622.13 rows=953,944 width=170) (actual time=32,482.388..52,434.693 rows=1,555,517 loops=1)

  • Hash Cond: (gd.property_id = lp.property_id)
5. 25,081.630 50,976.586 ↑ 1.0 1,573,301 1

Hash Join (cost=124,436.60..1,537,157.49 rows=1,578,942 width=170) (actual time=32,482.292..50,976.586 rows=1,573,301 loops=1)

  • Hash Cond: (gd.gl_header_id = gh.id)
6. 24,640.085 24,640.085 ↓ 1.0 46,502,394 1

Seq Scan on gl_details gd (cost=0.00..1,290,664.20 rows=46,497,776 width=103) (actual time=0.010..24,640.085 rows=46,502,394 loops=1)

  • Filter: (cid = 13531)
7. 461.951 1,254.871 ↑ 1.1 707,629 1

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

  • Buckets: 1048576 Batches: 1 Memory Usage: 79536kB
8. 792.920 792.920 ↑ 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.027..792.920 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
9. 0.042 0.089 ↑ 1.0 87 1

Hash (cost=3.09..3.09 rows=87 width=8) (actual time=0.088..0.089 rows=87 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
10. 0.047 0.047 ↑ 1.0 87 1

Seq Scan on load_prop lp (cost=0.00..3.09 rows=87 width=8) (actual time=0.006..0.047 rows=87 loops=1)

  • Filter: (cid = 13531)
11. 7,382.131 22,663.405 ↓ 1.0 12,238,392 1

Hash (cost=502,085.60..502,085.60 rows=12,229,568 width=59) (actual time=22,663.404..22,663.405 rows=12,238,392 loops=1)

  • Buckets: 8388608 Batches: 4 Memory Usage: 274003kB
12. 15,281.274 15,281.274 ↓ 1.0 12,238,392 1

Seq Scan on ar_transactions art (cost=0.00..502,085.60 rows=12,229,568 width=59) (actual time=0.012..15,281.274 rows=12,238,392 loops=1)

  • Filter: (cid = 13531)
13. 0.205 0.945 ↓ 1.0 422 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
14. 0.740 0.740 ↓ 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.740 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[])))
15. 264.746 883.986 ↑ 1.0 409,540 1

Hash (cost=19,524.25..19,524.25 rows=409,540 width=55) (actual time=883.985..883.986 rows=409,540 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 29716kB
16. 619.240 619.240 ↑ 1.0 409,540 1

Seq Scan on cached_leases cl (cost=0.00..19,524.25 rows=409,540 width=55) (actual time=1.027..619.240 rows=409,540 loops=1)

  • Filter: (cid = 13531)