explain.depesz.com

PostgreSQL's explain analyze made readable

Result: b16B

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 383.811 ↑ 1.0 50 1

Limit (cost=3,286.47..3,286.60 rows=50 width=254) (actual time=383.791..383.811 rows=50 loops=1)

2.          

CTE tags

3. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

4. 1.948 383.797 ↑ 1.2 50 1

Sort (cost=3,286.46..3,286.61 rows=58 width=254) (actual time=383.790..383.797 rows=50 loops=1)

  • Sort Key: p.popularity DESC
  • Sort Method: top-N heapsort Memory: 84kB
5. 3.555 381.849 ↓ 25.7 1,488 1

Nested Loop Left Join (cost=283.27..3,284.76 rows=58 width=254) (actual time=2.487..381.849 rows=1,488 loops=1)

6. 0.973 372.342 ↓ 25.7 1,488 1

Nested Loop Left Join (cost=282.99..3,261.83 rows=58 width=209) (actual time=2.474..372.342 rows=1,488 loops=1)

7. 2.361 46.985 ↓ 25.7 1,488 1

Nested Loop (cost=263.32..1,397.36 rows=58 width=177) (actual time=2.253..46.985 rows=1,488 loops=1)

8. 1.898 23.516 ↓ 30.3 1,759 1

Nested Loop (cost=263.03..913.35 rows=58 width=145) (actual time=2.224..23.516 rows=1,759 loops=1)

  • Join Filter: (st.station_id = station.station_id)
9. 4.849 9.277 ↓ 15.1 1,763 1

Hash Join (cost=261.12..672.67 rows=117 width=16) (actual time=2.187..9.277 rows=1,763 loops=1)

  • Hash Cond: (p.station_id = st.station_id)
10. 2.256 2.256 ↑ 1.0 20,558 1

Seq Scan on popularity p (cost=0.00..357.58 rows=20,558 width=12) (actual time=0.005..2.256 rows=20,558 loops=1)

11. 0.241 2.172 ↓ 15.1 1,763 1

Hash (cost=259.65..259.65 rows=117 width=4) (actual time=2.172..2.172 rows=1,763 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 78kB
12. 0.882 1.931 ↓ 15.1 1,763 1

HashAggregate (cost=257.31..258.48 rows=117 width=4) (actual time=1.663..1.931 rows=1,763 loops=1)

  • Group Key: st.station_id
  • Filter: (count(*) = $2)
13.          

Initplan (for HashAggregate)

14. 0.001 0.002 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)

15. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on tags tags_1 (cost=0.00..0.02 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

16. 0.174 1.047 ↓ 15.1 1,763 1

Nested Loop (cost=5.22..256.70 rows=117 width=4) (actual time=0.180..1.047 rows=1,763 loops=1)

17. 0.004 0.007 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=1)

  • Group Key: tags.column1
18. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on tags (cost=0.00..0.02 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1)

19. 0.737 0.866 ↓ 15.1 1,763 1

Bitmap Heap Scan on station_tag st (cost=5.20..255.49 rows=117 width=8) (actual time=0.172..0.866 rows=1,763 loops=1)

  • Recheck Cond: (tag_id = tags.column1)
  • Heap Blocks: exact=370
20. 0.129 0.129 ↓ 15.1 1,763 1

Bitmap Index Scan on idx_tag_overrides_tag_id (cost=0.00..5.17 rows=117 width=0) (actual time=0.129..0.129 rows=1,763 loops=1)

  • Index Cond: (tag_id = tags.column1)
21. 12.335 12.341 ↑ 1.0 1 1,763

Index Scan using station_new_pkey on station (cost=1.91..2.04 rows=1 width=137) (actual time=0.007..0.007 rows=1 loops=1,763)

  • Index Cond: (station_id = p.station_id)
  • Filter: (NOT (hashed SubPlan 2))
  • Rows Removed by Filter: 0
22.          

SubPlan (for Index Scan)

23. 0.006 0.006 ↑ 1.0 50 1

Seq Scan on blocked_station (cost=0.00..1.50 rows=50 width=4) (actual time=0.002..0.006 rows=50 loops=1)

24. 12.313 21.108 ↑ 1.0 1 1,759

GroupAggregate (cost=0.29..8.32 rows=1 width=40) (actual time=0.012..0.012 rows=1 loops=1,759)

  • Group Key: station.station_id
25. 8.795 8.795 ↑ 1.0 1 1,759

Index Scan using idx_stream_station_id on stream (cost=0.29..8.30 rows=1 width=111) (actual time=0.004..0.005 rows=1 loops=1,759)

  • Index Cond: (station_id = station.station_id)
  • Filter: last_check_ok
  • Rows Removed by Filter: 0
26. 32.736 324.384 ↑ 1.0 1 1,488

GroupAggregate (cost=19.67..32.13 rows=1 width=40) (actual time=0.218..0.218 rows=1 loops=1,488)

  • Group Key: station.station_id
27. 124.992 291.648 ↑ 1.0 4 1,488

Hash Join (cost=19.67..32.08 rows=4 width=41) (actual time=0.068..0.196 rows=4 loops=1,488)

  • Hash Cond: (t.tag_id = station_tag.tag_id)
28. 77.376 77.376 ↑ 1.0 536 1,488

Seq Scan on tag t (cost=0.00..10.36 rows=536 width=24) (actual time=0.001..0.052 rows=536 loops=1,488)

29. 4.464 89.280 ↑ 1.0 4 1,488

Hash (cost=19.62..19.62 rows=4 width=13) (actual time=0.060..0.060 rows=4 loops=1,488)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 23.808 84.816 ↑ 1.0 4 1,488

Hash Right Join (cost=13.49..19.62 rows=4 width=13) (actual time=0.032..0.057 rows=4 loops=1,488)

  • Hash Cond: (tn.tag_id = station_tag.tag_id)
31. 22.320 43.152 ↑ 1.0 69 1,488

Bitmap Heap Scan on tag_name tn (cost=4.81..10.67 rows=69 width=14) (actual time=0.017..0.029 rows=69 loops=1,488)

  • Recheck Cond: (language = 'en'::text)
  • Heap Blocks: exact=4464
32. 20.832 20.832 ↑ 1.0 69 1,488

Bitmap Index Scan on idx_tag_name_language (cost=0.00..4.79 rows=69 width=0) (actual time=0.014..0.014 rows=69 loops=1,488)

  • Index Cond: (language = 'en'::text)
33. 2.976 17.856 ↑ 1.0 4 1,488

Hash (cost=8.64..8.64 rows=4 width=4) (actual time=0.012..0.012 rows=4 loops=1,488)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 4.464 14.880 ↑ 1.0 4 1,488

Append (cost=0.29..8.64 rows=4 width=4) (actual time=0.005..0.010 rows=4 loops=1,488)

35. 7.440 7.440 ↓ 1.3 4 1,488

Index Only Scan using station_tag_new_pkey on station_tag (cost=0.29..4.34 rows=3 width=4) (actual time=0.004..0.005 rows=4 loops=1,488)

  • Index Cond: (station_id = station.station_id)
  • Heap Fetches: 0
36. 2.976 2.976 ↓ 0.0 0 1,488

Index Only Scan using tag_overrides_new_pkey on tag_overrides (cost=0.28..4.29 rows=1 width=6) (actual time=0.002..0.002 rows=0 loops=1,488)

  • Index Cond: (station_id = station.station_id)
  • Heap Fetches: 0
37. 5.952 5.952 ↑ 1.0 1 1,488

Index Scan using station_image_pkey on station_image (cost=0.29..0.39 rows=1 width=41) (actual time=0.004..0.004 rows=1 loops=1,488)

  • Index Cond: (station.station_id = station_id)
Planning time : 1.551 ms