explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fO1w6

Settings
# exclusive inclusive rows x rows loops node
1. 578.580 105,582.487 ↓ 3,355.0 3,355 1

Subquery Scan on q0 (cost=1,448,689.87..2,030,645.34 rows=1 width=280) (actual time=104,319.212..105,582.487 rows=3,355 loops=1)

  • Filter: ((q0.sale_date >= '2019-09-01'::date) AND (q0.sale_date <= '2019-09-30'::date) AND ((q0.visit_type_name)::text = 'IP'::text) AND (q0.row_number_for_partition = 1))
  • Rows Removed by Filter: 2,474,316
2. 36,694.724 105,003.907 ↓ 1.4 2,477,671 1

WindowAgg (cost=1,448,689.87..1,995,640.50 rows=1,750,242 width=140) (actual time=56,119.994..105,003.907 rows=2,477,671 loops=1)

3. 6,653.421 68,309.183 ↓ 1.4 2,477,671 1

WindowAgg (cost=1,448,689.87..1,501,197.13 rows=1,750,242 width=140) (actual time=56,119.930..68,309.183 rows=2,477,671 loops=1)

4. 40,672.610 61,655.762 ↓ 1.4 2,477,671 1

Sort (cost=1,448,689.87..1,453,065.48 rows=1,750,242 width=140) (actual time=56,119.877..61,655.762 rows=2,477,671 loops=1)

  • Sort Key: ssm.sale_id, isg.item_group_id
  • Sort Method: external merge Disk: 319,384kB
5. 4,257.634 20,983.152 ↓ 1.4 2,477,671 1

Hash Left Join (cost=621,193.67..890,310.45 rows=1,750,242 width=140) (actual time=12,180.294..20,983.152 rows=2,477,671 loops=1)

  • Hash Cond: ((b.visit_id)::text = (pr.patient_id)::text)
6. 762.017 15,379.032 ↓ 1.4 2,477,671 1

Hash Left Join (cost=546,041.30..716,088.04 rows=1,750,242 width=119) (actual time=10,695.146..15,379.032 rows=2,477,671 loops=1)

  • Hash Cond: ((ssd.sale_item_id = sscd.sale_item_id) AND ((sbcl.claim_id)::text = (sscd.claim_id)::text))
  • Join Filter: (sbcl.priority = 2)
7. 954.345 14,616.441 ↓ 1.4 2,477,671 1

Hash Left Join (cost=545,993.48..672,284.16 rows=1,750,242 width=130) (actual time=10,694.564..14,616.441 rows=2,477,671 loops=1)

  • Hash Cond: (sstd.item_subgroup_id = isg.item_subgroup_id)
8. 2,662.020 13,662.084 ↓ 1.4 2,477,671 1

Hash Right Join (cost=545,992.14..648,216.99 rows=1,750,242 width=130) (actual time=10,694.535..13,662.084 rows=2,477,671 loops=1)

  • Hash Cond: (sstd.sale_item_id = ssd.sale_item_id)
9. 329.939 329.939 ↓ 1.0 1,753,862 1

Seq Scan on store_sales_tax_details sstd (cost=0.00..30,423.42 rows=1,750,242 width=8) (actual time=0.008..329.939 rows=1,753,862 loops=1)

10. 1,286.775 10,670.125 ↓ 1.0 1,600,741 1

Hash (cost=496,307.68..496,307.68 rows=1,599,877 width=126) (actual time=10,670.125..10,670.125 rows=1,600,741 loops=1)

  • Buckets: 1,024 Batches: 256 Memory Usage: 904kB
11. 493.465 9,383.350 ↓ 1.0 1,600,741 1

Hash Left Join (cost=315,000.12..496,307.68 rows=1,599,877 width=126) (actual time=6,478.141..9,383.350 rows=1,600,741 loops=1)

  • Hash Cond: ((ssd.sale_item_id = pscd.sale_item_id) AND ((pbcl.claim_id)::text = (pscd.claim_id)::text))
  • Join Filter: (pbcl.priority = 1)
12. 1,774.899 8,889.300 ↓ 1.0 1,600,471 1

Hash Join (cost=314,952.30..456,262.91 rows=1,599,877 width=133) (actual time=6,477.541..8,889.300 rows=1,600,471 loops=1)

  • Hash Cond: ((ssd.sale_id)::text = (ssm.sale_id)::text)
13. 643.956 643.956 ↓ 1.0 1,600,426 1

Seq Scan on store_sales_details ssd (cost=0.00..85,457.77 rows=1,599,877 width=20) (actual time=0.010..643.956 rows=1,600,426 loops=1)

14. 493.630 6,470.445 ↓ 1.0 518,534 1

Hash (cost=299,372.68..299,372.68 rows=517,969 width=113) (actual time=6,470.445..6,470.445 rows=518,534 loops=1)

  • Buckets: 1,024 Batches: 128 Memory Usage: 490kB
15. 235.458 5,976.815 ↓ 1.0 518,534 1

Hash Join (cost=94,353.63..299,372.68 rows=517,969 width=113) (actual time=2,111.728..5,976.815 rows=518,534 loops=1)

  • Hash Cond: (b.visit_type = vn.visit_type)
16. 131.302 5,741.348 ↓ 1.0 518,534 1

Hash Left Join (cost=94,352.54..292,249.52 rows=517,969 width=109) (actual time=2,111.702..5,741.348 rows=518,534 loops=1)

  • Hash Cond: ((bc.bill_no)::text = (sbcl.bill_no)::text)
17. 177.827 5,610.029 ↓ 1.0 518,534 1

Hash Left Join (cost=94,343.09..289,649.43 rows=517,969 width=106) (actual time=2,111.668..5,610.029 rows=518,534 loops=1)

  • Hash Cond: ((bc.bill_no)::text = (pbcl.bill_no)::text)
18. 668.200 5,431.111 ↓ 1.0 518,518 1

Hash Left Join (cost=94,248.09..286,797.63 rows=517,969 width=92) (actual time=2,110.567..5,431.111 rows=518,518 loops=1)

  • Hash Cond: ((b.visit_id)::text = (src.customer_id)::text)
19. 1,226.002 4,676.412 ↓ 1.0 518,518 1

Hash Join (cost=90,281.15..261,208.83 rows=517,969 width=74) (actual time=2,004.418..4,676.412 rows=518,518 loops=1)

  • Hash Cond: ((ssm.bill_no)::text = (b.bill_no)::text)
20. 1,446.752 2,750.234 ↓ 1.0 518,518 1

Hash Join (cost=27,237.30..173,408.07 rows=517,969 width=61) (actual time=762.607..2,750.234 rows=518,518 loops=1)

  • Hash Cond: ((bc.charge_id)::text = (ssm.charge_id)::text)
21. 671.300 671.300 ↓ 1.0 1,883,857 1

Seq Scan on bill_charge bc (cost=0.00..99,264.90 rows=1,881,490 width=20) (actual time=0.019..671.300 rows=1,883,857 loops=1)

22. 381.946 632.182 ↓ 1.0 518,518 1

Hash (cost=15,197.69..15,197.69 rows=517,969 width=59) (actual time=632.182..632.182 rows=518,518 loops=1)

  • Buckets: 2,048 Batches: 64 Memory Usage: 817kB
23. 250.236 250.236 ↓ 1.0 518,518 1

Seq Scan on store_sales_main ssm (cost=0.00..15,197.69 rows=517,969 width=59) (actual time=0.009..250.236 rows=518,518 loops=1)

24. 312.503 700.176 ↓ 1.0 668,168 1

Hash (cost=50,778.93..50,778.93 rows=667,993 width=24) (actual time=700.176..700.176 rows=668,168 loops=1)

  • Buckets: 2,048 Batches: 64 Memory Usage: 597kB
25. 387.673 387.673 ↓ 1.0 668,168 1

Seq Scan on bill b (cost=0.00..50,778.93 rows=667,993 width=24) (actual time=0.004..387.673 rows=668,168 loops=1)

26. 64.499 86.499 ↓ 1.0 108,341 1

Hash (cost=1,978.64..1,978.64 rows=108,264 width=18) (actual time=86.499..86.499 rows=108,341 loops=1)

  • Buckets: 2,048 Batches: 8 Memory Usage: 670kB
27. 22.000 22.000 ↓ 1.0 108,341 1

Seq Scan on store_retail_customers src (cost=0.00..1,978.64 rows=108,264 width=18) (actual time=0.003..22.000 rows=108,341 loops=1)

28. 0.520 1.091 ↑ 1.0 2,115 1

Hash (cost=68.56..68.56 rows=2,115 width=26) (actual time=1.091..1.091 rows=2,115 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 124kB
29. 0.571 0.571 ↑ 1.0 2,115 1

Seq Scan on bill_claim pbcl (cost=0.00..68.56 rows=2,115 width=26) (actual time=0.005..0.571 rows=2,115 loops=1)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 10
30. 0.003 0.017 ↑ 1.0 10 1

Hash (cost=9.33..9.33 rows=10 width=26) (actual time=0.017..0.017 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
31. 0.014 0.014 ↑ 1.0 10 1

Index Scan using bill_claim_priority_idx on bill_claim sbcl (cost=0.28..9.33 rows=10 width=26) (actual time=0.008..0.014 rows=10 loops=1)

  • Index Cond: (priority = 2)
32. 0.005 0.009 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=6) (actual time=0.009..0.009 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
33. 0.004 0.004 ↑ 1.0 4 1

Seq Scan on visit_type_names vn (cost=0.00..1.04 rows=4 width=6) (actual time=0.002..0.004 rows=4 loops=1)

34. 0.276 0.585 ↑ 1.0 993 1

Hash (cost=32.93..32.93 rows=993 width=21) (actual time=0.585..0.585 rows=993 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 54kB
35. 0.309 0.309 ↑ 1.0 993 1

Seq Scan on sales_claim_details pscd (cost=0.00..32.93 rows=993 width=21) (actual time=0.004..0.309 rows=993 loops=1)

36. 0.007 0.012 ↑ 1.0 15 1

Hash (cost=1.15..1.15 rows=15 width=8) (actual time=0.012..0.012 rows=15 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
37. 0.005 0.005 ↑ 1.0 15 1

Seq Scan on item_sub_groups isg (cost=0.00..1.15 rows=15 width=8) (actual time=0.004..0.005 rows=15 loops=1)

38. 0.269 0.574 ↑ 1.0 993 1

Hash (cost=32.93..32.93 rows=993 width=21) (actual time=0.574..0.574 rows=993 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 54kB
39. 0.305 0.305 ↑ 1.0 993 1

Seq Scan on sales_claim_details sscd (cost=0.00..32.93 rows=993 width=21) (actual time=0.002..0.305 rows=993 loops=1)

40. 286.288 1,346.486 ↓ 1.0 604,764 1

Hash (cost=62,877.82..62,877.82 rows=604,284 width=39) (actual time=1,346.486..1,346.486 rows=604,764 loops=1)

  • Buckets: 2,048 Batches: 64 Memory Usage: 661kB
41. 581.208 1,060.198 ↓ 1.0 604,764 1

Hash Left Join (cost=17,797.30..62,877.82 rows=604,284 width=39) (actual time=300.599..1,060.198 rows=604,764 loops=1)

  • Hash Cond: ((pr.mr_no)::text = (pd.mr_no)::text)
42. 178.712 178.712 ↓ 1.0 604,764 1

Seq Scan on patient_registration pr (cost=0.00..24,558.84 rows=604,284 width=21) (actual time=0.021..178.712 rows=604,764 loops=1)

43. 85.518 300.278 ↓ 1.0 198,013 1

Hash (cost=13,969.17..13,969.17 rows=197,930 width=30) (actual time=300.278..300.278 rows=198,013 loops=1)

  • Buckets: 2,048 Batches: 16 Memory Usage: 753kB
44. 153.834 214.760 ↓ 1.0 198,013 1

Hash Left Join (cost=1.34..13,969.17 rows=197,930 width=30) (actual time=0.032..214.760 rows=198,013 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
45. 60.916 60.916 ↓ 1.0 198,013 1

Seq Scan on patient_details pd (cost=0.00..11,246.30 rows=197,930 width=34) (actual time=0.013..60.916 rows=198,013 loops=1)

46. 0.007 0.010 ↑ 1.0 15 1

Hash (cost=1.15..1.15 rows=15 width=14) (actual time=0.010..0.010 rows=15 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
47. 0.003 0.003 ↑ 1.0 15 1

Seq Scan on salutation_master sm (cost=0.00..1.15 rows=15 width=14) (actual time=0.002..0.003 rows=15 loops=1)

Total runtime : 105,638.460 ms