explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gOrk

Settings
# exclusive inclusive rows x rows loops node
1. 18.600 274.566 ↓ 0.0 0 1

Insert on user_tags (cost=2,771.53..3,109.63 rows=4,024 width=824) (actual time=274.561..274.566 rows=0 loops=1)

  • Conflict Resolution: UPDATE
  • Conflict Arbiter Indexes: brand_id_user_id_tag_id_unique_check, user_tags_user_id_brand_id_tag_id_index
  • Tuples Inserted: 500
  • Conflicting Tuples: 0
2.          

CTE user_available_tags

3. 21.501 87.184 ↑ 1.0 100 1

GroupAggregate (cost=0.72..1,381.08 rows=100 width=20) (actual time=1.279..87.184 rows=100 loops=1)

  • Group Key: u.user_id
  • Filter: ((count(ut.tag_id) < 50) AND (sum(CASE WHEN (ut.recommended_ts IS NOT NULL) THEN 1 ELSE 0 END) < 20))
4. 40.216 65.683 ↑ 1.0 3,100 1

Nested Loop Left Join (cost=0.72..1,340.75 rows=3,106 width=24) (actual time=0.101..65.683 rows=3,100 loops=1)

5. 1.367 1.367 ↑ 1.0 100 1

Index Scan using users_pkey on users u (cost=0.29..376.75 rows=100 width=12) (actual time=0.027..1.367 rows=100 loops=1)

  • Index Cond: ((user_id)::text = ANY ('{user_100000,user_100001,user_100002,user_100003,user_100004,user_100005,user_100006,user_100007,user_100008,user_100009,user_100010,user_100011,user_100012,user_100013,user_100014,user_100015,user_100016,user_100017,user_100018,user_100019,user_100020,user_100021,user_100022,user_100023,user_100024,user_100025,user_100026,user_100027,user_100028,user_100029,user_100030,user_100031,user_100032,user_100033,user_100034,user_100035,user_100036,user_100037,user_100038,user_100039,user_100040,user_100041,user_100042,user_100043,user_100044,user_100045,user_100046,user_100047,user_100048,user_100049,user_100050,user_100051,user_100052,user_100053,user_100054,user_100055,user_100056,user_100057,user_100058,user_100059,user_100060,user_100061,user_100062,user_100063,user_100064,user_100065,user_100066,user_100067,user_100068,user_100069,user_100070,user_100071,user_100072,user_100073,user_100074,user_100075,user_100076,user_100077,user_100078,user_100079,user_100080,user_100081,user_100082,user_100083,user_100084,user_100085,user_100086,user_100087,user_100088,user_100089,user_100090,user_100091,user_100092,user_100093,user_100094,user_100095,user_100096,user_100097,user_100098,user_100099}'::text[]))
  • Filter: (((config -> 'newsfeed'::text) ->> 'mode'::text) = 'automated'::text)
6. 24.100 24.100 ↑ 1.0 31 100

Index Scan using user_tags_user_id_index on user_tags ut (cost=0.43..9.33 rows=31 width=24) (actual time=0.018..0.241 rows=31 loops=100)

  • Index Cond: ((u.user_id)::text = (user_id)::text)
  • Filter: (((brand_id)::text = 'AFR'::text) AND ((tag_followed)::text = 'followed'::text))
  • Rows Removed by Filter: 9
7.          

CTE most_read_tags

8. 5.498 95.624 ↑ 20.0 500 1

Nested Loop (cost=0.29..796.00 rows=10,000 width=20) (actual time=1.453..95.624 rows=500 loops=1)

9. 88.426 88.426 ↑ 1.0 100 1

CTE Scan on user_available_tags uit (cost=0.00..2.00 rows=100 width=524) (actual time=1.295..88.426 rows=100 loops=1)

10. 1.700 1.700 ↑ 1.0 1 100

Index Scan using user_recommended_tags_user_id_index on user_recommended_tags urt (cost=0.29..7.43 rows=1 width=69) (actual time=0.011..0.017 rows=1 loops=100)

  • Index Cond: ((user_id)::text = (uit.user_id)::text)
  • Filter: ((brand_id)::text = 'AFR'::text)
11. 7.006 255.966 ↑ 8.0 500 1

Hash Join (cost=594.46..932.56 rows=4,024 width=824) (actual time=147.958..255.966 rows=500 loops=1)

  • Hash Cond: (mrt.tag_id = t.id)
12. 102.582 102.582 ↑ 20.0 500 1

CTE Scan on most_read_tags mrt (cost=0.00..200.00 rows=10,000 width=738) (actual time=1.471..102.582 rows=500 loops=1)

13. 71.948 146.378 ↑ 1.0 10,159 1

Hash (cost=467.47..467.47 rows=10,159 width=4) (actual time=146.372..146.378 rows=10,159 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 486kB
14. 74.430 74.430 ↑ 1.0 10,159 1

Seq Scan on tag t (cost=0.00..467.47 rows=10,159 width=4) (actual time=0.023..74.430 rows=10,159 loops=1)

  • Filter: (visible AND (NOT archived))
  • Rows Removed by Filter: 15,088
Planning time : 1.786 ms
Execution time : 303.280 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint user_tags_tag_id_fkey 10.167 ms 500 0.020 ms
for constraint user_tags_user_id_fkey 11.398 ms 500 0.023 ms