explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eWWV

Settings
# exclusive inclusive rows x rows loops node
1. 59.430 22,486.254 ↓ 7.5 468 1

GroupAggregate (cost=681,281.45..681,287.96 rows=62 width=2,091) (actual time=22,425.127..22,486.254 rows=468 loops=1)

  • Group Key: aggregated_lives.coveragepositionid, aggregated_lives.urlid, aggregated_lives.categoryid, aggregated_lives.subcategoryid, aggregated_lives.drugid, aggregated_lives.payerid, aggregated_lives.payer, aggregated_lives."policyUrl", aggregated_lives."priorAuthorizationUrl", aggregated_lives."priorAuthorizationCriteriaUrl", aggregated_lives."priorAuthorizationFormUrl", aggregated_lives."priorAuthorizationListUrl", aggregated_lives."priorAuthorizationRationaleUrl", aggregated_lives.initialdiagnosis, aggregated_lives.continueddiagnosis, aggregated_lives.initialquantitylimit, aggregated_lives.continuedquantitylimit, aggregated_lives.initialagerequirement, aggregated_lives.continuedagerequirement, aggregated_lives.initialprescriberrequirements, aggregated_lives.continuedprescriberrequirements, aggregated_lives.initialreauthorizationduration, aggregated_lives.continuedreauthorizationduration, aggregated_lives."priorAuthorizationRequired", aggregated_lives."priorAuthorizationText", aggregated_lives."stepEdit", aggregated_lives."stepEditText", aggregated_lives.covered, aggregated_lives.criteria, aggregated_lives.phone, aggregated_lives.fax, aggregated_lives."indicationIds", aggregated_lives."coveredMedicalCodes
2. 48.679 22,426.824 ↓ 74.7 4,632 1

Sort (cost=681,281.45..681,281.60 rows=62 width=2,091) (actual time=22,425.081..22,426.824 rows=4,632 loops=1)

  • Sort Key: aggregated_lives.coveragepositionid, aggregated_lives.urlid, aggregated_lives.categoryid, aggregated_lives.subcategoryid, aggregated_lives.drugid, aggregated_lives.payerid, aggregated_lives.payer, aggregated_lives."policyUrl", aggregated_lives."priorAuthorizationUrl", aggregated_lives."priorAuthorizationCriteriaUrl", aggregated_lives."priorAuthorizationFormUrl", aggregated_lives."priorAuthorizationListUrl", aggregated_lives."priorAuthorizationRationaleUrl", aggregated_lives.initialdiagnosis, aggregated_lives.continueddiagnosis, aggregated_lives.initialquantitylimit, aggregated_lives.continuedquantitylimit, aggregated_lives.initialagerequirement, aggregated_lives.continuedagerequirement, aggregated_lives.initialprescriberrequirements, aggregated_lives.continuedprescriberrequirements, aggregated_lives.initialreauthorizationduration, aggregated_lives.continuedreauthorizationduration, aggregated_lives."priorAuthorizationRequired", aggregated_lives."priorAuthorizationText", aggregated_lives."stepEdit", aggregated_lives."stepEditText", aggregated_lives.covered, aggregated_lives.criteria, aggregated_lives.phone, aggregated_lives.fax, aggregated_lives."indicationIds", aggregated_lives."coveredMedicalCodes
  • Sort Method: quicksort Memory: 13318kB
3. 0.844 22,378.145 ↓ 74.7 4,632 1

Subquery Scan on aggregated_lives (cost=681,272.16..681,279.60 rows=62 width=2,091) (actual time=22,343.308..22,378.145 rows=4,632 loops=1)

4. 31.756 22,377.301 ↓ 74.7 4,632 1

GroupAggregate (cost=681,272.16..681,278.98 rows=62 width=2,168) (actual time=22,343.307..22,377.301 rows=4,632 loops=1)

  • Group Key: mostrecentcoveragepositionupdate.id, urls_plantype_states.urlid, urls.categoryid, urls.subcategoryid, productwithdeleted.id, company.id, company.companyname, urls.url, (COALESCE(pa_criteria_urls.url, pa_form_urls.url, pa_list_urls.url, pa_rationale_urls.url)), pa_criteria_urls.url, pa_form_urls.url, pa_list_urls.url, pa_rationale_urls.url, mostrecentcoveragepositionupdate.initialdiagnosis, mostrecentcoveragepositionupdate.continueddiagnosis, mostrecentcoveragepositionupdate.initialquantitylimit, mostrecentcoveragepositionupdate.continuedquantitylimit, mostrecentcoveragepositionupdate.initialagerequirement, mostrecentcoveragepositionupdate.continuedagerequirement, mostrecentcoveragepositionupdate.initialprescriberrequirements, mostrecentcoveragepositionupdate.continuedprescriberrequirements, (get_coverage_duration(mostrecentcoveragepositionupdate.initialreauthorizationduration)), (get_coverage_duration(mostrecentcoveragepositionupdate.continuedreauthorizationduration)), mostrecentcoveragepositionupdate.priorauth, mostrecentcoveragepositionupdate.priorauthtext, (COALESCE(to_jsonb(array_remove(array_agg(DISTINCT productwithdeleted_1.name ORDER BY productwithdeleted_1.name), NULL::text)), '[]'::jsonb)), mostrecentcoveragepositionupdate.stepedittext, mostrecentcoveragepositionupdate.covered, mostrecentcoveragepositionupdate.criteria, mostrecentcoveragepositionupdate.phone, mostrecentcoveragepositionupdate.fax, (COALESCE(jsonb_agg(DISTINCT indicationswithdeleted.id ORDER BY indicationswithdeleted.id) FILTER (WHERE (indicationswithdeleted.id IS NOT NULL)), '[]'::jsonb)), states.id, states.name, plantype.name, originalplantype.name, (COALESCE(coveredcodes."coveredMedicalCodes", '[]'::jsonb))
5. 269.942 22,345.545 ↓ 82.7 5,128 1

Sort (cost=681,272.16..681,272.32 rows=62 width=2,140) (actual time=22,343.270..22,345.545 rows=5,128 loops=1)

  • Sort Key: mostrecentcoveragepositionupdate.id, urls_plantype_states.urlid, urls.categoryid, urls.subcategoryid, company.id, company.companyname, urls.url, (COALESCE(pa_criteria_urls.url, pa_form_urls.url, pa_list_urls.url, pa_rationale_urls.url)), pa_criteria_urls.url, pa_form_urls.url, pa_list_urls.url, pa_rationale_urls.url, mostrecentcoveragepositionupdate.initialdiagnosis, mostrecentcoveragepositionupdate.continueddiagnosis, mostrecentcoveragepositionupdate.initialquantitylimit, mostrecentcoveragepositionupdate.continuedquantitylimit, mostrecentcoveragepositionupdate.initialagerequirement, mostrecentcoveragepositionupdate.continuedagerequirement, mostrecentcoveragepositionupdate.initialprescriberrequirements, mostrecentcoveragepositionupdate.continuedprescriberrequirements, (get_coverage_duration(mostrecentcoveragepositionupdate.initialreauthorizationduration)), (get_coverage_duration(mostrecentcoveragepositionupdate.continuedreauthorizationduration)), mostrecentcoveragepositionupdate.priorauth, mostrecentcoveragepositionupdate.priorauthtext, (COALESCE(to_jsonb(array_remove(array_agg(DISTINCT productwithdeleted_1.name ORDER BY productwithdeleted_1.name), NULL::text)), '[]'::jsonb)), mostrecentcoveragepositionupdate.stepedittext, mostrecentcoveragepositionupdate.covered, mostrecentcoveragepositionupdate.criteria, mostrecentcoveragepositionupdate.phone, mostrecentcoveragepositionupdate.fax, (COALESCE(jsonb_agg(DISTINCT indicationswithdeleted.id ORDER BY indicationswithdeleted.id) FILTER (WHERE (indicationswithdeleted.id IS NOT NULL)), '[]'::jsonb)), states.id, states.name, plantype.name, originalplantype.name, (COALESCE(coveredcodes."coveredMedicalCodes", '[]'::jsonb))
  • Sort Method: quicksort Memory: 13065kB
6. 703.541 22,075.603 ↓ 82.7 5,128 1

Hash Join (cost=671,113.76..681,270.32 rows=62 width=2,140) (actual time=21,357.018..22,075.603 rows=5,128 loops=1)

  • Hash Cond: (mostrecentcoveragepositionupdate.urlid = urls_plantype_states.urlid)
7. 16.199 3,925.019 ↑ 9.9 476 1

GroupAggregate (cost=669,934.19..680,025.37 rows=4,710 width=2,330) (actual time=3,908.567..3,925.019 rows=476 loops=1)

  • Group Key: mostrecentcoveragepositionupdate.urlid, mostrecentcoveragepositionupdate.urlupdateid, mostrecentcoveragepositionupdate.supplementalurlid, mostrecentcoveragepositionupdate.supplementalurlupdateid, mostrecentcoveragepositionupdate.phone, mostrecentcoveragepositionupdate.pbpaphonesource, mostrecentcoveragepositionupdate.fax, mostrecentcoveragepositionupdate.pbpafaxsource, mostrecentcoveragepositionupdate.id, mostrecentcoveragepositionupdate.covered, mostrecentcoveragepositionupdate.criteria, mostrecentcoveragepositionupdate.initialdiagnosis, mostrecentcoveragepositionupdate.continueddiagnosis, mostrecentcoveragepositionupdate.initialquantitylimit, mostrecentcoveragepositionupdate.continuedquantitylimit, mostrecentcoveragepositionupdate.initialagerequirement, mostrecentcoveragepositionupdate.continuedagerequirement, mostrecentcoveragepositionupdate.initialprescriberrequirements, mostrecentcoveragepositionupdate.continuedprescriberrequirements, mostrecentcoveragepositionupdate.initialreauthorizationduration, mostrecentcoveragepositionupdate.continuedreauthorizationduration, mostrecentcoveragepositionupdate.initialdocumentationrequired, mostrecentcoveragepositionupdate.lastreviewdate, mostrecentcoveragepositionupdate.fdarestrictionrating_indivior, mostrecentcoveragepositionupdate.stepedittext, mostrecentcoveragepositionupdate.priorauth, mostrecentcoveragepositionupdate.priorauthtext, urls.id, productwithdeleted.id, company.id, collatedcoverageposition.coveragepositionid, pa_criteria_urls.id, pa_form_urls.id, pa_list_urls.id, pa_rationale_urls.id, supplementalurl.id, mostrecenturlupdates.id, mostrecenturlupdates_1.id, mostrecenturlupdates_2.id, mostrecenturlupdates_3.id, supplementalurlupdate.id, coveredcodes."coveredMedicalCodes
8.          

CTE coveredcodes

9. 1.103 4.788 ↑ 1.0 214 1

GroupAggregate (cost=606.44..615.07 rows=214 width=36) (actual time=3.658..4.788 rows=214 loops=1)

  • Group Key: coverageposition_medicalcode.coveragepositionid
10. 0.256 3.685 ↑ 1.0 794 1

Sort (cost=606.44..608.42 rows=794 width=9) (actual time=3.612..3.685 rows=794 loops=1)

  • Sort Key: coverageposition_medicalcode.coveragepositionid
  • Sort Method: quicksort Memory: 62kB
11. 0.987 3.429 ↑ 1.0 794 1

Merge Join (cost=282.13..568.19 rows=794 width=9) (actual time=1.658..3.429 rows=794 loops=1)

  • Merge Cond: (medicalcode.id = coverageposition_medicalcode.medicalcodeid)
12. 2.065 2.065 ↑ 3.1 11,621 1

Index Scan using medicalcode_id_pkey on medicalcode (cost=0.29..1,463.88 rows=35,822 width=9) (actual time=0.064..2.065 rows=11,621 loops=1)

13. 0.243 0.377 ↑ 1.0 794 1

Sort (cost=51.18..53.17 rows=794 width=8) (actual time=0.311..0.377 rows=794 loops=1)

  • Sort Key: coverageposition_medicalcode.medicalcodeid
  • Sort Method: quicksort Memory: 62kB
14. 0.134 0.134 ↑ 1.0 794 1

Seq Scan on coverageposition_medicalcode (cost=0.00..12.94 rows=794 width=8) (actual time=0.018..0.134 rows=794 loops=1)

  • Filter: covered
15. 12.704 3,908.820 ↑ 4.0 1,186 1

Sort (cost=669,319.13..669,330.90 rows=4,710 width=2,102) (actual time=3,908.345..3,908.820 rows=1,186 loops=1)

  • Sort Key: mostrecentcoveragepositionupdate.urlid, mostrecentcoveragepositionupdate.urlupdateid, mostrecentcoveragepositionupdate.supplementalurlid, mostrecentcoveragepositionupdate.supplementalurlupdateid, mostrecentcoveragepositionupdate.phone, mostrecentcoveragepositionupdate.pbpaphonesource, mostrecentcoveragepositionupdate.fax, mostrecentcoveragepositionupdate.pbpafaxsource, mostrecentcoveragepositionupdate.id, mostrecentcoveragepositionupdate.covered, mostrecentcoveragepositionupdate.criteria, mostrecentcoveragepositionupdate.initialdiagnosis, mostrecentcoveragepositionupdate.continueddiagnosis, mostrecentcoveragepositionupdate.initialquantitylimit, mostrecentcoveragepositionupdate.continuedquantitylimit, mostrecentcoveragepositionupdate.initialagerequirement, mostrecentcoveragepositionupdate.continuedagerequirement, mostrecentcoveragepositionupdate.initialprescriberrequirements, mostrecentcoveragepositionupdate.continuedprescriberrequirements, mostrecentcoveragepositionupdate.initialreauthorizationduration, mostrecentcoveragepositionupdate.continuedreauthorizationduration, mostrecentcoveragepositionupdate.initialdocumentationrequired, mostrecentcoveragepositionupdate.lastreviewdate, mostrecentcoveragepositionupdate.fdarestrictionrating_indivior, mostrecentcoveragepositionupdate.stepedittext, mostrecentcoveragepositionupdate.priorauth, mostrecentcoveragepositionupdate.priorauthtext, company.id, pa_criteria_urls.id, pa_form_urls.id, pa_list_urls.id, pa_rationale_urls.id, supplementalurl.id, mostrecenturlupdates.id, mostrecenturlupdates_1.id, mostrecenturlupdates_2.id, mostrecenturlupdates_3.id, supplementalurlupdate.id, coveredcodes."coveredMedicalCodes
  • Sort Method: quicksort Memory: 2806kB
16. 0.380 3,896.116 ↑ 4.0 1,186 1

Hash Left Join (cost=612,028.88..669,031.78 rows=4,710 width=2,102) (actual time=3,892.510..3,896.116 rows=1,186 loops=1)

  • Hash Cond: (mostrecentcoveragepositionupdate.id = coveredcodes.coveragepositionid)
17. 0.596 3,890.796 ↑ 3.7 1,186 1

Nested Loop Left Join (cost=612,021.92..668,851.17 rows=4,402 width=2,070) (actual time=3,887.548..3,890.796 rows=1,186 loops=1)

18. 1.464 3,890.200 ↑ 3.7 1,186 1

Hash Left Join (cost=612,021.50..644,624.49 rows=4,402 width=2,062) (actual time=3,887.540..3,890.200 rows=1,186 loops=1)

  • Hash Cond: (mostrecentcoveragepositionupdate.supplementalurlid = supplementalurl.id)
19. 0.573 3,825.781 ↑ 3.7 1,186 1

Hash Left Join (cost=603,007.56..635,550.02 rows=4,402 width=2,058) (actual time=3,823.416..3,825.781 rows=1,186 loops=1)

  • Hash Cond: (pa_list_urls.id = urls_3.id)
20. 0.566 2,901.433 ↑ 3.7 1,186 1

Hash Left Join (cost=460,753.58..493,235.52 rows=4,402 width=2,050) (actual time=2,899.331..2,901.433 rows=1,186 loops=1)

  • Hash Cond: (pa_rationale_urls.id = urls_4.id)
21. 0.528 1,973.051 ↑ 3.7 1,186 1

Nested Loop Left Join (cost=318,499.60..350,921.01 rows=4,402 width=2,042) (actual time=1,971.194..1,973.051 rows=1,186 loops=1)

22. 0.579 1,972.523 ↑ 3.7 1,186 1

Nested Loop Left Join (cost=318,499.18..339,643.75 rows=4,402 width=1,937) (actual time=1,971.191..1,972.523 rows=1,186 loops=1)

23. 16.086 1,971.944 ↑ 3.7 1,186 1

Hash Right Join (cost=318,498.76..328,366.49 rows=4,402 width=1,832) (actual time=1,971.184..1,971.944 rows=1,186 loops=1)

  • Hash Cond: (urls_1.id = pa_criteria_urls.id)
24. 89.497 859.828 ↑ 1.0 161,741 1

Hash Join (cost=131,013.18..140,229.57 rows=161,953 width=12) (actual time=745.440..859.828 rows=161,741 loops=1)

  • Hash Cond: (COALESCE(urls_1.rootid, urls_1.id) = mostrecenturlupdates.urlid)
25. 25.011 25.011 ↑ 1.0 161,953 1

Seq Scan on urls urls_1 (cost=0.00..6,989.53 rows=161,953 width=8) (actual time=0.023..25.011 rows=161,953 loops=1)

26. 33.131 745.320 ↓ 2.4 127,264 1

Hash (cost=130,349.82..130,349.82 rows=53,069 width=12) (actual time=745.320..745.320 rows=127,264 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 6493kB
27. 19.267 712.189 ↓ 2.4 127,264 1

Subquery Scan on mostrecenturlupdates (cost=0.42..130,349.82 rows=53,069 width=12) (actual time=0.019..712.189 rows=127,264 loops=1)

28. 66.053 692.922 ↓ 2.4 127,264 1

Unique (cost=0.42..129,819.13 rows=53,069 width=426) (actual time=0.018..692.922 rows=127,264 loops=1)

29. 626.869 626.869 ↑ 1.0 650,239 1

Index Only Scan using urlupdates_urlid_maxfirstfetchtime_maxid__idx on urlupdates (cost=0.42..128,193.53 rows=650,239 width=426) (actual time=0.016..626.869 rows=650,239 loops=1)

  • Heap Fetches: 650239
30. 1.096 1,096.030 ↑ 3.7 1,186 1

Hash (cost=187,430.55..187,430.55 rows=4,402 width=1,824) (actual time=1,096.030..1,096.030 rows=1,186 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 1963kB
31. 0.711 1,094.934 ↑ 3.7 1,186 1

Hash Left Join (cost=177,545.98..187,430.55 rows=4,402 width=1,824) (actual time=964.625..1,094.934 rows=1,186 loops=1)

  • Hash Cond: (collatedcoverageposition.priorauthcriteriaurlid = pa_criteria_urls.id)
32. 17.223 1,023.518 ↑ 3.7 1,186 1

Hash Right Join (cost=168,532.04..178,399.77 rows=4,402 width=1,719) (actual time=893.613..1,023.518 rows=1,186 loops=1)

  • Hash Cond: (urls_2.id = pa_form_urls.id)
33. 90.467 865.226 ↑ 1.0 161,741 1

Hash Join (cost=131,013.18..140,229.57 rows=161,953 width=12) (actual time=750.163..865.226 rows=161,741 loops=1)

  • Hash Cond: (COALESCE(urls_2.rootid, urls_2.id) = mostrecenturlupdates_1.urlid)
34. 24.701 24.701 ↑ 1.0 161,953 1

Seq Scan on urls urls_2 (cost=0.00..6,989.53 rows=161,953 width=8) (actual time=0.007..24.701 rows=161,953 loops=1)

35. 33.474 750.058 ↓ 2.4 127,264 1

Hash (cost=130,349.82..130,349.82 rows=53,069 width=12) (actual time=750.058..750.058 rows=127,264 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 6493kB
36. 19.316 716.584 ↓ 2.4 127,264 1

Subquery Scan on mostrecenturlupdates_1 (cost=0.42..130,349.82 rows=53,069 width=12) (actual time=0.016..716.584 rows=127,264 loops=1)

37. 65.544 697.268 ↓ 2.4 127,264 1

Unique (cost=0.42..129,819.13 rows=53,069 width=426) (actual time=0.016..697.268 rows=127,264 loops=1)

38. 631.724 631.724 ↑ 1.0 650,239 1

Index Only Scan using urlupdates_urlid_maxfirstfetchtime_maxid__idx on urlupdates urlupdates_1 (cost=0.42..128,193.53 rows=650,239 width=426) (actual time=0.015..631.724 rows=650,239 loops=1)

  • Heap Fetches: 650239
39. 0.949 141.069 ↑ 3.7 1,186 1

Hash (cost=37,463.83..37,463.83 rows=4,402 width=1,711) (actual time=141.069..141.069 rows=1,186 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 1955kB
40. 0.936 140.120 ↑ 3.7 1,186 1

Hash Left Join (cost=30,394.23..37,463.83 rows=4,402 width=1,711) (actual time=134.251..140.120 rows=1,186 loops=1)

  • Hash Cond: (collatedcoverageposition.priorauthformurlid = pa_form_urls.id)
41. 0.391 70.651 ↑ 3.7 1,186 1

Hash Left Join (cost=21,380.29..28,414.76 rows=4,402 width=1,606) (actual time=65.408..70.651 rows=1,186 loops=1)

  • Hash Cond: (coveragepositionstepedit.productid = productwithdeleted_1.id)
42. 0.283 67.501 ↑ 3.7 1,186 1

Hash Left Join (cost=21,127.36..28,101.31 rows=4,402 width=1,596) (actual time=62.623..67.501 rows=1,186 loops=1)

  • Hash Cond: ((mostrecentcoveragepositionupdate.productid = competingdruggroup_drugs.drugid) AND (mostrecentcoveragepositionupdate.urlid = competingcoverageposition.urlid))
43. 0.425 67.187 ↑ 3.7 1,186 1

Hash Left Join (cost=19,028.26..25,729.64 rows=4,402 width=1,582) (actual time=62.585..67.187 rows=1,186 loops=1)

  • Hash Cond: (mostrecentcoveragepositionupdate.id = coveragepositionstepedit.coveragepositionid)
44. 0.113 64.981 ↑ 2.6 476 1

Nested Loop (cost=18,810.55..25,446.56 rows=1,220 width=1,578) (actual time=60.789..64.981 rows=476 loops=1)

45. 0.057 0.057 ↑ 1.0 1 1

Index Scan using drugs_id_pkey on productwithdeleted (cost=0.29..6.30 rows=1 width=26) (actual time=0.056..0.057 rows=1 loops=1)

  • Index Cond: (id = 974)
  • Filter: (dtime IS NULL)
46. 0.202 64.811 ↑ 2.6 476 1

Hash Join (cost=18,810.26..25,428.06 rows=1,220 width=1,552) (actual time=60.728..64.811 rows=476 loops=1)

  • Hash Cond: (urls.companyid = company.id)
47. 0.178 61.418 ↑ 2.6 476 1

Hash Left Join (cost=18,726.37..25,327.40 rows=1,220 width=1,529) (actual time=57.529..61.418 rows=476 loops=1)

  • Hash Cond: ((mostrecentcoveragepositionupdate.productid = product_indications.productid) AND (mostrecentcoveragepositionupdate.indicationid = product_indications.indicationid))
48. 0.331 61.165 ↑ 2.6 476 1

Hash Join (cost=18,696.02..25,281.75 rows=1,220 width=1,529) (actual time=57.441..61.165 rows=476 loops=1)

  • Hash Cond: (mostrecentcoveragepositionupdate.id = collatedcoverageposition.coveragepositionid)
49. 0.313 54.288 ↑ 2.6 476 1

Nested Loop (cost=18,106.52..24,675.48 rows=1,220 width=1,505) (actual time=50.846..54.288 rows=476 loops=1)

50. 0.166 52.529 ↑ 3.1 482 1

Subquery Scan on mostrecentcoveragepositionupdate (cost=18,106.10..18,158.88 rows=1,508 width=1,384) (actual time=50.824..52.529 rows=482 loops=1)

  • Filter: (NOT mostrecentcoveragepositionupdate.removed)
  • Rows Removed by Filter: 51
51. 0.340 52.363 ↑ 5.7 533 1

Unique (cost=18,106.10..18,128.72 rows=3,016 width=1,401) (actual time=50.822..52.363 rows=533 loops=1)

52. 6.734 52.023 ↑ 1.0 3,016 1

Sort (cost=18,106.10..18,113.64 rows=3,016 width=1,401) (actual time=50.820..52.023 rows=3,016 loops=1)

  • Sort Key: coverageposition.urlid, coverageposition_indications.indicationid, urlupdates_2.firstfetchtime DESC, coverageposition.urlupdateid DESC
  • Sort Method: quicksort Memory: 5217kB
53. 1.850 45.289 ↑ 1.0 3,016 1

Hash Left Join (cost=175.62..17,931.79 rows=3,016 width=1,401) (actual time=6.128..45.289 rows=3,016 loops=1)

  • Hash Cond: ((coverageposition.productid = coverageposition_indications.productid) AND (coverageposition.id = coverageposition_indications.coveragepositionid) AND (coverageposition.urlid = coverageposition_indications.urlid) AND (coverageposition.urlupdateid = coverageposition_indications.urlupdateid))
54. 0.701 37.368 ↑ 1.0 3,016 1

Nested Loop (cost=0.42..17,711.35 rows=3,016 width=1,397) (actual time=0.040..37.368 rows=3,016 loops=1)

55. 27.619 27.619 ↑ 1.0 3,016 1

Seq Scan on coverageposition (cost=0.00..2,744.61 rows=3,016 width=1,381) (actual time=0.026..27.619 rows=3,016 loops=1)

  • Filter: (productid = 974)
  • Rows Removed by Filter: 18873
56. 9.048 9.048 ↑ 1.0 1 3,016

Index Scan using urlupdates_id_pkey on urlupdates urlupdates_2 (cost=0.42..4.95 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=3,016)

  • Index Cond: (id = coverageposition.urlupdateid)
57. 0.579 6.071 ↑ 1.0 3,009 1

Hash (cost=115.01..115.01 rows=3,009 width=20) (actual time=6.070..6.071 rows=3,009 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 185kB
58. 5.492 5.492 ↑ 1.0 3,009 1

Seq Scan on coverageposition_indications (cost=0.00..115.01 rows=3,009 width=20) (actual time=0.649..5.492 rows=3,009 loops=1)

  • Filter: (productid = 974)
  • Rows Removed by Filter: 3072
59. 1.446 1.446 ↑ 1.0 1 482

Index Scan using urls_id_pkey on urls (cost=0.42..4.31 rows=1 width=121) (actual time=0.003..0.003 rows=1 loops=482)

  • Index Cond: (id = mostrecentcoveragepositionupdate.urlid)
  • Filter: (hide = 0)
  • Rows Removed by Filter: 0
60. 3.214 6.546 ↑ 1.0 21,889 1

Hash (cost=315.89..315.89 rows=21,889 width=24) (actual time=6.546..6.546 rows=21,889 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1063kB
61. 3.332 3.332 ↑ 1.0 21,889 1

Seq Scan on collatedcoverageposition (cost=0.00..315.89 rows=21,889 width=24) (actual time=0.017..3.332 rows=21,889 loops=1)

62. 0.003 0.075 ↑ 1.0 3 1

Hash (cost=30.31..30.31 rows=3 width=12) (actual time=0.075..0.075 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
63. 0.005 0.072 ↑ 1.0 3 1

Nested Loop Left Join (cost=3.59..30.31 rows=3 width=12) (actual time=0.053..0.072 rows=3 loops=1)

64. 0.022 0.052 ↑ 1.0 3 1

Bitmap Heap Scan on product_indications (cost=3.31..11.40 rows=3 width=8) (actual time=0.039..0.052 rows=3 loops=1)

  • Recheck Cond: (productid = 974)
  • Heap Blocks: exact=2
65. 0.030 0.030 ↑ 1.0 3 1

Bitmap Index Scan on product_indications_productid_indicationid_key (cost=0.00..3.31 rows=3 width=0) (actual time=0.029..0.030 rows=3 loops=1)

  • Index Cond: (productid = 974)
66. 0.015 0.015 ↑ 1.0 1 3

Index Scan using indications_id_pkey on indicationswithdeleted (cost=0.28..6.29 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=3)

  • Index Cond: (product_indications.indicationid = id)
  • Filter: (dtime IS NULL)
67. 0.185 3.191 ↑ 1.0 1,195 1

Hash (cost=68.95..68.95 rows=1,195 width=27) (actual time=3.191..3.191 rows=1,195 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
68. 3.006 3.006 ↑ 1.0 1,195 1

Seq Scan on company (cost=0.00..68.95 rows=1,195 width=27) (actual time=0.559..3.006 rows=1,195 loops=1)

69. 0.945 1.781 ↑ 1.0 8,076 1

Hash (cost=116.76..116.76 rows=8,076 width=8) (actual time=1.781..1.781 rows=8,076 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 380kB
70. 0.836 0.836 ↑ 1.0 8,076 1

Seq Scan on coveragepositionstepedit (cost=0.00..116.76 rows=8,076 width=8) (actual time=0.014..0.836 rows=8,076 loops=1)

71. 0.000 0.031 ↓ 0.0 0 1

Hash (cost=2,070.56..2,070.56 rows=1,903 width=26) (actual time=0.031..0.031 rows=0 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 16kB
72. 0.004 0.031 ↓ 0.0 0 1

Hash Join (cost=1,644.03..2,070.56 rows=1,903 width=26) (actual time=0.031..0.031 rows=0 loops=1)

  • Hash Cond: (competingdruggroup_drugs.competingdruggroupid = competingdruggroup.id)
73. 0.000 0.013 ↓ 0.0 0 1

Hash Left Join (cost=1,643.00..2,063.58 rows=952 width=34) (actual time=0.013..0.013 rows=0 loops=1)

  • Hash Cond: (competingcoverageposition.productid = productwithdeleted_2.id)
74. 0.003 0.013 ↓ 0.0 0 1

Hash Right Join (cost=1,390.07..1,797.56 rows=952 width=24) (actual time=0.013..0.013 rows=0 loops=1)

  • Hash Cond: (competingcollatedcoverageposition.coveragepositionid = competingcoverageposition.id)
75. 0.000 0.000 ↓ 0.0 0

Seq Scan on collatedcoverageposition competingcollatedcoverageposition (cost=0.00..315.89 rows=21,889 width=8) (never executed)

76. 0.001 0.010 ↓ 0.0 0 1

Hash (cost=1,378.17..1,378.17 rows=952 width=24) (actual time=0.010..0.010 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
77. 0.000 0.009 ↓ 0.0 0 1

Nested Loop (cost=265.32..1,378.17 rows=952 width=24) (actual time=0.009..0.009 rows=0 loops=1)

78. 0.000 0.009 ↓ 0.0 0 1

Nested Loop (cost=0.13..10.27 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=1)

  • Join Filter: ((competingdruggroup_drugs.drugid <> competitors.drugid) AND (competingdruggroup_drugs.competingdruggroupid = competitors.competingdruggroupid))
79. 0.009 0.009 ↓ 0.0 0 1

Index Only Scan using competingdruggroup_drugs_competingdruggroupid_drugid_key on competingdruggroup_drugs (cost=0.13..9.17 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=1)

  • Index Cond: (drugid = 974)
  • Heap Fetches: 0
80. 0.000 0.000 ↓ 0.0 0

Seq Scan on competingdruggroup_drugs competitors (cost=0.00..1.04 rows=4 width=8) (never executed)

81. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on coverageposition competingcoverageposition (cost=265.19..1,358.38 rows=952 width=12) (never executed)

  • Recheck Cond: (productid = competitors.drugid)
82. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on coverageposition_id_productid_urlid_urlupdateid_unique (cost=0.00..264.96 rows=952 width=0) (never executed)

  • Index Cond: (productid = competitors.drugid)
83. 0.000 0.000 ↓ 0.0 0

Hash (cost=146.30..146.30 rows=8,530 width=18) (never executed)

84. 0.000 0.000 ↓ 0.0 0

Seq Scan on productwithdeleted productwithdeleted_2 (cost=0.00..146.30 rows=8,530 width=18) (never executed)

  • Filter: (dtime IS NULL)
85. 0.002 0.014 ↓ 2.0 2 1

Hash (cost=1.02..1.02 rows=1 width=4) (actual time=0.013..0.014 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
86. 0.012 0.012 ↓ 2.0 2 1

Seq Scan on competingdruggroup (cost=0.00..1.02 rows=1 width=4) (actual time=0.011..0.012 rows=2 loops=1)

  • Filter: (dtime IS NULL)
87. 1.296 2.759 ↑ 1.0 8,530 1

Hash (cost=146.30..146.30 rows=8,530 width=18) (actual time=2.759..2.759 rows=8,530 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 559kB
88. 1.463 1.463 ↑ 1.0 8,530 1

Seq Scan on productwithdeleted productwithdeleted_1 (cost=0.00..146.30 rows=8,530 width=18) (actual time=0.007..1.463 rows=8,530 loops=1)

  • Filter: (dtime IS NULL)
89. 37.648 68.533 ↑ 1.0 161,953 1

Hash (cost=6,989.53..6,989.53 rows=161,953 width=109) (actual time=68.533..68.533 rows=161,953 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 24558kB
90. 30.885 30.885 ↑ 1.0 161,953 1

Seq Scan on urls pa_form_urls (cost=0.00..6,989.53 rows=161,953 width=109) (actual time=0.007..30.885 rows=161,953 loops=1)

91. 39.612 70.705 ↑ 1.0 161,953 1

Hash (cost=6,989.53..6,989.53 rows=161,953 width=109) (actual time=70.705..70.705 rows=161,953 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 24558kB
92. 31.093 31.093 ↑ 1.0 161,953 1

Seq Scan on urls pa_criteria_urls (cost=0.00..6,989.53 rows=161,953 width=109) (actual time=0.021..31.093 rows=161,953 loops=1)

93. 0.000 0.000 ↓ 0.0 0 1,186

Index Scan using urls_id_pkey on urls pa_list_urls (cost=0.42..2.55 rows=1 width=109) (actual time=0.000..0.000 rows=0 loops=1,186)

  • Index Cond: (collatedcoverageposition.priorauthlisturlid = id)
94. 0.000 0.000 ↓ 0.0 0 1,186

Index Scan using urls_id_pkey on urls pa_rationale_urls (cost=0.42..2.55 rows=1 width=109) (actual time=0.000..0.000 rows=0 loops=1,186)

  • Index Cond: (collatedcoverageposition.priorauthrationaleurlid = id)
95. 37.923 927.816 ↑ 1.0 161,741 1

Hash (cost=140,229.57..140,229.57 rows=161,953 width=12) (actual time=927.816..927.816 rows=161,741 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 8998kB
96. 93.976 889.893 ↑ 1.0 161,741 1

Hash Join (cost=131,013.18..140,229.57 rows=161,953 width=12) (actual time=771.083..889.893 rows=161,741 loops=1)

  • Hash Cond: (COALESCE(urls_4.rootid, urls_4.id) = mostrecenturlupdates_3.urlid)
97. 24.957 24.957 ↑ 1.0 161,953 1

Seq Scan on urls urls_4 (cost=0.00..6,989.53 rows=161,953 width=8) (actual time=0.023..24.957 rows=161,953 loops=1)

98. 33.482 770.960 ↓ 2.4 127,264 1

Hash (cost=130,349.82..130,349.82 rows=53,069 width=12) (actual time=770.960..770.960 rows=127,264 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 6493kB
99. 19.694 737.478 ↓ 2.4 127,264 1

Subquery Scan on mostrecenturlupdates_3 (cost=0.42..130,349.82 rows=53,069 width=12) (actual time=0.030..737.478 rows=127,264 loops=1)

100. 65.853 717.784 ↓ 2.4 127,264 1

Unique (cost=0.42..129,819.13 rows=53,069 width=426) (actual time=0.029..717.784 rows=127,264 loops=1)

101. 651.931 651.931 ↑ 1.0 650,239 1

Index Only Scan using urlupdates_urlid_maxfirstfetchtime_maxid__idx on urlupdates urlupdates_3 (cost=0.42..128,193.53 rows=650,239 width=426) (actual time=0.027..651.931 rows=650,239 loops=1)

  • Heap Fetches: 650239
102. 39.471 923.775 ↑ 1.0 161,741 1

Hash (cost=140,229.57..140,229.57 rows=161,953 width=12) (actual time=923.775..923.775 rows=161,741 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 8998kB
103. 95.560 884.304 ↑ 1.0 161,741 1

Hash Join (cost=131,013.18..140,229.57 rows=161,953 width=12) (actual time=763.804..884.304 rows=161,741 loops=1)

  • Hash Cond: (COALESCE(urls_3.rootid, urls_3.id) = mostrecenturlupdates_2.urlid)
104. 25.066 25.066 ↑ 1.0 161,953 1

Seq Scan on urls urls_3 (cost=0.00..6,989.53 rows=161,953 width=8) (actual time=0.037..25.066 rows=161,953 loops=1)

105. 33.750 763.678 ↓ 2.4 127,264 1

Hash (cost=130,349.82..130,349.82 rows=53,069 width=12) (actual time=763.678..763.678 rows=127,264 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 6493kB
106. 19.236 729.928 ↓ 2.4 127,264 1

Subquery Scan on mostrecenturlupdates_2 (cost=0.42..130,349.82 rows=53,069 width=12) (actual time=0.027..729.928 rows=127,264 loops=1)

107. 67.042 710.692 ↓ 2.4 127,264 1

Unique (cost=0.42..129,819.13 rows=53,069 width=426) (actual time=0.025..710.692 rows=127,264 loops=1)

108. 643.650 643.650 ↑ 1.0 650,239 1

Index Only Scan using urlupdates_urlid_maxfirstfetchtime_maxid__idx on urlupdates urlupdates_4 (cost=0.42..128,193.53 rows=650,239 width=426) (actual time=0.023..643.650 rows=650,239 loops=1)

  • Heap Fetches: 650239
109. 34.134 62.955 ↑ 1.0 161,953 1

Hash (cost=6,989.53..6,989.53 rows=161,953 width=4) (actual time=62.955..62.955 rows=161,953 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 7742kB
110. 28.821 28.821 ↑ 1.0 161,953 1

Seq Scan on urls supplementalurl (cost=0.00..6,989.53 rows=161,953 width=4) (actual time=0.010..28.821 rows=161,953 loops=1)

111. 0.000 0.000 ↓ 0.0 0 1,186

Index Only Scan using urlupdates_id_pkey on urlupdates supplementalurlupdate (cost=0.42..5.49 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1,186)

  • Index Cond: (id = mostrecentcoveragepositionupdate.supplementalurlupdateid)
  • Heap Fetches: 0
112. 0.058 4.940 ↑ 1.0 214 1

Hash (cost=4.28..4.28 rows=214 width=36) (actual time=4.940..4.940 rows=214 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
113. 4.882 4.882 ↑ 1.0 214 1

CTE Scan on coveredcodes (cost=0.00..4.28 rows=214 width=36) (actual time=3.660..4.882 rows=214 loops=1)

114. 1,190.809 17,447.043 ↓ 7,556.6 3,468,471 1

Hash (cost=1,173.83..1,173.83 rows=459 width=85) (actual time=17,447.043..17,447.043 rows=3,468,471 loops=1)

  • Buckets: 524288 (originally 1024) Batches: 16 (originally 1) Memory Usage: 28673kB
115. 2,425.957 16,256.234 ↓ 7,556.6 3,468,471 1

Nested Loop (cost=106.24..1,173.83 rows=459 width=85) (actual time=0.516..16,256.234 rows=3,468,471 loops=1)

  • Join Filter: (originalplantype.id = urls_plantype_states.plantypeid)
  • Rows Removed by Join Filter: 19494915
116. 11.982 43.291 ↓ 218.4 26,211 1

Hash Join (cost=105.81..144.78 rows=120 width=109) (actual time=0.147..43.291 rows=26,211 loops=1)

  • Hash Cond: ((companypresencewithsubsidiaries.stateid)::text = (states.id)::text)
117. 5.978 31.225 ↓ 57.0 26,411 1

Nested Loop (cost=99.88..135.92 rows=463 width=96) (actual time=0.057..31.225 rows=26,411 loops=1)

118. 0.068 0.509 ↓ 7.1 57 1

Hash Join (cost=99.60..114.60 rows=8 width=80) (actual time=0.038..0.509 rows=57 loops=1)

  • Hash Cond: (plantypechild.childid = plantype.id)
119. 0.076 0.427 ↑ 1.0 57 1

Hash Join (cost=97.97..112.67 rows=59 width=44) (actual time=0.020..0.427 rows=57 loops=1)

  • Hash Cond: (plantypechild.id = originalplantype.id)
120. 0.341 0.341 ↑ 7.3 57 1

CTE Scan on plantypechild (cost=96.34..104.70 rows=418 width=72) (actual time=0.006..0.341 rows=57 loops=1)

121.          

CTE plantypechild

122. 0.083 0.213 ↑ 7.3 57 1

Recursive Union (cost=0.00..96.34 rows=418 width=76) (actual time=0.004..0.213 rows=57 loops=1)

123. 0.025 0.025 ↑ 1.0 28 1

Seq Scan on plantype plantype_1 (cost=0.00..1.28 rows=28 width=76) (actual time=0.002..0.025 rows=28 loops=1)

124. 0.069 0.105 ↑ 3.9 10 3

Hash Join (cost=1.63..8.67 rows=39 width=76) (actual time=0.014..0.035 rows=10 loops=3)

  • Hash Cond: (plantypechild_1.carry = plantype_2.id)
125. 0.018 0.018 ↑ 14.7 19 3

WorkTable Scan on plantypechild plantypechild_1 (cost=0.00..5.60 rows=280 width=40) (actual time=0.001..0.006 rows=19 loops=3)

126. 0.009 0.018 ↑ 1.0 28 1

Hash (cost=1.28..1.28 rows=28 width=40) (actual time=0.017..0.018 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
127. 0.009 0.009 ↑ 1.0 28 1

Seq Scan on plantype plantype_2 (cost=0.00..1.28 rows=28 width=40) (actual time=0.007..0.009 rows=28 loops=1)

128. 0.004 0.010 ↑ 1.0 28 1

Hash (cost=1.28..1.28 rows=28 width=36) (actual time=0.010..0.010 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
129. 0.006 0.006 ↑ 1.0 28 1

Seq Scan on plantype originalplantype (cost=0.00..1.28 rows=28 width=36) (actual time=0.002..0.006 rows=28 loops=1)

130. 0.002 0.014 ↑ 1.0 28 1

Hash (cost=1.28..1.28 rows=28 width=36) (actual time=0.014..0.014 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
131. 0.012 0.012 ↑ 1.0 28 1

Seq Scan on plantype (cost=0.00..1.28 rows=28 width=36) (actual time=0.005..0.012 rows=28 loops=1)

132. 24.738 24.738 ↓ 8.3 463 57

Index Scan using companypresencewithsubsidiari_plantypeid_stateid_parentcomp_idx on companypresencewithsubsidiaries (cost=0.29..2.10 rows=56 width=28) (actual time=0.006..0.434 rows=463 loops=57)

  • Index Cond: (plantypeid = plantypechild.childid)
133. 0.010 0.084 ↑ 1.0 52 1

Hash (cost=5.27..5.27 rows=52 width=13) (actual time=0.084..0.084 rows=52 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
134. 0.074 0.074 ↑ 1.0 52 1

Seq Scan on states (cost=0.00..5.27 rows=52 width=13) (actual time=0.015..0.074 rows=52 loops=1)

  • Filter: ((id)::text = ANY ('{AK,AL,AR,AZ,CA,CO,CT,DC,DE,FL,GA,HI,IA,ID,IL,IN,KS,KY,LA,MA,MD,ME,MI,MN,MO,MS,MT,NC,ND,NE,NH,NJ,NM,NV,NY,OH,OK,OR,PA,PR,RI,SC,SD,TN,TX,UT,VA,VT,WA,WI,WV,WY}'::text[]))
  • Rows Removed by Filter: 5
135. 13,786.986 13,786.986 ↓ 8.1 876 26,211

Index Scan using urls_plantype_states_companyid_stateid_idx on urls_plantype_states (cost=0.43..7.23 rows=108 width=15) (actual time=0.011..0.526 rows=876 loops=26,211)

  • Index Cond: ((companyid = companypresencewithsubsidiaries.parentcompanyid) AND ((stateid)::text = (companypresencewithsubsidiaries.stateid)::text))
Planning time : 23.422 ms
Execution time : 22,487.746 ms