explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wQXf

Settings
# exclusive inclusive rows x rows loops node
1. 62.059 17,282.832 ↑ 239.7 69,428 1

GroupAggregate (cost=1,538,619.65..1,626,830.73 rows=16,643,600 width=49) (actual time=17,207.033..17,282.832 rows=69,428 loops=1)

  • Output: array_to_string(array_agg(p.id), ';'::text), (unnest((fn.normalized_name_id || pn.normalized_firstname_parts_ids))), (unnest((ln.normalized_name_id || pn.normalized_lastname_parts_ids))), fals…
  • Group Key: (unnest((fn.normalized_name_id || pn.normalized_firstname_parts_ids))), (unnest((ln.normalized_name_id || pn.normalized_lastname_parts_ids)))
2. 108.581 17,220.773 ↑ 1.2 141,395 1

Sort (cost=1,538,619.65..1,539,035.74 rows=166,436 width=24) (actual time=17,207.016..17,220.773 rows=141,395 loops=1)

  • Output: (unnest((fn.normalized_name_id || pn.normalized_firstname_parts_ids))), (unnest((ln.normalized_name_id || pn.normalized_lastname_parts_ids))), p.id
  • Sort Key: (unnest((fn.normalized_name_id || pn.normalized_firstname_parts_ids))), (unnest((ln.normalized_name_id || pn.normalized_lastname_parts_ids)))
  • Sort Method: external merge Disk: 4696kB
3. 1,579.443 17,112.192 ↑ 1.2 141,395 1

Hash Join (cost=484,253.18..1,524,185.82 rows=166,436 width=24) (actual time=4,653.272..17,112.192 rows=141,395 loops=1)

  • Output: unnest((fn.normalized_name_id || pn.normalized_firstname_parts_ids)), unnest((ln.normalized_name_id || pn.normalized_lastname_parts_ids)), p.id
  • Hash Cond: (pn.normalized_lastname_id = ln.normalized_name_id)
  • Join Filter: (((ln.normalized_name_id >= 200000) AND (ln.normalized_name_id <= 250000)) OR ((fn.normalized_name_id >= 200000) AND (fn.normalized_name_id <= 250000)))
  • Rows Removed by Join Filter: 3420303
4. 1,101.108 13,879.840 ↑ 1.0 3,539,565 1

Hash Join (cost=397,599.04..1,234,729.49 rows=3,551,873 width=93) (actual time=2,995.529..13,879.840 rows=3,539,565 loops=1)

  • Output: p.id, pn.normalized_firstname_parts_ids, pn.normalized_lastname_parts_ids, pn.normalized_lastname_id, fn.normalized_name_id
  • Hash Cond: (pn.normalized_firstname_id = fn.normalized_name_id)
5. 4,017.354 12,235.976 ↑ 1.0 3,539,565 1

Hash Join (cost=310,944.90..982,456.26 rows=3,551,873 width=93) (actual time=2,448.021..12,235.976 rows=3,539,565 loops=1)

  • Output: p.id, pn.normalized_firstname_parts_ids, pn.normalized_lastname_parts_ids, pn.normalized_firstname_id, pn.normalized_lastname_id
  • Hash Cond: (pn.id = p.name_id)
6. 5,772.410 5,772.410 ↑ 1.0 10,816,672 1

Seq Scan on ptgrid.personname pn (cost=0.00..218,896.50 rows=10,863,750 width=93) (actual time=0.472..5,772.410 rows=10,816,672 loops=1)

  • Output: pn.normalized_firstname_parts_ids, pn.normalized_lastname_parts_ids, pn.id, pn.normalized_firstname_id, pn.normalized_lastname_id
7. 568.307 2,446.212 ↑ 1.0 3,539,565 1

Hash (cost=249,202.49..249,202.49 rows=3,551,873 width=16) (actual time=2,446.212..2,446.212 rows=3,539,565 loops=1)

  • Output: p.id, p.name_id
  • Buckets: 262144 Batches: 32 Memory Usage: 7240kB
8. 1,877.905 1,877.905 ↑ 1.0 3,539,565 1

Seq Scan on ptgrid.person p (cost=0.00..249,202.49 rows=3,551,873 width=16) (actual time=0.024..1,877.905 rows=3,539,565 loops=1)

  • Output: p.id, p.name_id
  • Filter: ((p.delisteddate IS NULL) OR (p.delisteddate > now()))
  • Rows Removed by Filter: 2175586
9. 307.351 542.756 ↓ 1.0 2,119,795 1

Hash (cost=51,875.84..51,875.84 rows=2,119,784 width=8) (actual time=542.756..542.756 rows=2,119,795 loops=1)

  • Output: fn.normalized_name_id
  • Buckets: 262144 Batches: 16 Memory Usage: 7311kB
10. 235.405 235.405 ↓ 1.0 2,119,795 1

Seq Scan on public.normalized_name fn (cost=0.00..51,875.84 rows=2,119,784 width=8) (actual time=0.006..235.405 rows=2,119,795 loops=1)

  • Output: fn.normalized_name_id
11. 324.675 1,652.909 ↓ 1.0 2,119,795 1

Hash (cost=51,875.84..51,875.84 rows=2,119,784 width=8) (actual time=1,652.909..1,652.909 rows=2,119,795 loops=1)

  • Output: ln.normalized_name_id
  • Buckets: 262144 Batches: 16 Memory Usage: 7312kB
12. 1,328.234 1,328.234 ↓ 1.0 2,119,795 1

Seq Scan on public.normalized_name ln (cost=0.00..51,875.84 rows=2,119,784 width=8) (actual time=0.415..1,328.234 rows=2,119,795 loops=1)

  • Output: ln.normalized_name_id
Planning time : 4.694 ms
Execution time : 17,286.491 ms