explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 29Qn : q3.2

Settings
# exclusive inclusive rows x rows loops node
1. 28.237 5,437.935 ↓ 133,650.0 133,650 1

Subquery Scan on l (cost=56,250.63..63,813.92 rows=1 width=99) (actual time=5,195.324..5,437.935 rows=133,650 loops=1)

  • Filter: ((l.pn_rank = 1) AND (l.pp_rank = 1))
  • Rows Removed by Filter: 100899
2. 178.666 5,409.698 ↓ 16.6 234,549 1

WindowAgg (cost=56,250.63..63,601.87 rows=14,137 width=133) (actual time=5,195.323..5,409.698 rows=234,549 loops=1)

3. 1,517.570 5,231.032 ↓ 16.6 234,549 1

Sort (cost=56,250.63..56,285.97 rows=14,137 width=125) (actual time=5,195.316..5,231.032 rows=234,549 loops=1)

  • Sort Key: pn.personid, (displayrow(pp.effectivedate, pp.enddate))
  • Sort Method: external sort Disk: 31672kB
4. 121.856 3,713.462 ↓ 16.6 234,549 1

WindowAgg (cost=47,924.84..55,276.08 rows=14,137 width=125) (actual time=3,561.953..3,713.462 rows=234,549 loops=1)

5. 1,433.566 3,591.606 ↓ 16.6 234,549 1

Sort (cost=47,924.84..47,960.18 rows=14,137 width=117) (actual time=3,561.946..3,591.606 rows=234,549 loops=1)

  • Sort Key: pn.personid, (displayrow(pn.effectivedate, pn.enddate))
  • Sort Method: external sort Disk: 29840kB
6. 1,480.843 2,158.040 ↓ 16.6 234,549 1

Merge Join (cost=1.27..46,950.29 rows=14,137 width=117) (actual time=0.050..2,158.040 rows=234,549 loops=1)

  • Merge Cond: (pn.personid = p.personid)
7. 150.922 648.963 ↓ 13.7 234,549 1

Merge Join (cost=0.84..33,849.63 rows=17,158 width=112) (actual time=0.015..648.963 rows=234,549 loops=1)

  • Merge Cond: (pn.personid = pp.personid)
8. 148.053 148.053 ↓ 3.1 161,494 1

Index Scan using personnamepersonidnametype on person_names pn (cost=0.42..18,052.77 rows=52,926 width=87) (actual time=0.010..148.053 rows=161,494 loops=1)

  • Index Cond: (nametype = 'Legal'::bpchar)
  • Filter: ((effectivedate <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 26129
9. 349.988 349.988 ↓ 3.2 236,095 1

Index Scan using pers_pospersendeff on pers_pos pp (cost=0.42..15,311.16 rows=72,721 width=25) (actual time=0.003..349.988 rows=236,095 loops=1)

  • Filter: ((effectivedate <= enddate) AND (persposrel = 'Occupies'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 119414
10. 28.234 28.234 ↑ 1.0 272,201 1

Index Only Scan using person_pkey on person p (cost=0.42..5,137.08 rows=272,244 width=13) (actual time=0.009..28.234 rows=272,201 loops=1)

  • Heap Fetches: 336
Planning time : 23.340 ms
Execution time : 5,459.362 ms