explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vgag : few

Settings
# exclusive inclusive rows x rows loops node
1. 9.129 337.269 ↓ 0.0 0 1

Insert on user_tags (cost=1,624.11..1,793.16 rows=2,012 width=824) (actual time=337.263..337.269 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.621 49.717 ↑ 1.0 50 1

GroupAggregate (cost=0.72..706.02 rows=50 width=20) (actual time=1.322..49.717 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.670 37.096 ↑ 1.0 1,550 1

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

5. 0.776 0.776 ↑ 1.0 50 1

Index Scan using users_pkey on users u (cost=0.29..203.88 rows=50 width=12) (actual time=0.026..0.776 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.650 13.650 ↑ 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.020..0.273 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.          

CTE most_read_tags

8. 65.872 184.530 ↑ 20.0 250 1

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

  • Hash Cond: ((urt.user_id)::text = (uit.user_id)::text)
9. 67.838 67.838 ↑ 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.018..67.838 rows=10,000 loops=1)

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

Hash (cost=1.00..1.00 rows=50 width=524) (actual time=50.815..50.820 rows=50 loops=1)

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

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

12. 3.767 328.140 ↑ 8.0 250 1

Hash Join (cost=594.46..763.51 rows=2,012 width=824) (actual time=187.648..328.140 rows=250 loops=1)

  • Hash Cond: (mrt.tag_id = t.id)
13. 187.745 187.745 ↑ 20.0 250 1

CTE Scan on most_read_tags mrt (cost=0.00..100.00 rows=5,000 width=738) (actual time=50.916..187.745 rows=250 loops=1)

14. 67.268 136.628 ↑ 1.0 10,159 1

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

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

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

  • Filter: (visible AND (NOT archived))
  • Rows Removed by Filter: 15,088
Planning time : 1.547 ms
Execution time : 352.492 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint user_tags_tag_id_fkey 5.557 ms 250 0.022 ms
for constraint user_tags_user_id_fkey 5.982 ms 250 0.024 ms