#	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 (forResult)
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)

Result  (cost=165718.73..28388087.53 rows=99266 width=101) (actual time=3408.913..3541.246 rows=3308 loops=1)
  ->  Sort  (cost=165718.73..165966.90 rows=99266 width=69) (actual time=3408.705..3409.283 rows=3308 loops=1)
        Sort Key: media.added DESC
        Sort Method: quicksort  Memory: 735kB
        ->  Nested Loop  (cost=156167.65..157480.15 rows=99266 width=69) (actual time=3393.680..3407.655 rows=3308 loops=1)
              ->  HashAggregate  (cost=156167.23..156169.23 rows=200 width=4) (actual time=3393.662..3394.228 rows=3308 loops=1)
                    Group Key: media_1.id
                    ->  GroupAggregate  (cost=9918.33..153734.84 rows=194591 width=8) (actual time=336.044..3392.493 rows=3308 loops=1)
                          Group Key: media_1.id
                          Filter: (NOT (array_agg(media_tags.tag) && '{126995,22020,112733,44335}'::integer[]))
                          Rows Removed by Filter: 2544
                          ->  Nested Loop  (cost=9918.33..142427.40 rows=1665893 width=8) (actual time=333.959..2799.259 rows=7634059 loops=1)
                                Join Filter: (media_1.id = media_tags.medium)
                                ->  Nested Loop  (cost=9917.90..25089.76 rows=99551 width=12) (actual time=333.949..448.556 rows=122623 loops=1)
                                      ->  Merge Join  (cost=9917.47..15751.37 rows=5949 width=8) (actual time=333.919..357.621 rows=5930 loops=1)
                                            Merge Cond: (media_tags_2.medium = media_1.id)
                                            ->  Sort  (cost=9917.05..9931.92 rows=5949 width=4) (actual time=328.175..328.815 rows=5930 loops=1)
                                                  Sort Key: media_tags_2.medium
                                                  Sort Method: quicksort  Memory: 470kB
                                                  ->  Index Scan using mediumbytag on media_tags media_tags_2  (cost=0.43..9544.09 rows=5949 width=4) (actual time=313.546..327.061 rows=5930 loops=1)
                                                        Index Cond: (tag = ANY ('{6169,38643,112626}'::integer[]))
                                            ->  Index Only Scan using media_pkey on media media_1  (cost=0.42..5248.80 rows=198532 width=4) (actual time=0.037..18.905 rows=198652 loops=1)
                                                  Heap Fetches: 3712
                                      ->  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=5930)
                                            Index Cond: (medium = media_1.id)
                                            Heap Fetches: 122623
                                ->  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=122623)
                                      Index Cond: (medium = media_tags_1.medium)
                                      Heap Fetches: 7634059
              ->  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=3308)
                    Index Cond: (id = media_1.id)
  SubPlan 1
    ->  Nested Loop  (cost=0.85..284.30 rows=24 width=13) (actual time=0.006..0.038 rows=17 loops=3308)
          ->  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=3308)
                Index Cond: (medium = media.id)
          ->  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=55654)
                Index Cond: (id = media_tags_3.tag)
Planning Time: 1.522 ms
JIT:
  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
Execution Time: 3564.634 ms

Per node type stats

node type	count	sum of times	% of query
GroupAggregate	1	593.234 ms	16.8 %
HashAggregate	1	1.735 ms	0.0 %
Index Only Scan	3	1,812.717 ms	51.2 %
Index Scan	4	419.103 ms	11.8 %
Merge Join	1	9.901 ms	0.3 %
Nested Loop	4	694.915 ms	19.6 %
Result	1	6.259 ms	0.2 %
Sort	2	3.382 ms	0.1 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
media	2	32.137 ms	0.9 %
Index Only Scan	1	18.905 ms	58.8 %
Index Scan	1	13.232 ms	41.2 %
media_tags	4	2,144.029 ms	60.5 %
Index Only Scan	2	1,793.812 ms	83.7 %
Index Scan	2	350.217 ms	16.3 %
tags	1	55.654 ms	1.6 %
Index Scan	1	55.654 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EnMt

Color mode:

Visible columns:

Per node type stats

Per table stats