explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HVWa : q2

Settings
# exclusive inclusive rows x rows loops node
1. 24.401 4,824.140 ↓ 330.4 40,643 1

Nested Loop (cost=1,634,565.35..1,654,702.47 rows=123 width=659) (actual time=4,014.260..4,824.140 rows=40,643 loops=1)

  • Join Filter: ((pe.personid = pn.personid) AND (GREATEST(LEAST(CURRENT_DATE, pe.enddate), pe.effectivedate) >= pn.effectivedate) AND (GREATEST(LEAST(CURRENT_DATE, pe.enddate), pe.effectivedate) <= pn.enddate))
  • Rows Removed by Join Filter: 5603
2. 232.379 4,553.595 ↓ 10.6 41,024 1

Merge Join (cost=1,634,564.93..1,652,616.97 rows=3,879 width=370) (actual time=4,014.236..4,553.595 rows=41,024 loops=1)

  • Merge Cond: (pp.personid = pe.personid)
  • Join Filter: ((GREATEST(LEAST(CURRENT_DATE, pe.enddate), pe.effectivedate) >= pp.effectivedate) AND (GREATEST(LEAST(CURRENT_DATE, pe.enddate), pe.effectivedate) <= pp.enddate))
  • Rows Removed by Join Filter: 177190
3. 220.474 220.474 ↓ 1.0 218,754 1

Index Scan using pers_pospersendeff on pers_pos pp (cost=0.42..14,438.27 rows=217,041 width=92) (actual time=0.009..220.474 rows=218,754 loops=1)

  • Filter: ((persposrel = 'Occupies'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 76877
4. 17.285 4,100.742 ↑ 1.2 223,586 1

Materialize (cost=1,634,550.29..1,635,909.32 rows=271,807 width=278) (actual time=4,014.211..4,100.742 rows=223,586 loops=1)

5. 945.519 4,083.457 ↑ 1.9 139,888 1

Sort (cost=1,634,550.29..1,635,229.80 rows=271,807 width=278) (actual time=4,014.209..4,083.457 rows=139,888 loops=1)

  • Sort Key: pe.personid
  • Sort Method: external merge Disk: 35120kB
6. 9.855 3,137.938 ↑ 1.9 139,888 1

Nested Loop (cost=5.80..1,589,842.68 rows=271,807 width=278) (actual time=0.421..3,137.938 rows=139,888 loops=1)

7. 138.701 138.701 ↑ 1.0 271,762 1

Seq Scan on person p (cost=0.00..7,660.07 rows=271,807 width=105) (actual time=0.006..138.701 rows=271,762 loops=1)

8. 271.762 2,989.382 ↑ 1.0 1 271,762

Limit (cost=5.80..5.80 rows=1 width=177) (actual time=0.011..0.011 rows=1 loops=271,762)

9. 271.762 2,717.620 ↑ 1.0 1 271,762

Sort (cost=5.80..5.80 rows=1 width=177) (actual time=0.010..0.010 rows=1 loops=271,762)

  • Sort Key: (displayrow(pe.effectivedate, pe.enddate))
  • Sort Method: quicksort Memory: 25kB
10. 2,445.858 2,445.858 ↑ 1.0 1 271,762

Index Scan using personemploymentenddate on person_employment pe (cost=0.42..5.79 rows=1 width=177) (actual time=0.007..0.009 rows=1 loops=271,762)

  • Index Cond: (personid = p.personid)
  • Filter: ((effectivedate <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 1
11. 246.144 246.144 ↑ 1.0 1 41,024

Index Scan using personnamepersonidnametype on person_names pn (cost=0.42..0.51 rows=1 width=289) (actual time=0.005..0.006 rows=1 loops=41,024)

  • Index Cond: ((personid = pp.personid) AND (nametype = 'Legal'::bpchar))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
Planning time : 192.700 ms
Execution time : 4,833.323 ms