explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sV6Z

Settings
# exclusive inclusive rows x rows loops node
1. 8,272.467 8,272.467 ↓ 1.9 618,363 1

CTE Scan on impressions (cost=242,440.71..259,185.53 rows=318,949 width=172) (actual time=424.708..8,272.467 rows=618,363 loops=1)

2.          

CTE rate_card_multiplier_ced

3. 0.013 0.013 ↓ 0.0 0 1

Seq Scan on rate_card_multiplier (cost=0.00..1.84 rows=1 width=32) (actual time=0.013..0.013 rows=0 loops=1)

  • Filter: (active AND (domain_id = 13))
  • Rows Removed by Filter: 67
4.          

CTE impressions

5. 3,600.343 6,448.503 ↓ 1.9 618,363 1

Hash Join (cost=11,779.30..242,438.87 rows=318,949 width=92) (actual time=424.696..6,448.503 rows=618,363 loops=1)

  • Hash Cond: (su.screen_id = screen.id)
6. 549.899 1,253.937 ↓ 2.8 618,363 1

Hash Join (cost=1,622.26..123,113.59 rows=224,365 width=46) (actual time=63.981..1,253.937 rows=618,363 loops=1)

  • Hash Cond: (su.screen_schedule_id = ss.id)
7. 640.245 640.245 ↓ 2.8 618,363 1

Index Scan using lean_screen_usage_screen_id_running_date_index on lean_screen_usage su (cost=0.43..118,406.75 rows=224,365 width=50) (actual time=0.027..640.245 rows=618,363 loops=1)

  • Index Cond: ((screen_id = ANY ('{21926,21927,21928,21929,21930,21931,21935,21936,21937,21938,21939,21940,21941,21942,21943,21944,21945,21946,21947,21948,21949,21950,21951,21952,21953,21954,21955,21956,21957,21958,21959,21960,21961,21962,21963,21964,21965,21966,21967,21968,21969,21970,21971,21972,21973,21974,21975,21976,21977,21978,21979,21980,21981,21982,21983,21984,21985,21986,21987,21988,21989,21990,21991,21992,21993,21994,21995,21996,21997,21998,21999,22000,22001,22002,22003,22004,22005,23162,24443,24444,24927,24928,24929,24930,24931,24932,24933,24934,24935,24936,24937,24938,24939,24940,24941,24942,24943,24944,24945,24946,24947,24948,24949,24950,24951,24952,24953,24954,24955,24956,24957,24958,24959,24960,24961,24962,24963,24964,24965,24966,24967,24968,24969,24970,24971,24972,24973,24974,24975,24976,24977,24978,24979,24980,24981,24982,24983,24984,24985,24986,24987,24988,24989,24990,24991,24992,24993,24994,24995,24996,24997,24998,24999,25000,25001,25002,25003,25004,25005,25006,25007,25008,25009,25010,25011,25012,25013,25014,25015,25016,25017,25018,25019,25020,25021,25022,25023,25024,25025,25026,25027,25028,25029,25030,25031,25032,25033,25034,25035,25036,25037,25038,25039,25040,25041,25042,25043,25044,25045,25046,25047,25048,25049,25050,25051,25052,25053,25054,25055,25056,25057,25058,25059,25060,25061,25062,25063,25064,25065,25066,25067,25068,25069,25070,25071,25072,25073,25074,25075,25076,25077,25078,25079,25080,25081,25082,25083,25084,25085,25086,25087,25088,25089,25090,25091,25092,25093,25094,25095,25096,25097,25098,25099,25100,25101,25102,25103,25104,25105,25106,25107,25108,25109,25110,25111,25112,25113,25114,25115,25116,25117,25118,25119,25120,25121,25122,25123,25124,25125,25126,25127,25128,25129,25130,25131,25132,25133,25134,25135,25136,25137,25138,25139,25140,25141,25142,25143,25144,25145,25146,25147,25148,25149,25150,25151,25152,25153,25154,25155,25156,25157,25158,25159,25160,25161,25162,25163,25164,25286,25287,25484,25485,25486,25487,25488,25489,25490,25491,25492,25493,25494,25495,25496,25497,25498,25499,25500,25501,25502,25503,25504,25505,25506,25507,25508,25509,25510,25511,25512,25513,25514,25515,25516,25517,25518,25519,25520,25521,25522,25523,25524,25525,25526,25527,25528,25529,25530,25531,25532,25533,25534,25535,25536,25537,25538,25539,25540,25541,25542,25543,25544,25545,25546,25547,25548,25549,25550,25551,25552,25553,25554,25555,25556,25557,25558,25559,25560,25561,25562,25563,25564,25565,25566,25567,25568,25569,25570,25571,25572,25573,25574,25575,25576,25577,25578,25579,25580,25581,25582,25583,25584,25585,25586,25587,25588,25589,25590,25591,25592,25593,25594,25595,25596,25597,25598,25599,25600,25601,25602,25603,25604,25605,25606,25607,25608,25609,25610,25611,25612,25613,25614,25615,25616,25617,25618,25619,25620,25621,25622,25623,25624,25625,25626,25627,25628,25629,25630,25631,25632,25633,25634,25635,25636,25637,25638,25639,25640,25641,25642,25643,25644,25645,25646,25647,25648,25649,25650,25651,25652,25653,25654,25655,25656,25657,25658,25659,25660,25661,25662,25663,25664,25665,25666,25667,25668,25669,25670,25671,25672,25673,25674,25675,25676,25677,25678,25679,25680,25681,25682,25683,26081,26082,26083,26084,26085,26086,26087,26088,26089,26090,26091,26092,26093,26094,26095,26096,26097,26098,26099,26100,26101,26102,26103,26104,26105,26106,26107,26108,26109,26110,26111,26112,26113,26114,26115,26116,26117,26118,26119,26120,26121,26122,26123,26124,26125,26126,26127,26128,26129,26130,26131,26132,26133,26134,26135,26136,26137,26138,26139,26140,26141,26142,26143,26144,26145,26146,26147,26148,26149,26150,26151,26152,26153,26154,26155,26156,26157,26158,26159,26160,26161,26162,26163,26164,26165,26166,26167,26168,26169,26170,26171,26172,26173,26174,26175,26176,26177,26178,26179,26180,26181,26182,26183,26184,26185,26186,26187,26188,26189,26190,26191,26192,26193,26194,26195,26196,26197,26198,26199,26200,26201,26202,26203,26204,26205,26206,26207,26208,26209,26210,26211,26212,26213,26214,26215,26216,26217,26218,26219,26220,26221,26222,26223,26224,26225,26226,26227,26228,26229,26230,26231,26232,26233,26234,26235,26236,26237,26238,26239,26240,26241,26242,26243,26244,26245,26246,26247,26248,26249,26250,26251,26252,26253,26254,26255,26256,26257,26258,26259,26260,26261,26262,26263,26264,26265,26266,26267,26268,26269,26270,26271,26272,26273,26274,26275,26276,26277,26278,26279,26280,26281,26282,26283,26284,26285,26286,26287,26288,26289,26290,26291,26292,26293,26294,26295,26296,26297,26298,26299,26300,26301,26302,26303,26304,26305,26306,26307,26308,26309,26310,26311,26312,26313,26314,26315,26316,26317,26318,26319,26320,26321,26322,26323,26324,26325,26326,26327,26328,26329,26330,26331,26332,26333,26334,26335,26336,26337,26338,26339,26340,26341,26342,26343,26344,26345,26346,26347,26348,26349,26350,26351,26352,26353,26354,26355,26356,26357,26358,26359,26360,26361,26362,26363,26364,26365,26366,26367,26368,26369,26370,26371,26372,26373,26374,26375,26376,26377,26378,26379,26380,26381,26382,26383,26384,26385,26386,26387,26388,26389,26390,26391,26392,26393,26394,26395,26396,26397,26398,26399,26400,26401,26402,26403,26404,26405,26406,26407,26408,26409,26410,26411,26412,26413,26414,26415,26416,26417,26418,26419,26420,26421,26422,26423,26424,26425,26426,26427,26428,26429,26430,26431,26432,26433,26434,26435,26436,26437,26438,26439,26440,26441,26442,26443,26444,26445,26446,26447,26448,26449,26450,26451,26452,26453,26454,26455,26456,26457,26458,26459,26460,26461,26462,26463,26464,26465,26466,26467,26468,26469,26470,26471,26472,26473,26474,26475,26476,26477,26478,26479,26480,26481,26482,26483,26484,26485,26486,26487,26488,26489,26490,26491,26492,26493,26494,26495,26496,26497,26498,26499,26500,26501,26502,26503,26504,26505,26506,26507,26508,26509,26510,26511,26512,26513,26514,26515,26516,26517,26518,26519,26520,26521,26522,26523,26524,26525,26526,26527,26528,26529,26530,26531,26532,26533,26534,26535,26536,26537,26538,26539,26540,26541,26542,26543,26544,26545,26546,26547,26548,26549,26550,26551,26552,26553,26554,26555,26556,26557,26558,26559,26560,26561,26562,26563,26564,26565,26566,26567,26568,26569,26570,26571,26572,26573,26574,26575,26576,26577,26578,26579,26580,26581,26582,26583,26584,26585,26586,26587,26588,26589,26590,26591,26592,26593,26594,26595,26596,26597,26598,26599,26600,26601,26602,26603,26604,26605,26606,26607,26608,26609,26610,26611,26612,26613,26614,26615,26616,26617,26618,26619,26620,26621,26622,26623,26624,26625,26626,26627,26628,26629,26630,26631,26632,26633,26634,26635,26636,26637,26638,26639,26640,26641,26642,26643,26644,26645,26646,26647,26648,26649,26650,26651,26652,26653,26654,26655,26656,26657,26658,26659,26660,26661,26662,26663,26664,26665,26666,26667,26668,26669,26670,26671,26672,26673,26674,26675,26676,26677,26678,26679,26680,26681,26682,26683,26684,26685,26686,26687,26688,26689,26690,26691,26692,26693,26694,26695,26696,26697,26698,26699,26700,26701,26702,26703,26704,26705,26706,26707,26708,26709,26710,26711,26712,26713,26714,26715,26716,26717,26718,26719,26720,26721,26722,26723,26724,26725,26726,26727,26728,26729,26730,26731,26732,26733,26734,26735,26736,26737,26738,26739,26740,26741,26742,26743,26744,26745,26746,26747,26748,26749,26750,26751,26752,26753,26754,26755,26756,26757,26758,26759,26760,26761,26762,26763,26764,26765,26766,26767,26768,26769,26770,26771,26772,26773,26774,26775,26776,26777,26778,26779,26780,26781,26782,26783,26784,26785,26786,26787,26788,26789,26790,26791,26792,26793,26794,26795,26796,26797,26798,26799,26800,26801,26802,26803,26804,26805,26806,26807,26808,26809,26810,26811,26812,26813,26814,26815,26816,26817,26818,26819,26820,26821,26822,26823,26824,26825,26826,26827,26828,26829,26830,26831,26832,26833,26834,26835,26836,26837,26838,26839,26840,26841,26842,26843,26844,26845,26846,26847,26848,26849,26850,26851,26852,26853,26854,26855,26856,26857,26858,26859,26860,26861,26862,26863,26864,26865,26866,26867,26868,26869,26870,26871,26872,26873,26874,26875,26876,26877,26878,26879,26880,26881,26882,26883,26884,26885,26886,26887,26888,26889,26890,26891,26892,26893,26894,26895,26896,26897,26898,26899,26900,26901,26902,26903,26904,26905,26906,26907,26908,26909,26910,26911,26912,26913,26914,26915,26916,26917,26918,26919,26920,26921,26922,26923,26924,26925,26926,26927,26928,26929,26930,26931,26932,26933,26934,26935,26936,26937,26938,26939,26940,26941,26942,26943,26944,26945,26946,26947,26948,26949,26950,26951,26952,26953,26954,26955,26956,26957,26958,26959,26960,26961,26962,26963,26964,26965,26966,26967,26968,26969,26970,26971,26972,26973,26974,26975,26976,26977,26978,26979,26980,26981,26982,26983,26984,26985,26986,26987,26988,26989,26990,26991,26992,26993,26994,26995,26996,26997,26998,26999,27000,27001,27002,27003,27004,27005,27006,27007,27008,27009,27010,27011,27012,27013,27014,27015,27016,27017,27018,27019,27020,27021,27022,27023,27024,27025,27026,27027,27028,27029,27030,27031,27032,27033,27034,27035,27036,27037,27038,27039,27040,27041,27042,27043,27044,27045,27046,27047,27048,27049,27050,27051,27052,27053,27054,27055,27056,27057,27058,27059,27060,27061,27062,27063,27064,27065,27066,27067,27068,27069,27070,27071,27072,27073,27074,27075,27076,27077,27078,27079,27080,27081,27082,27083,27084,27085,27086,27087,27088,27089,27090,27091,27092,27093,27094,27095,27096,27097,27098,27099,27100,27101,27102,27103,27104,27105,27106,27107,27108,27109,27110,27111,27112,27113,27114,27115,27116,27117,27118,27119,27120,27121,27122,27123,27124,27125,27126,27127,27128,27129,27130,27131,27132,27133,27134,27135,27136,27137,27138,27139,27140,27141,27142,27143,27144,27145,27146,27147,27148,27149,27150,27151,27152,27153,27154,27155,27156,27157,27158,27159,27160,27161,27162,27163,27164,27165,27166,27167,27168,27169,27170,27171,27172,27173,27174,27175,27176,27177,27178,27179,27180,27181,27182,27183}'::integer[])) AND (running_date >= '2019-12-03'::date) AND (running_date <= '2020-12-17'::date))
  • Filter: ((running_times && '[00:00:00,23:59:59)'::timerange) AND ((dow_mask & 127) > 0))
8. 30.283 63.793 ↑ 1.0 40,570 1

Hash (cost=1,114.70..1,114.70 rows=40,570 width=12) (actual time=63.793..63.793 rows=40,570 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2414kB
9. 33.510 33.510 ↑ 1.0 40,570 1

Seq Scan on screen_schedule ss (cost=0.00..1,114.70 rows=40,570 width=12) (actual time=0.007..33.510 rows=40,570 loops=1)

10. 74.614 357.497 ↓ 1.7 131,552 1

Hash (cost=8,767.62..8,767.62 rows=75,634 width=24) (actual time=357.497..357.497 rows=131,552 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 4058kB
11. 129.734 282.883 ↓ 1.7 131,552 1

Hash Right Join (cost=2,412.98..8,767.62 rows=75,634 width=24) (actual time=89.912..282.883 rows=131,552 loops=1)

  • Hash Cond: (_as.screen_id = screen.id)
12. 63.374 63.374 ↑ 1.0 127,876 1

Seq Scan on audience_schedule _as (cost=0.00..5,118.76 rows=127,876 width=16) (actual time=0.010..63.374 rows=127,876 loops=1)

13. 22.628 89.775 ↓ 1.0 31,561 1

Hash (cost=2,019.62..2,019.62 rows=31,469 width=16) (actual time=89.775..89.775 rows=31,561 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1658kB
14. 36.607 67.147 ↓ 1.0 31,561 1

Hash Left Join (cost=35.11..2,019.62 rows=31,469 width=16) (actual time=1.299..67.147 rows=31,561 loops=1)

  • Hash Cond: (screen.rate_card_id = rate_card.id)
15. 29.270 29.270 ↓ 1.0 31,561 1

Seq Scan on screen (cost=0.00..1,744.05 rows=31,469 width=16) (actual time=0.015..29.270 rows=31,561 loops=1)

  • Filter: active
  • Rows Removed by Filter: 21644
16. 0.225 1.270 ↑ 1.0 343 1

Hash (cost=30.82..30.82 rows=343 width=16) (actual time=1.270..1.270 rows=343 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
17. 0.435 1.045 ↑ 1.0 343 1

Hash Right Join (cost=14.53..30.82 rows=343 width=16) (actual time=0.404..1.045 rows=343 loops=1)

  • Hash Cond: (rate_card_schedule.rate_card_id = rate_card.id)
18. 0.224 0.224 ↑ 1.0 343 1

Seq Scan on rate_card_schedule (cost=0.00..12.24 rows=343 width=16) (actual time=0.006..0.224 rows=343 loops=1)

  • Filter: active
  • Rows Removed by Filter: 81
19. 0.177 0.386 ↑ 1.0 343 1

Hash (cost=10.24..10.24 rows=343 width=8) (actual time=0.386..0.386 rows=343 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
20. 0.209 0.209 ↑ 1.0 343 1

Seq Scan on rate_card (cost=0.00..10.24 rows=343 width=8) (actual time=0.007..0.209 rows=343 loops=1)

  • Filter: active
  • Rows Removed by Filter: 81
21.          

SubPlan (forHash Join)

22. 1,236.726 1,236.726 ↑ 1.0 1 618,363

Aggregate (cost=0.03..0.04 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=618,363)

23. 0.000 0.000 ↓ 0.0 0 618,363

CTE Scan on rate_card_multiplier_ced r (cost=0.00..0.03 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=618,363)

  • Filter: ((su.running_date >= start_date) AND (su.running_date <= end_date))
Planning time : 11.882 ms
Execution time : 8,515.071 ms