explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Bj8p

Settings
# exclusive inclusive rows x rows loops node
1. 26.451 828,571.246 ↓ 175,686.0 175,686 1

Unique (cost=578,926.04..578,926.05 rows=1 width=6) (actual time=828,503.385..828,571.246 rows=175,686 loops=1)

2. 376.138 828,544.795 ↓ 175,686.0 175,686 1

Sort (cost=578,926.04..578,926.05 rows=1 width=6) (actual time=828,503.384..828,544.795 rows=175,686 loops=1)

  • Sort Key: iploru_obx_latest16.iploru_obx_id, hl7_obx7.obx_11
  • Sort Method: external merge  Disk: 2,736kB
3. 924.999 828,168.657 ↓ 175,686.0 175,686 1

Nested Loop (cost=301,068.87..578,926.03 rows=1 width=6) (actual time=14,239.362..828,168.657 rows=175,686 loops=1)

  • Join Filter: ((iploru_obx13.set_id = hl7_obx7.obx_1) AND (hl7_msh15.msg_id = hl7_obx7.msg_id) AND ((iploru_obx13.obs_id)::text = (hl7_obx7.obx_3_1)::text))
  • Rows Removed by Join Filter: 7,375,780
4. 204.192 503,278.674 ↓ 175,686.0 175,686 1

Nested Loop (cost=301,068.30..578,904.47 rows=1 width=65) (actual time=14,229.120..503,278.674 rows=175,686 loops=1)

  • Join Filter: ((iploru9.filler_order_number)::text = (hl7_obr10.obr_3)::text)
  • Rows Removed by Join Filter: 126
5. 267.099 346,713.942 ↓ 175,686.0 175,686 1

Nested Loop (cost=301,067.87..578,902.13 rows=1 width=87) (actual time=14,222.095..346,713.942 rows=175,686 loops=1)

6. 166.819 198,519.231 ↓ 87,843.0 175,686 1

Nested Loop (cost=301,067.44..578,897.53 rows=2 width=79) (actual time=14,215.785..198,519.231 rows=175,686 loops=1)

7. 4,239.423 28,464.050 ↓ 13,514.3 175,686 1

Hash Join (cost=301,067.01..578,864.64 rows=13 width=49) (actual time=14,207.229..28,464.050 rows=175,686 loops=1)

  • Hash Cond: ((iploru_obx_latest16.latest_obx_date = hl7_msh15.msh_7) AND (msg_to_iploru8.msg_id = hl7_msh15.msg_id))
8. 675.081 13,579.361 ↑ 3.1 175,686 1

Hash Join (cost=89,011.32..327,164.28 rows=537,436 width=49) (actual time=3,483.927..13,579.361 rows=175,686 loops=1)

  • Hash Cond: (iploru_obx13.iploru_id = msg_to_iploru8.iploru_id)
9. 273.945 12,036.527 ↑ 3.0 175,686 1

Hash Join (cost=53,964.11..221,513.78 rows=528,225 width=41) (actual time=2,615.171..12,036.527 rows=175,686 loops=1)

  • Hash Cond: (iploru_obx13.iploru_obx_id = iploru_obx_latest16.iploru_obx_id)
10. 150.753 9,158.895 ↑ 8.6 175,686 1

Hash Join (cost=2.19..127,485.73 rows=1,518,902 width=33) (actual time=1.649..9,158.895 rows=175,686 loops=1)

  • Hash Cond: ((iploru_obx13.obs_id)::text = (obx_date_parameters0.obx_parameter_name)::text)
11. 9,008.124 9,008.124 ↑ 2.9 1,096,577 1

Seq Scan on iploru_obx iploru_obx13 (cost=0.00..100,472.48 rows=3,152,543 width=22) (actual time=1.598..9,008.124 rows=1,096,577 loops=1)

  • Filter: ((iploru_id IS NOT NULL) AND (set_id IS NOT NULL) AND (obs_id IS NOT NULL) AND (iploru_obx_id IS NOT NULL))
  • Rows Removed by Filter: 27
12. 0.006 0.018 ↑ 1.0 53 1

Hash (cost=1.53..1.53 rows=53 width=11) (actual time=0.017..0.018 rows=53 loops=1)

  • Buckets: 1,024  Batches: 1  Memory Usage: 11kB
13. 0.012 0.012 ↑ 1.0 53 1

Seq Scan on obx_date_parameters obx_date_parameters0 (cost=0.00..1.53 rows=53 width=11) (actual time=0.007..0.012 rows=53 loops=1)

  • Filter: (obx_parameter_name IS NOT NULL)
14. 178.863 2,603.687 ↓ 1.0 1,096,604 1

Hash (cost=34,903.06..34,903.06 rows=1,096,389 width=12) (actual time=2,603.687..2,603.687 rows=1,096,604 loops=1)

  • Buckets: 131,072  Batches: 32  Memory Usage: 2,630kB
15. 2,424.824 2,424.824 ↓ 1.0 1,096,604 1

Seq Scan on iploru_obx_latest iploru_obx_latest16 (cost=0.00..34,903.06 rows=1,096,389 width=12) (actual time=2.327..2,424.824 rows=1,096,604 loops=1)

  • Filter: ((latest_obx_date IS NOT NULL) AND (iploru_obx_id IS NOT NULL) AND (latest_obx_date > '2000-12-31 23:59:59.9'::timestamp without time zone) AND ('2050-01-01 00:00:00'::timestamp without time zone > latest_obx_date))
16. 186.816 867.753 ↓ 1.0 1,136,730 1

Hash (cost=16,397.20..16,397.20 rows=1,136,720 width=8) (actual time=867.753..867.753 rows=1,136,730 loops=1)

  • Buckets: 131,072  Batches: 16  Memory Usage: 3,819kB
17. 680.937 680.937 ↓ 1.0 1,136,730 1

Seq Scan on msg_to_iploru msg_to_iploru8 (cost=0.00..16,397.20 rows=1,136,720 width=8) (actual time=0.015..680.937 rows=1,136,730 loops=1)

  • Filter: ((iploru_id IS NOT NULL) AND (msg_id IS NOT NULL))
18. 1,021.997 10,645.266 ↓ 1.0 4,318,215 1

Hash (cost=126,200.35..126,200.35 rows=4,318,023 width=16) (actual time=10,645.265..10,645.266 rows=4,318,215 loops=1)

  • Buckets: 131,072  Batches: 128  Memory Usage: 2,594kB
19. 9,623.269 9,623.269 ↓ 1.0 4,318,215 1

Seq Scan on hl7_msh hl7_msh15 (cost=0.00..126,200.35 rows=4,318,023 width=16) (actual time=0.016..9,623.269 rows=4,318,215 loops=1)

  • Filter: ((msh_7 IS NOT NULL) AND (msg_id IS NOT NULL) AND (msh_id IS NOT NULL) AND (msh_7 > '2000-12-31 23:59:59.9'::timestamp without time zone) AND ('2050-01-01 00:00:00'::timestamp without time zone > msh_7))
20. 169,888.362 169,888.362 ↑ 1.0 1 175,686

Index Scan using iploru_pkey on iploru iploru9 (cost=0.43..2.52 rows=1 width=42) (actual time=0.923..0.967 rows=1 loops=175,686)

  • Index Cond: ((iploru_id = iploru_obx13.iploru_id) AND (iploru_id IS NOT NULL))
  • Filter: (filler_order_number IS NOT NULL)
21. 147,927.612 147,927.612 ↑ 1.0 1 175,686

Index Scan using hl7_msg_pkey on hl7_msg hl7_msg3 (cost=0.43..2.29 rows=1 width=8) (actual time=0.841..0.842 rows=1 loops=175,686)

  • Index Cond: ((msg_id = hl7_msh15.msg_id) AND (msg_id IS NOT NULL))
  • Filter: ((feed)::text = 'IPLORU'::text)
22. 156,360.540 156,360.540 ↑ 2.0 1 175,686

Index Scan using hl7_obr_msg_id on hl7_obr hl7_obr10 (cost=0.43..2.31 rows=2 width=42) (actual time=0.888..0.890 rows=1 loops=175,686)

  • Index Cond: ((msg_id = hl7_msh15.msg_id) AND (msg_id IS NOT NULL))
  • Filter: ((obr_3 IS NOT NULL) AND (obr_id IS NOT NULL))
23. 323,964.984 323,964.984 ↓ 43.0 43 175,686

Index Scan using hl7_obx_obr_id_idx on hl7_obx hl7_obx7 (cost=0.57..21.55 rows=1 width=32) (actual time=1.359..1.844 rows=43 loops=175,686)

  • Index Cond: ((obr_id = hl7_obr10.obr_id) AND (obr_id IS NOT NULL))
  • Filter: ((obx_1 IS NOT NULL) AND (msg_id IS NOT NULL) AND (obx_3_1 IS NOT NULL) AND (obx_id IS NOT NULL) AND (obx_11 IS NOT NULL) AND (hl7_obr10.msg_id = msg_id))