explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EnMt

Settings
# exclusive inclusive rows x rows loops node
1. 6.259 3,541.246 ↑ 30.0 3,308 1

Result (cost=165,718.73..28,388,087.53 rows=99,266 width=101) (actual time=3,408.913..3,541.246 rows=3,308 loops=1)

  • Functions: 46
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 3.867 ms, Inlining 40.695 ms, Optimization 166.497 ms, Emission 106.028 ms, Total 317.088 ms
2. 1.628 3,409.283 ↑ 30.0 3,308 1

Sort (cost=165,718.73..165,966.90 rows=99,266 width=69) (actual time=3,408.705..3,409.283 rows=3,308 loops=1)

  • Sort Key: media.added DESC
  • Sort Method: quicksort Memory: 735kB
3. 0.195 3,407.655 ↑ 30.0 3,308 1

Nested Loop (cost=156,167.65..157,480.15 rows=99,266 width=69) (actual time=3,393.680..3,407.655 rows=3,308 loops=1)

4. 1.735 3,394.228 ↓ 16.5 3,308 1

HashAggregate (cost=156,167.23..156,169.23 rows=200 width=4) (actual time=3,393.662..3,394.228 rows=3,308 loops=1)

  • Group Key: media_1.id
5. 593.234 3,392.493 ↑ 58.8 3,308 1

GroupAggregate (cost=9,918.33..153,734.84 rows=194,591 width=8) (actual time=336.044..3,392.493 rows=3,308 loops=1)

  • Group Key: media_1.id
  • Filter: (NOT (array_agg(media_tags.tag) && '{126995,22020,112733,44335}'::integer[]))
  • Rows Removed by Filter: 2544
6. 633.981 2,799.259 ↓ 4.6 7,634,059 1

Nested Loop (cost=9,918.33..142,427.40 rows=1,665,893 width=8) (actual time=333.959..2,799.259 rows=7,634,059 loops=1)

  • Join Filter: (media_1.id = media_tags.medium)
7. 13.845 448.556 ↓ 1.2 122,623 1

Nested Loop (cost=9,917.90..25,089.76 rows=99,551 width=12) (actual time=333.949..448.556 rows=122,623 loops=1)

8. 9.901 357.621 ↑ 1.0 5,930 1

Merge Join (cost=9,917.47..15,751.37 rows=5,949 width=8) (actual time=333.919..357.621 rows=5,930 loops=1)

  • Merge Cond: (media_tags_2.medium = media_1.id)
9. 1.754 328.815 ↑ 1.0 5,930 1

Sort (cost=9,917.05..9,931.92 rows=5,949 width=4) (actual time=328.175..328.815 rows=5,930 loops=1)

  • Sort Key: media_tags_2.medium
  • Sort Method: quicksort Memory: 470kB
10. 327.061 327.061 ↑ 1.0 5,930 1

Index Scan using mediumbytag on media_tags media_tags_2 (cost=0.43..9,544.09 rows=5,949 width=4) (actual time=313.546..327.061 rows=5,930 loops=1)

  • Index Cond: (tag = ANY ('{6169,38643,112626}'::integer[]))
11. 18.905 18.905 ↓ 1.0 198,652 1

Index Only Scan using media_pkey on media media_1 (cost=0.42..5,248.80 rows=198,532 width=4) (actual time=0.037..18.905 rows=198,652 loops=1)

  • Heap Fetches: 3712
12. 77.090 77.090 ↑ 1.1 21 5,930

Index Only Scan using media_tags_pkey on media_tags media_tags_1 (cost=0.43..1.33 rows=24 width=4) (actual time=0.009..0.013 rows=21 loops=5,930)

  • Index Cond: (medium = media_1.id)
  • Heap Fetches: 122623
13. 1,716.722 1,716.722 ↓ 2.6 62 122,623

Index Only Scan using media_tags_pkey on media_tags (cost=0.43..0.88 rows=24 width=8) (actual time=0.002..0.014 rows=62 loops=122,623)

  • Index Cond: (medium = media_tags_1.medium)
  • Heap Fetches: 7634059
14. 13.232 13.232 ↑ 1.0 1 3,308

Index Scan using media_pkey on media (cost=0.42..7.78 rows=1 width=69) (actual time=0.004..0.004 rows=1 loops=3,308)

  • Index Cond: (id = media_1.id)
15.          

SubPlan (for Result)

16. 46.894 125.704 ↑ 1.4 17 3,308

Nested Loop (cost=0.85..284.30 rows=24 width=13) (actual time=0.006..0.038 rows=17 loops=3,308)

17. 23.156 23.156 ↑ 1.4 17 3,308

Index Scan using tagbymedium on media_tags media_tags_3 (cost=0.43..81.80 rows=24 width=4) (actual time=0.004..0.007 rows=17 loops=3,308)

  • Index Cond: (medium = media.id)
18. 55.654 55.654 ↑ 1.0 1 55,654

Index Scan using tags_pkey on tags (cost=0.42..8.44 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=55,654)

  • Index Cond: (id = media_tags_3.tag)
Planning time : 1.522 ms
Execution time : 3,564.634 ms