explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ay0

Settings
# exclusive inclusive rows x rows loops node
1. 12.443 1,304.508 ↓ 251.6 190,484 1

Subquery Scan on zzz (cost=280,462.87..280,565.06 rows=757 width=24) (actual time=1,183.169..1,304.508 rows=190,484 loops=1)

  • Filter: (zzz.allocation > 0)
  • Rows Removed by Filter: 92,499
2. 97.784 1,292.065 ↓ 124.6 282,983 1

WindowAgg (cost=280,462.87..280,536.67 rows=2,271 width=24) (actual time=1,183.168..1,292.065 rows=282,983 loops=1)

3. 58.332 1,194.281 ↓ 124.6 282,983 1

Sort (cost=280,462.87..280,468.54 rows=2,271 width=36) (actual time=1,183.121..1,194.281 rows=282,983 loops=1)

  • Sort Key: c.zoneid
  • Sort Method: quicksort Memory: 34,397kB
4. 42.769 1,135.949 ↓ 124.6 282,983 1

Hash Join (cost=261,393.09..280,336.27 rows=2,271 width=36) (actual time=1,087.634..1,135.949 rows=282,983 loops=1)

  • Hash Cond: (z.zo_id = c.zoneid)
5. 63.898 63.898 ↑ 1.0 54,883 1

Seq Scan on calc_zones z (cost=0.00..18,440.24 rows=54,883 width=20) (actual time=58.331..63.898 rows=54,883 loops=1)

  • Filter: ((se_id = 3,608) AND (pe_id = 4))
6. 30.750 1,029.282 ↓ 124.6 282,983 1

Hash (cost=261,364.70..261,364.70 rows=2,271 width=20) (actual time=1,029.282..1,029.282 rows=282,983 loops=1)

  • Buckets: 524,288 (originally 4096) Batches: 1 (originally 1) Memory Usage: 18,467kB
7. 13.797 998.532 ↓ 124.6 282,983 1

Subquery Scan on c (cost=261,200.05..261,364.70 rows=2,271 width=20) (actual time=792.676..998.532 rows=282,983 loops=1)

8. 183.188 984.735 ↓ 124.6 282,983 1

WindowAgg (cost=261,200.05..261,341.99 rows=2,271 width=28) (actual time=792.675..984.735 rows=282,983 loops=1)

9. 51.723 801.547 ↓ 124.6 282,983 1

Sort (cost=261,200.05..261,205.73 rows=2,271 width=44) (actual time=792.663..801.547 rows=282,983 loops=1)

  • Sort Key: f.ownerid, f.zoneid
  • Sort Method: quicksort Memory: 34,397kB
10. 18.244 749.824 ↓ 124.6 282,983 1

Subquery Scan on f (cost=227,001.10..261,073.45 rows=2,271 width=44) (actual time=509.357..749.824 rows=282,983 loops=1)

  • Filter: (f.ownerid = f.to_owner)
11. 212.692 731.580 ↑ 1.6 282,983 1

WindowAgg (cost=227,001.10..255,394.73 rows=454,298 width=48) (actual time=509.355..731.580 rows=282,983 loops=1)

12. 58.762 518.888 ↑ 1.6 282,983 1

Sort (cost=227,001.10..228,136.85 rows=454,298 width=56) (actual time=509.342..518.888 rows=282,983 loops=1)

  • Sort Key: v.to_owner, v.zoneid, v.attraction DESC
  • Sort Method: quicksort Memory: 52,083kB
13. 22.071 460.126 ↑ 1.6 282,983 1

Subquery Scan on v (cost=147,514.24..184,312.36 rows=454,298 width=56) (actual time=261.358..460.126 rows=282,983 loops=1)

  • Filter: ((v.r <= '10'::double precision) OR (v.nd_attraction > '1e-05'::double precision))
  • Rows Removed by Filter: 29,881
14. 148.543 438.055 ↑ 2.6 312,864 1

WindowAgg (cost=147,514.24..172,046.32 rows=817,736 width=64) (actual time=261.356..438.055 rows=312,864 loops=1)

15. 194.689 289.512 ↑ 2.6 312,864 1

Sort (cost=147,514.24..149,558.58 rows=817,736 width=56) (actual time=261.343..289.512 rows=312,864 loops=1)

  • Sort Key: rtsepebrbrav.br2_id, ca.zo_id, ca.attraction DESC
  • Sort Method: quicksort Memory: 56,285kB
16. 52.156 94.823 ↑ 2.6 312,864 1

Hash Join (cost=380.35..67,207.35 rows=817,736 width=56) (actual time=1.953..94.823 rows=312,864 loops=1)

  • Hash Cond: (ca.co_id = co.lo_id)
17. 40.757 40.757 ↑ 2.6 312,864 1

Index Scan using calc_attractions_3608_4_zo_idx on calc_attractions_3608_4 ca (cost=0.43..55,583.56 rows=817,736 width=40) (actual time=0.033..40.757 rows=312,864 loops=1)

  • Index Cond: (zo_id = ANY ('{627213,627214,627215,627216,627217,627218,627219,627220,627221,627222,627223,627224,627225,627226,627227,627228,627229,627230,627231,627232,627233,627234,627235,627236,627237,627238,627239,627240,627241,627242,627243,627244,627245,627246,627247,627248,627249,627250,627251,627252,627253,627254,627255,627256,627257,627258,627259,627260,627261,627262,627263,627264,627265,627266,627267,627268,627269,627270,627271,627272,627273,627274,627275,627276,627277,627278,627279,627280,627281,627282,627283,627284,627285,627286,627287,627288,627289,627290,627292,627293,627294,627295,627296,627297,627298,627299,627300,627301,627302,627303,627304,627305,627306,627307,627308,627309,627310,627311,627312,627313,627314,627315,627316,627317,627318,627319,627320,627321,627322,627323,627324,627325,627326,627327,627328,627329,627330,627331,627332,627333,627334,627335,627336,627337,627338,627339,627340,627341,627342,627343,627344,627345,627346,627347,627348,627349,627350,627351,627352,627353,627354,627355,627356,627357,627358,627359,627360,627361,627362,627363,627364,627365,627366,627367,627368,627369,627370,627371,627372,627373,627374,627375,627376,627377,627378,627379,627380,627381,627382,627383,627384,627385,627386,627387,627388,627389,627390,627391,627392,627393,627394,627395,627396,627397,627398,627399,627400,627401,627402,627403,627404,627405,627406,627407,627408,627409,627410,627411,627412,627413,627414,627415,627416,627417,627418,627419,627420,627421,627422,627423,627424,627425,627427,627428,627429,627430,627431,627432,627433,627434,627435,627436,627437,627438,627439,627440,627441,627442,627443,627444,627445,627446,627447,627448,627449,627450,627451,627452,627453,627454,627455,627456,627457,627458,627459,627460,627461,627462,627463,627464,627465,627466,627467,627468,627469,627470,627471,627472,627473,627474,627475,627476,627477,627478,627479,627480,627481,627482,627483,627484,627485,627486,627487,627488,627489,627490,627491,627492,627493,627494,627495,627496,627497,627498,627499,627500,627501,627502,627503,627504,627505,627506,627507,627508,627509,627510,627511,627512,627513,627514,627515,627516,627517,627518,627519,627520,627521,627522,627523,627524,627525,627526,627527,627528,627529,627530,627531,627532,627533,627534,627535,627536,627537,627538,627539,627540,627541,627542,627543,627544,627545,627546,627547,627548,627549,627550,627551,627552,627553,627554,627555,627556,627557,627558,627559,627560,627561,627562,627563,627564,627565,627566,627567,627568,627569,627570,627571,627572,627573,627574,627575,627576,627577,627578,627579,627580,627581,627582,627583,627584,627585,627586,627587,627588,627589,627590,627591,627592,627593,627594,627595,627596,627597,627598,627599,627600,627601,627602,627603,627604,627605,627606,627607,627608,627609,627610,627611,627612,627613,627614,627615,627616,627617,627618,627619,627620,627621,627622,627623,627624,627625,627626,627627,627628,627629,627630,627631,627632,627633,627634,627635,627636,627637,627638,627639,627640,627641,627642,627643,627644,627645,627646,627647,627648,627649,627650,627651,627652,627653,627654,627655,627656,627657,627658,627659,627660,627661,627662,627663,627664,627665,627666,627667,627668,627669,627670,627671,627672,627673,627674,627675,627676,627677,627678,627679,627680,627681,627682,627683,627684,627685,627686,627687,627688,627689,627690,627691,627692,627693,627694,627695,627696,627697,627698,627699,627700,627701,627702,627703,627704,627705,627706,627707,627708,627709,627710,627711,627712,627713,627714,627715,627716,627717,627718,627719,627720,627721,627722,627723,627724,627725,627726,627727,627728,627729,627730,627731,627732,627733,627734,627735,627736,627737,627738,627739,627740,627741,627742,627743,627744,627745,627746,627747,627748,627749,627750,627751,627752,627753,627754,627755,627756,627757,627758,627759,627760,627761,627762,627763,627764,627765,627766,627767,627768,627769,627770,627771,627772,627773,627774,627775,627776,627777,627778,627779,627780,627781,627782,627783,627784,627785,627786,627787,627788,627789,627790,627791,627792,627793,627794,627795,627796,627797,627798,627799,627800,627801,627802,627803,627804,627805,627806,627807,627808,627809,627810,627811,627812,627813,627814,627815,627816,627817,627818,627819,627820,627821,627822,627823,627824,627825,627826,627827,627828,627829,627830,627831,627832,627833,627834,627835,627836,627837,627838,627839,627840,627841,627842,627843,627844,627845,627846,627847,627848,627849,627850,627851,627852,627853,627854,627855,627856,627857,627858,627859,627860,627861,627862,627863,627864,627865,627866,627867,627868,627869,627870,627871,627872,627873,627874,627875,627876,627877,627878,627879,627880,627881,627882,627883,627884,627885,627886,627887,627888,627889,627890,627891,627892,627893,627894,627895,627896,627897,627898,627899,627900,627901,627902,627903,627904,627905,627906,627907,627908,627909,627910,627911,627912,627913,627914,627915,627916,627917,627918,627919,627920,627921,627922,627923,627924,627925,627926,627927,627928,627929,627930,627931,627932,627933,627934,627935,627936,627937,627938,627939,627940,627941,627942,627943,627944,627945,627946,627947,627948,627949,627950,627951,627952,627953,627954,627955,627956,627957,627958,627959,627960,627961,627962,627963,627964,627965,627966,627967,627968,627969,627970,627971,627972,627973,627974,627975,627976,627977,627978,627979,627980,627981,627982,627983,627984,627985,627986,627987,627988,627989,627990,627991,627992,627993,627994,627995,627996,627997,627998,627999,628000,628001,628002,628003,628004,628005,628006,628007,628008,628009,628010,628011,628012,628013,628014,628015,628016,628017,628018,628019,628020,628021,628022,628023,628024,628025,628026,628027,628028,628029,628030,628031,628032,628033,628034,628035,628036,628037,628038,628039,628040,628041,628042,628043,628044,628045,628046,628047,628048,628049,628050,628051,628052,628053,628054,628055,628056,628057,628058,628059,628060,628061,628062,628063,628064,628065,628066,628067,628068,628069,628070,628071,628072,628073,628074,628075,628076,628077,628078,628079,628080,628081,628082,628083,628084,628085,628086,628087,628088,628089,628090,628091,628092,628093,628094,628095,628096,628097,628098,628099,628100,628101,628102,628103,628104,628105,628106,628107,628108,628109,628110,628111,628112,628113,628114,628115,628116,628117,628118,628119,628120,628121,628122,628123,628124,628125,628126,628127,628128,628129,628130,628131,628132,628133,628134,628135,628136,628137,628138,628139,628140,628141,628142,628143,628144,628145,628146,628147,628148,628149,628150,628151,628152,628153,628154,628155,628156,628157,628158,628159,628160,628161,628162,628163,628164,628165,628166,628167,628168,628169,628170,628171,628172,628173,628174,628175,628176,628177,628178,628179,628180,628181,628182,628183,628184,628185,628186,628187,628188,628189,628190,628191,628192,628193,628194,628195,628196,628197,628198,628199,628200,628201,628202,628203,628204,628205,628206,628207,628208,628209,628210,628211,628212,628213,628214,628215,628216,628217,628218,628219,628220,628221,628222,628223,628224,628225,628226,628227,628228,628229,628230,628231,628232,628233,628234,628235,628236,628237,628238,628240,628241,628242,628243,628244,628245,628246,628247,628248,628249,628250,628251,628252,628253,628254,628255,628256,628257,628258,628259,628260,628261,628262,628263,628264,628265,628266,628267,628268,628269,628270,628271,628272,628273,628274,628275,628276,628277,628278,628279,628280,628281,628282,628283,628284,628285,628286,628287,628288,628289,628290,628291,628292,628293,628294,628295,628296,628297,628298,628299,628300,628301,628302,628303,628304,628305,628306,628307,628308,628309,628310,628311,628312,628313,628314,628315,628316,628317,628318,628319,628320,628321,628322,628323,628324,628325,628326,628327,628328,628329,628330,628331,628332,628333,628334,628335,628336,628337,628338,628339,628340,628341,628342,628343,628344,628345,628346,628347,628348,628349,628350,628351,628352,628353,628354,628355,628356,628357,628358,628359,628360,628361,628362,628363,628364,628365,628366,628367,628368,628369,628370,628371,628372,628373,628374,628375,628376,628377,628378,628379,628380,628381,628382,628383,628384,628385,628386,628387,628388,628389,628390,628391,628392,628393,628394,628395,628396,628397,628398,628399,628400,628401,628402,628403,628404,628405,628406,628407,628408,628409,628410,628411,628412,628413,628414,628415,628416,628417,628418,628419,628420,628421,628422,628423,628424,628425,628426,628427,628428,628429,628430,628431,628432,628433,628434,628435,628436,628437,628438,628439,628440,628441,628442,628443,628444,628445,628446,628447,628448,628449,628450,628451,628452,628453,628454,628455,628456,628457,628458,628459,628460,628461,628462,628463,628464,628465,628466,628467,628468,628469,628470,628471,628472,628473,628474,628475,628476,628477,628478,628479,628480,628481,628482,628483,628484,628485,628486,628487,628488,628489,628490,628491,628492,628493,628494,628495,628496,628497,628498,628499,628500,628501,628502,628503,628504,628505,628506,628507,628508,628509,628510,628511,628512,628513,628514,628515,628516,628517,628518,628519,628520,628521,628522,628523,628524,628525,628526,628527,628528,628529,628530,628531,628532,628533,628534,628535,628536,628537,628538,628539,628540,628541,628542,628543,628544,628545,628546,628547,628548,628549,628550,628551,628552,628553,628554,628555,628556,628557,628558,628559,628560,628561,628562,628563,628564,628565,628566,628567,628568,628569,628570,628571,628572,628573,628574,628575,628576,628577,628578,628579,628580,628581,628582,628583,628584,628585,628586,628587,628588,628589,628590,628591,628592,628593,628594,628595,628596,628597,628598,628599,628600,628601,628602,628603,628604,628605,628606,628607,628608,628609,628610,628611,628612,628613,628614,628615,628616,628617,628618,628619,628620,628621,628622,628623,628624,628625,628626,628627,628628,628629,628630,628631,628632,628633,628634,628635,628636,628637,628638,628639,628640,628641,628642,628643,628644,628645,628646,628647,628648,628649,628650,628651,628652,628653,628654,628655,628656,628657,628658,628659,628660,628661,628662,628663,628664,628665,628666,628667,628668,628669,628670,628671,628672,628673,628674,628675,628676,628677,628678,628679,628680,628681,628682,628683,628684,628685,628686,628687,628688,628689,628690,628691,628692,628693,628694,628695,628696,628697,628698,628699,628700,628701,628702,628703,628704,628705,628706,628707,628708,628709,628710,628711,628712,628713,628714,628715,628716,628717,628718,628719,628720,628721,628722,628723,628724,628725,628726,628727,628728,628729,628730,628731,628732,628733,628734,628735,628736,628737,628738,628739,628740,628741,628742,628743,628744,628745,628746,628747,628748,628749,628750,628751,628752,628753,628754,628755,628756,628757,628758,628759,628760,628761,628762,628763,628764,628765,628766,628767,628768,628769,628770,628771,628772,628773,628774,628775,628776,628777,628778,628779,628780,628781,628782,628783,628784,628785,628786,628787,628788,628790,628791,628792,628793,628794,628795,628796,628797,628798,628799,628800,628801,628802,628803,628804,628805,628806,628807,628808,628809,628810,628811,628812,628813,628814,628815,628816,628817,628818,628819,628820,628821,628822,628823,628824,628825,628826,628827,628828,628829,628830,628831,628832,628833,628834,628835,628836,628837,628838,628839,628840,628841,628842,628843,628844,628845,628846,628847,628848,628849,628850,628851,628852,628853,628854,628855,628856,628857,628858,628859,628860,628861,628862,628863,628864,628865,628866,628867,628868,628869,628870,628871,628872,628873,628874,628875,628876,628877,628878,628879,628880,628881,628882,628883,628884,628885,628886,628887,628888,628889,628890,628891,628892,628893,628894,628895,628896,628897,628898,628899,628900,628901,628902,628903,628904,628905,628906,628907,628908,628909,628910,628911,628912,628913,628914,628915,628916,628917,628918,628919,628920,628921,628922,628923,628924,628925,628926,628927,628928,628929,628930,628931,628932,628933,628934,628935,628936,628937,628938,628939,628940,628941,628942,628943,628944,628945,628946,628947,628948,628949,628950,628951,628952,628953,628954,628955,628956,628957,628958,628959,628960,628961,628962,628963,628964,628965,628966,628967,628968,628969,628970,628971,628972,628973,628974,628975,628976,628977,628978,628979,628980,628981,628982,628983,628984,628985,628986,628987,628988,628989,628990,628991,628992,628993,628994,628995,628996,628997,628998,628999,629000,629001,629002,629003,629004,629005,629006,629007,629008,629009,629010,629011,629012,629013,629014,629015,629016,629017,629018,629019,629020,629021,629022,629023,629024,629025,629026,629027,629028,629029,629030,629031,629032,629033,629034,629035,629036,629037}'::integer[]))
18. 0.578 1.910 ↑ 1.0 7,196 1

Hash (cost=289.97..289.97 rows=7,196 width=20) (actual time=1.910..1.910 rows=7,196 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 458kB
19. 0.873 1.332 ↑ 1.0 7,196 1

Hash Join (cost=74.06..289.97 rows=7,196 width=20) (actual time=0.116..1.332 rows=7,196 loops=1)

  • Hash Cond: (co.br_id = rtsepebrbrav.br_id)
20. 0.353 0.353 ↑ 1.0 7,196 1

Seq Scan on rtco co (cost=0.00..116.96 rows=7,196 width=8) (actual time=0.006..0.353 rows=7,196 loops=1)

21. 0.013 0.106 ↑ 1.0 153 1

Hash (cost=72.14..72.14 rows=154 width=16) (actual time=0.106..0.106 rows=153 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
22. 0.006 0.093 ↑ 1.0 153 1

Append (cost=0.00..72.14 rows=154 width=16) (actual time=0.052..0.093 rows=153 loops=1)

23. 0.070 0.070 ↑ 1.0 153 1

Seq Scan on rtsepebrbrav (cost=0.00..18.03 rows=153 width=16) (actual time=0.052..0.070 rows=153 loops=1)

  • Filter: ((se_id = 3,608) AND (pe_id = 4) AND (ad_id = 16))
  • Rows Removed by Filter: 306
24. 0.001 0.017 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=29.64..53.34 rows=1 width=16) (actual time=0.017..0.017 rows=0 loops=1)

25. 0.000 0.016 ↓ 0.0 0 1

Hash Anti Join (cost=29.64..53.33 rows=1 width=64) (actual time=0.016..0.016 rows=0 loops=1)

  • Hash Cond: ((rtpe.id = av_1.pe_id) AND (av.se_id = av_1.se_id) AND (av.ad_id = av_1.ad_id) AND (av.br_id = av_1.br_id) AND (av.br2_id = av_1.br2_id))
26. 0.002 0.016 ↓ 0.0 0 1

Hash Join (cost=8.17..27.73 rows=132 width=28) (actual time=0.016..0.016 rows=0 loops=1)

  • Hash Cond: (av.pe_id = rtpe.baseline_id)
27. 0.008 0.008 ↑ 459.0 1 1

Seq Scan on rtsepebrbrav av (cost=0.00..16.88 rows=459 width=28) (actual time=0.008..0.008 rows=1 loops=1)

  • Filter: ((se_id = 3,608) AND (ad_id = 16))
28. 0.001 0.006 ↓ 0.0 0 1

Hash (cost=8.15..8.15 rows=1 width=8) (actual time=0.005..0.006 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
29. 0.005 0.005 ↑ 1.0 1 1

Index Only Scan using rtpe_id_baseline_id_idx on rtpe (cost=0.14..8.15 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (id = 4)
  • Heap Fetches: 1
30. 0.000 0.000 ↓ 0.0 0

Hash (cost=18.03..18.03 rows=153 width=20) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Seq Scan on rtsepebrbrav av_1 (cost=0.00..18.03 rows=153 width=20) (never executed)

  • Filter: ((pe_id = 4) AND (se_id = 3,608) AND (ad_id = 16))
Planning time : 8.397 ms
Execution time : 1,315.461 ms