explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 15ET

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 77,600.208 ↑ 2,962,951.0 1 1

Sort (cost=4,584,114.54..4,591,521.92 rows=2,962,951 width=137) (actual time=77,600.208..77,600.208 rows=1 loops=1)

  • Sort Key: fi.owner_id, fi.id
  • Sort Method: quicksort Memory: 25kB
2. 8,995.265 77,600.193 ↑ 2,962,951.0 1 1

GroupAggregate (cost=3,500,331.49..3,627,596.00 rows=2,962,951 width=137) (actual time=74,715.144..77,600.193 rows=1 loops=1)

  • Group Key: fi.id
  • Filter: ((NOT (fi.formats @> COALESCE(array_agg(efv.format), '{}'::character varying[]))) OR (NOT (fi.formats <@ COALESCE(array_agg(efv.format), '{}'::character varying[]))))
  • Rows Removed by Filter: 2874514
3. 32,430.159 68,604.928 ↓ 1.4 8,526,204 1

Sort (cost=3,500,331.49..3,515,414.07 rows=6,033,029 width=129) (actual time=63,975.321..68,604.928 rows=8,526,204 loops=1)

  • Sort Key: fi.id
  • Sort Method: external merge Disk: 1404768kB
4. 23,406.230 36,174.769 ↓ 1.4 8,526,204 1

Hash Join (cost=402,219.55..1,171,216.16 rows=6,033,029 width=129) (actual time=8,934.081..36,174.769 rows=8,526,204 loops=1)

  • Hash Cond: ((efv.original_repository_id)::text = (fi.repository_id)::text)
5. 3,838.523 3,838.523 ↓ 1.0 8,526,256 1

Seq Scan on extfileversions efv (cost=0.00..333,288.66 rows=8,525,066 width=95) (actual time=0.061..3,838.523 rows=8,526,256 loops=1)

6. 4,101.994 8,930.016 ↓ 1.0 2,985,225 1

Hash (cost=292,844.66..292,844.66 rows=2,962,951 width=173) (actual time=8,930.016..8,930.016 rows=2,985,225 loops=1)

  • Buckets: 16384 Batches: 512 Memory Usage: 1399kB
7. 4,828.022 4,828.022 ↓ 1.0 2,985,225 1

Seq Scan on files fi (cost=0.00..292,844.66 rows=2,962,951 width=173) (actual time=0.027..4,828.022 rows=2,985,225 loops=1)

  • Filter: (((repository_id)::text ~~ 'amazon%'::text) AND (filetype_id = '1'::smallint))
  • Rows Removed by Filter: 1201619