explain.depesz.com

PostgreSQL's explain analyze made readable

Result: he8M

Settings
# exclusive inclusive rows x rows loops node
1. 6,026.303 33,405.800 ↑ 217.0 6 1

GroupAggregate (cost=344,509.91..347,492.03 rows=1,302 width=108) (actual time=24,873.530..33,405.800 rows=6 loops=1)

  • Group Key: (to_char(date_trunc('month'::text, (elt.month_year)::timestamp with time zone), 'Mon-YY'::text))
2.          

Initplan (for GroupAggregate)

3. 0.007 0.007 ↑ 1.0 1 1

Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.005..0.007 rows=1 loops=1)

4. 10,882.115 27,379.460 ↓ 28.1 5,502,339 1

Sort (cost=344,509.89..344,998.77 rows=195,553 width=52) (actual time=23,127.358..27,379.460 rows=5,502,339 loops=1)

  • Sort Key: (to_char(date_trunc('month'::text, (elt.month_year)::timestamp with time zone), 'Mon-YY'::text))
  • Sort Method: external merge Disk: 212,752kB
5. 0.000 16,497.345 ↓ 28.1 5,502,339 1

Gather (cost=1,006.75..327,323.51 rows=195,553 width=52) (actual time=121.657..16,497.345 rows=5,502,339 loops=1)

  • Workers Planned: 3
  • Params Evaluated: $1
  • Workers Launched: 3
6. 3,663.171 17,231.207 ↓ 21.8 1,375,585 4 / 4

Hash Left Join (cost=6.75..306,768.21 rows=63,082 width=52) (actual time=41.858..17,231.207 rows=1,375,585 loops=4)

  • Hash Cond: (entity.tier_id = tier.id)
7. 2,258.443 13,567.756 ↓ 21.8 1,375,585 4 / 4

Nested Loop (cost=0.99..306,119.57 rows=63,082 width=18) (actual time=41.422..13,567.756 rows=1,375,585 loops=4)

8. 70.677 70.677 ↓ 3.2 8,097 4 / 4

Parallel Index Scan using cdno_deleted_ix on child_dno entity (cost=0.42..44,981.08 rows=2,515 width=22) (actual time=0.063..70.677 rows=8,097 loops=4)

  • Index Cond: (client_id = 1,002)
  • Filter: ((ageing IS NOT NULL) AND (dnoid = ANY ('{7470,17102,13275,18571,18733,15602,13527,14067,19070,16159,12458,15710,5691,18498,15686,15591,19229,17803,12794,7958,3010,15126,13566,15321,14167,15745,16116,18547,17573,16751,15447,15141,7955,8594,2740,14179,12799,13196,5601,16072,17600,15426,3964,18696,16288,16762,12676,16742,17907,12614,7927,14957,17571,13271,8472,10205,14831,17519,16539,18920,17721,8896,17212,15642,18222,16641,18815,18950,3149,18451,13812,15712,14058,12803,3150,15732,17720,3071,12798,15755,13291,16717,15695,18876,12718,13278,13218,13376,7204,16730,16188,12801,8589,17105,18085,14850,15209,2226,12792,16619,12775,17692,18304,16135,5692,15740,8593,17104,15564,8286,12862,15721,18690,12795,13503,15407,17619,13276,17927,16696,12884,15464,12117,3673,15493,16280,14787,18392,15639,13273,12807,3801,13464,5659,16771,18596,8620,14188,15485,13078,18914,15907,12471,16728,17115,15592,16296,3072,17027,14949,14828,16074,13530,15640,3005,2211,6618,15456,15517,13204,15742,13458,18290,14204,8346,16748,11386,13460,18466,17624,3009,15386,19237,17003,13531,7921,6619,15154,18751,17111,19158,3328,18275,14178,13452,7597,16622,16083,15418,3804,3125,17147,15460,2220,6620,18846,16755,15916,7376,16202,15651,15683,19171,14649,16711,16713,15171,12790,15646,10106,15428,2241,15513,2246,5658,16274,15714,16286,18093,6900,18333,16637,7959,13186,17649,14643,18198,17726,15668,16263,18861,3677,17146,2388,17722,13281,11487,13203,16387,13094,9647,18674,15749,8391,16270,12789,3415,17994,14174,13623,18426,15441,17110,14647,14780,9644,7252,16260,16702,15697,2389,3674,8381,19165,14795,9230,17943,2214,2245,12865,14130,3357,15430,13374,17305,3416,15688,18538,15535,8379,14740,14060,9226,14823,18711,15629,14182,12470,3073,14463,13958,11435,3007,6901,16075,17770,15424,16828,13429,14953,7324,16575,13432,9637,2244,18282,13201,17101,15681,2222,3682,13440,14169,6617,16578,8622,16253,18086,15565,12716,4074,16726,2221,3132,14947,16142,15699,17322,12596,15658,15750,15439,9632,17109,3705,3689,8595,18779,16766,19224,18481,12797,18046,7205,16284,12612,14826,16985,15554,14152,18482,16122,12611,6995,2217,15487,11390,3688,10109,13957,12815,15660,18022,3011,8590,13381,2224,15558,15624,14833,15148,13096,12630,16657,15560,15451,14951,9227,15550,15416,15644,4315,16583,14857,15130,3065,2219,7926,14186,13198,12467,16268,17910,12468,16206,8591,10110,15748,18278,7957,15743,14150,3014,14814,3675,16997,17963,3797,3236,17781,17714,4073,15566,10023,16653,16151,18830,18084,13512,16815,16650,18066,3893,5693,2390,18708,8454,18105,2355,13674,19125,3008,16453,16190,13209,15753,15551,17904,12720,18183,12805,12863,16333,3069,15489,15751,3359,15717,13482,2243,16614,7224,18260,10327,3905,14137,3130,3122,17723,3686,12866,14862,18439,8390,12864,14789,13454,17983,13390,13091,16153,14856,13427,17852,11488,3013,2225,12598,18791,13532,19184,3003,8377,5654,13286,13681,13827,18798,15432,3127,3615,13295,13824,5695,15631,17742,3128,16262,15757,16623,15500,16782,3147,3358,3129,13670,3012,16078,14791,13279,15610,16257,18638,11450,16132,13213,19175,16821,16655,12788,12670,7225,3802,16798,16212,13925,6616,14681,18807,15693,2210,12816,14829,12469,14836,15515,10326,15156,17839,12868,3126,2223,13515,16954,15521,18480,15478,17779,14860,13401,15689,13796,14193,13525,17108,16669,17107,13385,15746,4516,15961,9549,2237,16647,16804,15152,3670,14782,15519,15438,13293,3685,14994,2315,16719,15429,18921,12441,18973,3418,16734,12971,15600,15691,17664,13085,8473,16989,3613,14859,3152,18873,14847,10165,18162,14815,2239,12683,3413,12472,7925,16282,16133,16385,7920,18527,15589,16258,2739,16138,5690,13288,9233,19223,18135,15483,18056,17113,17177,18714,12674,16139,15948,15318,8222,9634,2356,18888,3411,12673,13383,12666,16272,13462,3070,3360,18109,16767,3614,18026,14056,17916,13206,8621,18145,2247,3683,18025,17569,8592,14945,3151,15511,15715,16294,2212,8389,7928,16140,12820,16085,16276,9633,3713,16924,18131,3598,18928,16617,18150,3676,17295,15637,16076,18024,16192,15648,16073,15552,18079,17682,14158,3672,9645,18028,14184,16648,12814,16660,18171,16651,12152,14790,16150,7306,17932,3064,14180,7223,16259,19180,16646,9631,2242,13430,7922,17096,12625,17326,17106,17203,12819,17592,13821,12679,18557,8897,13694,18292,7924,4057,14645,3004,15653,9229,18177,12800,14941,14955,3002,13221,17905,16389,3006,13219,16290,15562,14864,10108,14838,15556,15319,12818,13501,17561,15744,15635,18213,3796,18684,2213,17178,15614,10019,8378,14964,16689,17542,13403,3687,17112,18009,15633,15580,9646,16292,18099,7203,15317,15434,15622,13936,8380,7960,14793,12791,16144,3892,11421,16993,15491,6505,16644,13533,3697,2949,12787,15670,17892,15634,17687}'::integer[])))
  • Rows Removed by Filter: 182
9. 11,238.636 11,238.636 ↑ 1.2 170 32,388 / 4

Index Scan using idx_entity_lead_time_5 on entity_lead_time elt (cost=0.57..101.71 rows=212 width=8) (actual time=0.968..1.388 rows=170 loops=32,388)

  • Index Cond: (entity_id = entity.id)
  • Filter: ((ageing IS NOT NULL) AND (month_year > $1) AND (entity_type_id = 13))
  • Rows Removed by Filter: 750
10. 0.141 0.280 ↑ 1.0 167 4 / 4

Hash (cost=3.67..3.67 rows=167 width=10) (actual time=0.279..0.280 rows=167 loops=4)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
11. 0.139 0.139 ↑ 1.0 167 4 / 4

Seq Scan on tier (cost=0.00..3.67 rows=167 width=10) (actual time=0.017..0.139 rows=167 loops=4)

12.          

SubPlan (for GroupAggregate)

13. 0.030 0.030 ↑ 1.0 1 6

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=6)

Planning time : 4.513 ms
Execution time : 33,442.079 ms