explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TKVx

Settings
# exclusive inclusive rows x rows loops node
1. 1,891.142 3,696.306 ↓ 11.8 416,641 1

Hash Right Join (cost=313,068.70..341,961.87 rows=35,219 width=36) (actual time=1,792.232..3,696.306 rows=416,641 loops=1)

  • Hash Cond: ((com.zo_id = z.zo_id) AND (com.br_id = n.br_id))
2. 13.801 24.062 ↑ 1.0 99,345 1

Bitmap Heap Scan on rtsepezobrav com (cost=3,263.86..7,936.45 rows=100,034 width=16) (actual time=10.355..24.062 rows=99,345 loops=1)

  • Recheck Cond: ((se_id = 1) AND (pe_id = 3) AND (ad_id = 49))
  • Heap Blocks: exact=773
3. 10.261 10.261 ↑ 1.0 99,345 1

Bitmap Index Scan on rtsepezobrav_unique (cost=0.00..3,238.85 rows=100,034 width=0) (actual time=10.261..10.261 rows=99,345 loops=1)

  • Index Cond: ((se_id = 1) AND (pe_id = 3) AND (ad_id = 49))
4. 262.714 1,781.102 ↓ 11.8 416,641 1

Hash (cost=309,276.56..309,276.56 rows=35,219 width=180) (actual time=1,781.102..1,781.102 rows=416,641 loops=1)

  • Buckets: 524288 (originally 65536) Batches: 1 (originally 1) Memory Usage: 69685kB
5. 260.683 1,518.388 ↓ 11.8 416,641 1

Hash Join (cost=3,065.83..309,276.56 rows=35,219 width=180) (actual time=51.092..1,518.388 rows=416,641 loops=1)

  • Hash Cond: ((z.re_id = rtseperereav.re_id) AND (n.re_id = rtseperereav.re2_id))
6. 158.391 1,257.681 ↓ 5.9 416,641 1

Hash Join (cost=3,061.40..308,391.65 rows=70,438 width=180) (actual time=51.061..1,257.681 rows=416,641 loops=1)

  • Hash Cond: (x.zo_id = z.zo_id)
7. 299.955 1,050.663 ↓ 5.6 416,641 1

Hash Join (cost=175.69..304,241.11 rows=74,728 width=172) (actual time=2.401..1,050.663 rows=416,641 loops=1)

  • Hash Cond: (x.co_id = n.co_id)
8. 748.337 748.337 ↓ 1.2 416,641 1

Index Scan using rtpezocoav_unique on rtpezocoav x (cost=0.56..301,993.10 rows=353,495 width=16) (actual time=0.024..748.337 rows=416,641 loops=1)

  • Index Cond: ((pe_id = 3) AND (zo_id = ANY ('{336924,336925,336926,336927,336928,336929,336930,336931,336932,336933,336934,336935,336936,336937,336938,336939,336940,336941,336942,336943,336944,336945,336946,336947,336948,336949,336950,336951,336952,336953,336954,336955,336956,336957,336958,336959,336960,336961,336962,336963,336964,336965,336966,336967,336968,336969,336970,336971,336972,336973,336974,336975,336976,336977,336978,336979,336980,336981,336982,336983,336984,336985,336986,336987,336988,336989,336990,336991,336992,336993,336994,336995,336996,336997,336998,336999,337000,337001,337002,337003,337004,337005,337006,337007,337008,337009,337010,337011,337012,337013,337014,337015,337016,337017,337018,337019,337020,337021,337022,337023,337024,337025,337026,337027,337028,337029,337030,337031,337032,337033,337034,337035,337036,337037,337038,337039,337040,337041,337042,337043,337044,337045,337046,337047,337048,337049,337050,337051,337052,337053,337054,337055,337056,337057,337058,337059,337060,337061,337062,337063,337064,337065,337066,337067,337068,337069,337070,337071,337072,337073,337074,337075,337076,337077,337078,337079,337080,337081,337082,337083,337084,337085,337086,337087,337088,337089,337090,337091,337092,337093,337094,337095,337096,337097,337098,337099,337100,337101,337102,337103,337104,337105,337106,337107,337108,337109,337110,337111,337112,337113,337114,337115,337116,337117,337118,337119,337120,337121,337122,337123,337124,337125,337126,337127,337128,337129,337130,337131,337132,337133,337134,337135,337136,337137,337138,337139,337140,337141,337142,337143,337144,337145,337146,337147,337148,337149,337150,337151,337152,337153,337154,337155,337156,337157,337158,337159,337160,337161,337162,337163,337164,337165,337166,337167,337168,337169,337170,337171,337172,337173,337174,337175,337176,337177,337178,337179,337180,337181,337182,337183,337184,337185,337186,337187,337188,337189,337190,337191,337192,337193,337194,337195,337196,337197,337198,337199,337200,337201,337202,337203,337204,337205,337206,337207,337208,337209,337210,337211,337212,337213,337214,337215,337216,337217,337218,337219,337220,337221,337222,337223,337224,337225,337226,337227,337228,337229,337230,337231,337232,337233,337234,337235,337236,337237,337238,337239,337240,337241,337242,337243,337244,337245,337246,337247,337248,337249,337250,337251,337252,337253,337254,337255,337256,337257,337258,337259,337260,337261,337262,337263,337264,337265,337266,337267,337268,337269,337270,337271,337272,337273,337274,337275,337276,337277,337278,337279,337280,337281,337282,337283,337284,337285,337286,337287,337288,337289,337290,337291,337292,337293,337294,337295,337296,337297,337298,337299,337300,337301,337302,337303,337304,337305,337306,337307,337308,337309,337310,337311,337312,337313,337314,337315,337316,337317,337318,337319,337320,337321,337322,337323,337324,337325,337326,337327,337328,337329,337330,337331,337332,337333,337334,337335,337336,337337,337338,337339,337340,337341,337342,337343,337344,337345,337346,337347,337348,337349,337350,337351,337352,337353,337354,337355,337356,337357,337358,337359,337360,337361,337362,337363,337364,337365,337366,337367,337368,337369,337370,337371,337372,337373,337374,337375,337376,337377,337378,337379,337380,337381,337382,337383,337384,337385,337386,337387,337388,337389,337390,337391,337392,337393,337394,337395,337396,337397,337398,337399,337400,337401,337402,337403,337404,337405,337406,337407,337408,337409,337410,337411,337412,337413,337414,337415,337416,337417,337418,337419,337420,337421,337422,337423,337424,337425,337426,337427,337428,337429,337430,337431,337432,337433,337434,337435,337436,337437,337438,337439,337440,337441,337442,337443,337444,337445,337446,337447,337448,337449,337450,337451,337452,337453,337454,337455,337456,337457,337458,337459,337460,337461,337462,337463,337464,337465,337466,337467,337468,337469,337470,337471,337472,337473,337474,337475,337476,337477,337478,337479,337480,337481,337482,337483,337484,337485,337486,337487,337488,337489,337490,337491,337492,337493,337494,337495,337496,337497,337498,337499,337500,337501,337502,337503,337504,337505,337506,337507,337508,337509,337510,337511,337512,337513,337514,337515,337516,337517,337518,337519,337520,337521,337522,337523,337524,337525,337526,337527,337528,337529,337530,337531,337532,337533,337534,337535,337536,337537,337538,337539,337540,337541,337542,337543,337544,337545,337546,337547,337548,337549,337550,337551,337552,337553,337554,337555,337556,337557,337558,337559,337560,337561,337562,337563,337564,337565,337566,337567,337568,337569,337570,337571,337572,337573,337574,337575,337576,337577,337578,337579,337580,337581,337582,337583,337584,337585,337586,337587,337588,337589,337590,337591,337592,337593,337594,337595,337596,337597,337598,337599,337600,337601,337602,337603,337604,337605,337606,337607,337608,337609,337610,337611,337612,337613,337614,337615,337616,337617,337618,337619,337620,337621,337622,337623,337624,337625,337626,337627,337628,337629,337630,337631,337632,337633,337634,337635,337636,337637,337638,337639,337640,337641,337642,337643,337644,337645,337646,337647,337648,337649,337650,337651,337652,337653,337654,337655,337656,337657,337658,337659,337660,337661,337662,337663,337664,337665,337666,337667,337668,337669,337670,337671,337672,337673,337674,337675,337676,337677,337678,337679,337680,337681,337682,337683,337684,337685,337686,337687,337688,337689,337690,337691,337692,337693,337694,337695,337696,337697,337698,337699,337700,337701,337702,337703,337704,337705,337706,337707,337708,337709,337710,337711,337712,337713,337714,337715,337716,337717,337718,337719,337720,337721,337722,337723,337724,337725,337726,337727,337728,337729,337730,337731,337732,337733,337734,337735,337736,337737,337738,337739,337740,337741,337742,337743,337744,337745,337746,337747,337748,337749,337750,337751,337752,337753,337754,337755,337756,337757,337758,337759,337760,337761,337762,337763,337764,337765,337766,337767,337768,337769,337770,337771,337772,337773,337774,337775,337776,337777,337778,337779,337780,337781,337782,337783,337784,337785,337786,337787,337788,337789,337790,337791,337792,337793,337794,337795,337796,337797,337798,337799,337800,337801,337802,337803,337804,337805,337806,337807,337808,337809,337810,337811,337812,337813,337814,337815,337816,337817,337818,337819,337820,337821,337822,337823,337824,337825,337826,337827,337828,337829,337830,337831,337832,337833,337834,337835,337836,337837,337838,337839,337840,337841,337842,337843,337844,337845,337846,337847,337848,337849,337850,337851,337852,337853,337854,337855,337856,337857,337858,337859,337860,337861,337862,337863,337864,337865,337866,337867,337868,337869,337870,337871,337872,337873,337874,337875,337876,337877,337878,337879,337880,337881,337882,337883,337884,337885,337886,337887,337888,337889,337890,337891,337892,337893,337894,337895,337896,337897,337898,337899,337900,337901,337902,337903,337904,337905,337906,337907,337908,337909,337910,337911,337912,337913,337914,337915,337916,337917,337918,337919,337920,337921,337922,337923,337924,337925,337926,337927,337928,337929,337930,337931,337932,337933,337934,337935,337936,337937,337938,337939,337940,337941,337942,337943,337944,337945,337946,337947,337948,337949,337950,337951,337952,337953,337954,337955,337956,337957,337958,337959,337960,337961,337962,337963,337964,337965,337966,337967,337968,337969,337970,337971,337972,337973,337974,337975,337976,337977,337978,337979,337980,337981,337982,337983,337984,337985,337986,337987,337988,337989,337990,337991,337992,337993,337994,337995,337996,337997,337998,337999,338000,338001,338002,338003,338004,338005,338006,338007,338008,338009,338010,338011,338012,338013,338014,338015,338016,338017,338018,338019,338020,338021,338022,338023,338024,338025,338026,338027}'::integer[])) AND (ad_id = 2))
9. 0.594 2.371 ↓ 4.8 1,522 1

Hash (cost=171.14..171.14 rows=319 width=160) (actual time=2.371..2.371 rows=1,522 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 216kB
10. 0.885 1.777 ↓ 4.8 1,522 1

Hash Join (cost=6.29..171.14 rows=319 width=160) (actual time=0.034..1.777 rows=1,522 loops=1)

  • Hash Cond: (n.shop_concept = (sh.sh_id)::double precision)
11. 0.871 0.871 ↓ 1.2 1,522 1

Seq Scan on calc_nodes n (cost=0.00..155.28 rows=1,276 width=140) (actual time=0.009..0.871 rows=1,522 loops=1)

  • Filter: ((pe_id = 3) AND (se_id = 1))
  • Rows Removed by Filter: 1522
12. 0.004 0.021 ↑ 1.0 4 1

Hash (cost=6.24..6.24 rows=4 width=20) (actual time=0.020..0.021 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.017 0.017 ↑ 1.0 4 1

Seq Scan on calc_shopconcepts sh (cost=0.00..6.24 rows=4 width=20) (actual time=0.014..0.017 rows=4 loops=1)

  • Filter: ((pe_id = 3) AND (se_id = 1))
  • Rows Removed by Filter: 12
14. 7.343 48.627 ↓ 1.2 33,115 1

Hash (cost=2,541.12..2,541.12 rows=27,567 width=8) (actual time=48.627..48.627 rows=33,115 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 1806kB
15. 41.284 41.284 ↓ 1.2 33,115 1

Seq Scan on calc_zones z (cost=0.00..2,541.12 rows=27,567 width=8) (actual time=20.023..41.284 rows=33,115 loops=1)

  • Filter: ((pe_id = 3) AND (se_id = 1))
  • Rows Removed by Filter: 33115
16. 0.002 0.024 ↑ 2.0 1 1

Hash (cost=4.40..4.40 rows=2 width=16) (actual time=0.024..0.024 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.000 0.022 ↑ 2.0 1 1

Append (cost=0.00..4.40 rows=2 width=16) (actual time=0.009..0.022 rows=1 loops=1)

18. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on rtseperereav (cost=0.00..1.07 rows=1 width=16) (actual time=0.009..0.010 rows=1 loops=1)

  • Filter: ((pe_id = 3) AND (se_id = 1) AND (ad_id = 22))
  • Rows Removed by Filter: 3
19. 0.000 0.012 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.00..3.33 rows=1 width=16) (actual time=0.012..0.012 rows=0 loops=1)

20. 0.001 0.012 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.00..3.32 rows=1 width=64) (actual time=0.012..0.012 rows=0 loops=1)

  • Join Filter: ((av_1.pe_id = rtpe.id) AND (av_1.se_id = av.se_id) AND (av_1.ad_id = av.ad_id) AND (av_1.re_id = av.re_id) AND (av_1.re2_id = av.re2_id))
21. 0.001 0.011 ↓ 0.0 0 1

Nested Loop (cost=0.00..2.22 rows=1 width=28) (actual time=0.011..0.011 rows=0 loops=1)

  • Join Filter: (av.pe_id = rtpe.baseline_id)
  • Rows Removed by Join Filter: 2
22. 0.002 0.002 ↓ 2.0 2 1

Seq Scan on rtseperereav av (cost=0.00..1.06 rows=1 width=28) (actual time=0.001..0.002 rows=2 loops=1)

  • Filter: ((se_id = 1) AND (ad_id = 22))
  • Rows Removed by Filter: 2
23. 0.008 0.008 ↑ 1.0 1 2

Seq Scan on rtpe (cost=0.00..1.15 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=2)

  • Filter: (id = 3)
  • Rows Removed by Filter: 11
24. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.07 rows=1 width=20) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Seq Scan on rtseperereav av_1 (cost=0.00..1.07 rows=1 width=20) (never executed)

  • Filter: ((pe_id = 3) AND (se_id = 1) AND (ad_id = 22))
Planning time : 7.887 ms
Execution time : 3,757.957 ms