explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dLfL

Settings
# exclusive inclusive rows x rows loops node
1. 9.289 333.611 ↓ 0.0 0 1

Insert on user_tags (cost=1,302.11..1,743.16 rows=2,012 width=106) (actual time=333.605..333.611 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: 250
  • Conflicting Tuples: 0
2.          

CTE user_available_tags

3. 12.229 49.397 ↑ 1.0 50 1

GroupAggregate (cost=0.72..706.02 rows=50 width=20) (actual time=1.313..49.397 rows=50 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. 22.694 37.168 ↑ 1.0 1,550 1

Nested Loop Left Join (cost=0.72..685.88 rows=1,552 width=24) (actual time=0.152..37.168 rows=1,550 loops=1)

5. 0.624 0.624 ↑ 1.0 50 1

Index Scan using users_pkey on users u (cost=0.29..203.88 rows=50 width=12) (actual time=0.021..0.624 rows=50 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}'::text[]))
  • Filter: (((config -> 'newsfeed'::text) ->> 'mode'::text) = 'automated'::text)
6. 13.850 13.850 ↑ 1.0 31 50

Index Scan using user_tags_user_id_index on user_tags ut (cost=0.43..9.33 rows=31 width=24) (actual time=0.021..0.277 rows=31 loops=50)

  • 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. 3.638 324.322 ↑ 8.0 250 1

Hash Join (cost=596.08..1,037.13 rows=2,012 width=106) (actual time=187.147..324.322 rows=250 loops=1)

  • Hash Cond: ((unnest(urt.recommended_tags[1:uit.available_tags])) = t.id)
8. 65.863 184.313 ↑ 20.0 250 1

Hash Join (cost=1.62..323.62 rows=5,000 width=20) (actual time=50.681..184.313 rows=250 loops=1)

  • Hash Cond: ((urt.user_id)::text = (uit.user_id)::text)
9. 67.827 67.827 ↑ 1.0 10,000 1

Seq Scan on user_recommended_tags urt (cost=0.00..259.00 rows=10,000 width=69) (actual time=0.015..67.827 rows=10,000 loops=1)

  • Filter: ((brand_id)::text = 'AFR'::text)
10. 0.390 50.623 ↑ 1.0 50 1

Hash (cost=1.00..1.00 rows=50 width=524) (actual time=50.617..50.623 rows=50 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
11. 50.233 50.233 ↑ 1.0 50 1

CTE Scan on user_available_tags uit (cost=0.00..1.00 rows=50 width=524) (actual time=1.330..50.233 rows=50 loops=1)

12. 67.715 136.371 ↑ 1.0 10,159 1

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

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

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

  • Filter: (visible AND (NOT archived))
  • Rows Removed by Filter: 15,088
Planning time : 1.435 ms
Execution time : 348.600 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint user_tags_tag_id_fkey 5.649 ms 250 0.023 ms
for constraint user_tags_user_id_fkey 5.664 ms 250 0.023 ms