explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aYfq

Settings
# exclusive inclusive rows x rows loops node
1. 1,041.596 466,754.891 ↓ 1.3 1,045,515 1

Subquery Scan on final (cost=26,665,230.87..26,744,604.75 rows=814,091 width=1,177) (actual time=463,830.511..466,754.891 rows=1,045,515 loops=1)

2.          

CTE enrollment_records

3. 13,695.209 266,023.137 ↑ 1.0 16,184,643 1

WindowAgg (cost=11,398,891.38..11,843,992.24 rows=16,185,486 width=1,051) (actual time=199,672.661..266,023.137 rows=16,184,643 loops=1)

4. 227,680.619 252,327.928 ↑ 1.0 16,184,643 1

Sort (cost=11,398,891.38..11,439,355.09 rows=16,185,486 width=1,043) (actual time=199,672.629..252,327.928 rows=16,184,643 loops=1)

  • Sort Key: a_1.subscriber_id, b_1.filename_timestamp DESC NULLS LAST, a_1.application_date DESC, a_1.completion_date DESC, a_1.election_type
  • Sort Method: external merge Disk: 4,560,168kB
5. 12,630.981 24,647.309 ↑ 1.0 16,184,643 1

Hash Join (cost=283.02..988,733.31 rows=16,185,486 width=1,043) (actual time=6.113..24,647.309 rows=16,184,643 loops=1)

  • Hash Cond: (a_1.source_info_uuid = b_1.uuid)
6. 12,011.274 12,011.274 ↑ 1.0 16,184,643 1

Seq Scan on convey__member_demographics_enrollment_record a_1 (cost=0.00..765,899.86 rows=16,185,486 width=1,035) (actual time=0.946..12,011.274 rows=16,184,643 loops=1)

7. 1.371 5.054 ↑ 1.0 4,401 1

Hash (cost=228.01..228.01 rows=4,401 width=24) (actual time=5.054..5.054 rows=4,401 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 305kB
8. 3.683 3.683 ↑ 1.0 4,401 1

Seq Scan on convey__member_demographics_timing_info b_1 (cost=0.00..228.01 rows=4,401 width=24) (actual time=0.047..3.683 rows=4,401 loops=1)

9. 1,612.897 465,713.295 ↓ 1.3 1,045,515 1

WindowAgg (cost=14,821,238.63..14,876,189.77 rows=814,091 width=1,200) (actual time=463,829.192..465,713.295 rows=1,045,515 loops=1)

10. 2,562.192 464,100.398 ↓ 1.3 1,045,515 1

Sort (cost=14,821,238.63..14,823,273.86 rows=814,091 width=265) (actual time=463,829.121..464,100.398 rows=1,045,515 loops=1)

  • Sort Key: a.person_id, b.carrier_id, ((date_part('year'::text, (b.effective_date)::timestamp without time zone) > date_part('year'::text, (('now'::cstring)::date)::timestamp without time zone))), b.effective_date DESC NULLS LAST
  • Sort Method: external sort Disk: 246,408kB
11. 705.368 461,538.206 ↓ 1.3 1,045,515 1

WindowAgg (cost=14,641,779.38..14,682,483.93 rows=814,091 width=265) (actual time=460,587.871..461,538.206 rows=1,045,515 loops=1)

12. 2,074.492 460,832.838 ↓ 1.3 1,045,515 1

Sort (cost=14,641,779.38..14,643,814.61 rows=814,091 width=257) (actual time=460,587.853..460,832.838 rows=1,045,515 loops=1)

  • Sort Key: a.person_id, b.carrier_id, b.effective_date, a.most_recent_filename_timestamp DESC NULLS LAST, b.end_date DESC
  • Sort Method: external sort Disk: 238,232kB
13. 773.133 458,758.346 ↓ 1.3 1,045,515 1

WindowAgg (cost=14,463,910.14..14,504,614.69 rows=814,091 width=257) (actual time=457,734.839..458,758.346 rows=1,045,515 loops=1)

14. 2,696.100 457,985.213 ↓ 1.3 1,045,515 1

Sort (cost=14,463,910.14..14,465,945.37 rows=814,091 width=253) (actual time=457,734.803..457,985.213 rows=1,045,515 loops=1)

  • Sort Key: a.person_id, b.carrier_id, (CASE WHEN (b.effective_date > b.end_date) THEN 2 ELSE 1 END), b.effective_date
  • Sort Method: external sort Disk: 234,160kB
15. 797.714 455,289.113 ↓ 1.3 1,045,515 1

WindowAgg (cost=14,289,666.12..14,328,335.45 rows=814,091 width=253) (actual time=454,128.526..455,289.113 rows=1,045,515 loops=1)

16. 2,774.848 454,491.399 ↓ 1.3 1,045,515 1

Sort (cost=14,289,666.12..14,291,701.35 rows=814,091 width=249) (actual time=454,128.492..454,491.399 rows=1,045,515 loops=1)

  • Sort Key: a.person_id, (CASE WHEN (b.effective_date > b.end_date) THEN 2 ELSE 1 END), b.effective_date
  • Sort Method: external merge Disk: 227,280kB
17. 886.267 451,716.551 ↓ 1.3 1,045,515 1

WindowAgg (cost=14,111,351.65..14,154,091.43 rows=814,091 width=249) (actual time=449,755.812..451,716.551 rows=1,045,515 loops=1)

18. 10,276.526 450,830.284 ↓ 1.3 1,045,515 1

Sort (cost=14,111,351.65..14,113,386.88 rows=814,091 width=244) (actual time=449,755.685..450,830.284 rows=1,045,515 loops=1)

  • Sort Key: b.subscriber_id, b.effective_date, (CASE WHEN (b.approved_benefit = 'N'::text) THEN 1 ELSE 2 END)
  • Sort Method: external merge Disk: 222,544kB
19. 30,215.482 440,553.758 ↓ 1.3 1,045,515 1

Hash Join (cost=3,066,523.88..13,977,366.96 rows=814,091 width=244) (actual time=348,888.371..440,553.758 rows=1,045,515 loops=1)

  • Hash Cond: ((b.subscriber_id = a.subscriber_id) AND (b.source_info_uuid = a.most_recent_uuid) AND (b.carrier_id = a.contract_number))
20. 75,355.065 75,355.065 ↑ 1.0 195,592,440 1

Seq Scan on convey__member_demographics_eligibility_record b (cost=0.00..5,750,082.20 rows=195,592,492 width=88) (actual time=0.961..75,355.065 rows=195,592,440 loops=1)

  • Filter: (pbp_code <> '000'::text)
21. 72.806 334,983.211 ↓ 1.2 89,788 1

Hash (cost=3,065,192.30..3,065,192.30 rows=76,090 width=188) (actual time=334,983.211..334,983.211 rows=89,788 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 15,945kB
22. 6,712.287 334,910.405 ↓ 1.2 89,788 1

Hash Right Join (cost=1,276,558.27..3,065,192.30 rows=76,090 width=188) (actual time=310,568.549..334,910.405 rows=89,788 loops=1)

  • Hash Cond: ((permanent_address.subscriber_id = a.subscriber_id) AND (permanent_address.carrier_id = a.contract_number) AND (permanent_address.source_info_uuid = a.most_recent_uuid))
23. 17,715.892 17,715.892 ↓ 1.0 13,487,681 1

Seq Scan on convey__member_demographics_address_record permanent_address (cost=0.00..1,437,822.72 rows=13,337,760 width=42) (actual time=57.176..17,715.892 rows=13,487,681 loops=1)

  • Filter: ((address_type = '2'::text) AND ((end_date IS NULL) OR (end_date >= ('now'::cstring)::date)) AND (begin_date <= ('now'::cstring)::date))
  • Rows Removed by Filter: 18,888,453
24. 65.714 310,482.226 ↓ 1.2 89,788 1

Hash (cost=1,275,226.70..1,275,226.70 rows=76,090 width=178) (actual time=310,482.226..310,482.226 rows=89,788 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 15,016kB
25. 5,885.191 310,416.512 ↓ 1.2 89,788 1

Hash Join (cost=371,978.24..1,275,226.70 rows=76,090 width=178) (actual time=302,130.964..310,416.512 rows=89,788 loops=1)

  • Hash Cond: ((c.subscriber_id = a.subscriber_id) AND (c.source_info_uuid = a.most_recent_uuid) AND (c.carrier_id = a.contract_number))
26. 3,184.919 3,184.919 ↓ 1.0 13,496,043 1

Seq Scan on convey__member_demographics_demographic_record c (cost=0.00..497,616.14 rows=13,495,714 width=55) (actual time=0.027..3,184.919 rows=13,496,043 loops=1)

27. 129.280 301,346.402 ↑ 1.0 89,788 1

Hash (cost=370,406.95..370,406.95 rows=89,788 width=123) (actual time=301,346.402..301,346.402 rows=89,788 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 9,992kB
28. 201.425 301,217.122 ↑ 1.0 89,788 1

Hash Right Join (cost=4,610.70..370,406.95 rows=89,788 width=123) (actual time=199,836.796..301,217.122 rows=89,788 loops=1)

  • Hash Cond: ((d.subscriber_id = a.subscriber_id) AND (d.carrier_id = a.contract_number))
29. 300,857.083 300,857.083 ↑ 1.0 77,419 1

CTE Scan on enrollment_records d (cost=0.00..364,173.43 rows=80,927 width=128) (actual time=199,672.691..300,857.083 rows=77,419 loops=1)

  • Filter: (record_rank = 1)
  • Rows Removed by Filter: 16,107,224
30. 60.533 158.614 ↑ 1.0 89,788 1

Hash (cost=3,263.88..3,263.88 rows=89,788 width=59) (actual time=158.614..158.614 rows=89,788 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 9,267kB
31. 98.081 98.081 ↑ 1.0 89,788 1

Seq Scan on temp_confirmed_record_identifiers a (cost=0.00..3,263.88 rows=89,788 width=59) (actual time=1.038..98.081 rows=89,788 loops=1)

Planning time : 93.633 ms
Execution time : 468,265.531 ms