explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bbhG

Settings
# exclusive inclusive rows x rows loops node
1. 1,239.060 5,263.774 ↓ 0.0 0 1

Delete on outfit_visit_history ovh (cost=203,211.12..4,844,854.37 rows=212,502 width=34) (actual time=5,263.774..5,263.774 rows=0 loops=1)

2.          

CTE visits

3. 35.755 873.248 ↑ 103.7 50 1

Subquery Scan on temp (cost=169,375.56..203,093.98 rows=5,187 width=20) (actual time=586.012..873.248 rows=50 loops=1)

  • Filter: (temp.r = 200)
  • Rows Removed by Filter: 1,029,220
4. 188.237 837.493 ↑ 1.0 1,029,270 1

WindowAgg (cost=169,375.56..190,125.36 rows=1,037,490 width=20) (actual time=585.960..837.493 rows=1,029,270 loops=1)

5. 469.604 649.256 ↑ 1.0 1,029,270 1

Sort (cost=169,375.56..171,969.28 rows=1,037,490 width=12) (actual time=585.956..649.256 rows=1,029,270 loops=1)

  • Sort Key: outfit_visit_history.id_user_app, outfit_visit_history.visited_at DESC
  • Sort Method: external sort Disk: 26,192kB
6. 179.652 179.652 ↑ 1.0 1,029,270 1

Index Scan using outfit_visit_history_id_outfit_id_user_app_uc on outfit_visit_history (cost=0.43..47,975.10 rows=1,037,490 width=12) (actual time=0.152..179.652 rows=1,029,270 loops=1)

  • Index Cond: (id_user_app = ANY ('{43094,43139,40954,39154,30458,23,33171,45787,33539,33831,25,26016,26172,7,31468,35312,45912,31532,31582,40840,31678,35579,43034,38586,36713,48828,42324,48097,40322,36761,41099,45271,39,44301,27,36486,32880,35871,39955,29667,27592,43355,30748,24,14,41474,44718,38091,34983,44405,45585,49773,49089,27784,30529,49070,47909,36115,31565,41633,34652,44861,47,32498,27101,29735,32564,36495,45940,45,40474,48398,41013,43646,29499,41950,42674,45283,43763,45724,49839,45214,41142,21,44397,49658,41836,47693,36185,36971,46379,37649,29342,34832,46826,45871,37,42396,40020,34937,46582,47359,29610,47902,34889,40558,45555,44088,47719,46,32745,37503,47797,48394,25206,36081,27223,26322,40632,40580,31908,31978,37805,44915,41921,44885,39118,8,48,44122,27026,31,20,26044,33419,42018,35567,47557,26111,49056,35869,42611,44083,48867,26944,32244,33177,26267,38388,45433,29,49202,39503,47107,40567,33793,10,25217,31093,29455,26435,48037,48018,32465,38,46665,26904,27093,9,26776,30935,29801,31252,33672,35540,30816,27721,48010,32610,27810,31669,33909,38223,32937,43409,48331,31474,28120,42213,42269,43551,37948,40091,26251,49303,39917,29581,45863,36,26705,45060,40787,27463,12,38481,31621,47213,29908,45226,32,32900,27730,49552,26795,26565,45306,42419,26259,36016,1,47793,28015,28986,43632,43840,35085,41308,41173,47593,30199,30387,30739,38989,49129,27981,36049,32584,49152,42239,18,41,39207,32083,38840,43604,33,38115,43975,48825,36392,44347,49995,38199,37389,47475,43200,49810,25843,41695,47448,34942,35107,46944,47591,48110,42680,47005,34072,34965,13,37403,34047,43123,34882,40785,32840,46325,34510,36021,32153,45088,37577,31449,27513,31276,35159,25474,47363,28716,34783,33544,30966,45341,40674,32906,48431,37166,49,48490,42764,34042,45024,49100,40448,30854,28393,48078,15,17,48995,25063,43008,37220,40,29868,48748,25816,35,43629,44913,47330,30546,44,26,19,35950,49178,29775,45914,27298,44818,28530,33554,33973,33463,37783,36979,26070,36349,30181,35773,43722,41971,40246,34628,38851,41887,40971,26366,43279,46470,40015,31668,38959,38397,37142,41675,28,4,41183,37327,26688,5,27426,25249,28468,28775,32784,38008,38441,48807,30114,33490,31705,40176,30,26673,48719,39570,33523,41455,16,32854,25169,26454,40945,25881,42899,31870,34206,49945,33344,40384,39463,45386,31603,30375,35485,49666,47188,49529,42,36037,39062,41378,48678,26587,31281,22,32416,28265,25411,26461,32011,34,27803,48873,25774,28237,48913,40253,47063,41094,33137,36818,28077,26873,38889,35308,39259,3,27520,47333,26275,35331,39913,43174,33378,45133,46770,43,35711,39665,31517,31352,26328,29764,28365,30549,38150,32199,41822,31754,44740,28552,29184,39688,11,31256,6,43449,32510,27977,49059,32401,37052,50,43007,35045,39349,31511,27706,34745,35474,28001,28592,2}'::integer[]))
7. 71.353 4,024.714 ↓ 4.6 975,000 1

Nested Loop (cost=117.14..4,641,760.39 rows=212,502 width=34) (actual time=875.915..4,024.714 rows=975,000 loops=1)

8. 0.167 873.511 ↑ 4.0 50 1

HashAggregate (cost=116.71..118.71 rows=200 width=32) (actual time=873.421..873.511 rows=50 loops=1)

  • Group Key: visits.id_user_app
9. 873.344 873.344 ↑ 103.7 50 1

CTE Scan on visits (cost=0.00..103.74 rows=5,187 width=32) (actual time=586.023..873.344 rows=50 loops=1)

10. 4.850 3,079.850 ↓ 295.5 19,500 50

Index Scan using outfit_visit_history_id_outfit_id_user_app_uc on outfit_visit_history ovh (cost=0.43..23,207.55 rows=66 width=10) (actual time=2.722..61.597 rows=19,500 loops=50)

  • Index Cond: (id_user_app = visits.id_user_app)
  • Filter: (visited_at < (SubPlan 2))
  • Rows Removed by Filter: 1,000
11.          

SubPlan (for Index Scan)

12. 3,075.000 3,075.000 ↑ 26.0 1 1,025,000

CTE Scan on visits visits_1 (cost=0.00..116.71 rows=26 width=8) (actual time=0.001..0.003 rows=1 loops=1,025,000)

  • Filter: (id_user_app = ovh.id_user_app)
  • Rows Removed by Filter: 49