explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xG5X

Settings
# exclusive inclusive rows x rows loops node
1. 0.023 515,060.013 ↑ 947,924.0 1 1

Sort (cost=3,994,277.65..3,996,647.46 rows=947,924 width=137) (actual time=515,060.012..515,060.013 rows=1 loops=1)

  • Sort Key: fi.owner_id, fi.id
  • Sort Method: quicksort Memory: 25kB
2. 13,308.141 515,059.990 ↑ 947,924.0 1 1

Hash Join (cost=3,465,189.10..3,696,055.28 rows=947,924 width=137) (actual time=508,437.900..515,059.990 rows=1 loops=1)

  • Hash Cond: ((efv.original_repository_id)::text = (fi.repository_id)::text)
  • Join Filter: ((NOT (fi.formats @> COALESCE(efv.formats, '{}'::character varying[]))) OR (NOT (fi.formats <@ COALESCE(efv.formats, '{}'::character varying[]))))
  • Rows Removed by Join Filter: 2874514
3. 900.976 495,259.535 ↓ 2.1 2,874,538 1

Subquery Scan on efv (cost=3,062,969.55..3,157,046.56 rows=1,339,512 width=103) (actual time=412,465.745..495,259.535 rows=2,874,538 loops=1)

4. 7,575.997 494,358.559 ↓ 2.1 2,874,538 1

GroupAggregate (cost=3,062,969.55..3,143,651.44 rows=1,339,512 width=103) (actual time=412,465.743..494,358.559 rows=2,874,538 loops=1)

  • Group Key: efv_1.original_repository_id
5. 483,882.105 486,782.562 ↓ 1.0 8,526,256 1

Sort (cost=3,062,969.55..3,084,282.21 rows=8,525,066 width=95) (actual time=412,465.667..486,782.562 rows=8,526,256 loops=1)

  • Sort Key: efv_1.original_repository_id
  • Sort Method: external merge Disk: 881800kB
6. 2,900.457 2,900.457 ↓ 1.0 8,526,256 1

Seq Scan on extfileversions efv_1 (cost=0.00..333,288.66 rows=8,525,066 width=95) (actual time=0.046..2,900.457 rows=8,526,256 loops=1)

7. 3,008.962 6,492.314 ↓ 1.0 2,985,225 1

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

  • Buckets: 16384 Batches: 512 Memory Usage: 1399kB
8. 3,483.352 3,483.352 ↓ 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.080..3,483.352 rows=2,985,225 loops=1)

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