explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OAo5

Settings
# exclusive inclusive rows x rows loops node
1. 4.485 847.045 ↓ 81.0 81 1

GroupAggregate (cost=5,835.56..5,835.59 rows=1 width=108) (actual time=842.326..847.045 rows=81 loops=1)

  • Group Key: rg.region_order, ra.rearea_order, rg.region_name, ra.rearea_name
  • Buffers: shared hit=303002
2. 4.425 842.560 ↓ 5,843.0 5,843 1

Sort (cost=5,835.56..5,835.57 rows=1 width=107) (actual time=842.304..842.560 rows=5,843 loops=1)

  • Sort Key: rg.region_order, ra.rearea_order, rg.region_name, ra.rearea_name
  • Sort Method: quicksort Memory: 1308kB
  • Buffers: shared hit=303002
3. 1.327 838.135 ↓ 5,843.0 5,843 1

Nested Loop Semi Join (cost=1.84..5,835.55 rows=1 width=107) (actual time=0.340..838.135 rows=5,843 loops=1)

  • Join Filter: (ml.dt_medcom_id = ml_1.dt_medcom_id)
  • Buffers: shared hit=303002
4. 2.257 790.056 ↓ 5,844.0 5,844 1

Nested Loop Semi Join (cost=1.13..5,818.25 rows=1 width=131) (actual time=0.313..790.056 rows=5,844 loops=1)

  • Join Filter: (ml.dt_medcom_id = msg.dt_medcom_id)
  • Buffers: shared hit=260490
5. 3.584 737.663 ↓ 6,267.0 6,267 1

Nested Loop (cost=0.83..5,801.26 rows=1 width=123) (actual time=0.268..737.663 rows=6,267 loops=1)

  • Join Filter: CASE WHEN (NULLIF((NULL::text), ''::text) IS NOT NULL) THEN (ra.rearea_code = ANY ((concat('{', regexp_replace((NULL::text), '[\+\s]'::text, ','::text, 'g'::text), '}'))::smallint[])) ELSE true END
  • Buffers: shared hit=215258
6. 14.942 734.079 ↓ 6,267.0 6,267 1

Nested Loop (cost=0.83..5,801.21 rows=1 width=125) (actual time=0.266..734.079 rows=6,267 loops=1)

  • Join Filter: (ra.region_code = rg.region_code)
  • Rows Removed by Join Filter: 50136
  • Buffers: shared hit=215258
7. 162.583 694.069 ↓ 6,267.0 6,267 1

Nested Loop (cost=0.83..5,799.97 rows=1 width=72) (actual time=0.259..694.069 rows=6,267 loops=1)

  • Join Filter: (concat(ok.okato_code_01, ok.okato_code_02) = (ra.okato_code)::text)
  • Rows Removed by Join Filter: 532695
  • Buffers: shared hit=208991
8. 30.532 318.408 ↓ 6,267.0 6,267 1

Nested Loop (cost=0.83..5,794.97 rows=1 width=38) (actual time=0.198..318.408 rows=6,267 loops=1)

  • Join Filter: (mc.cb_okato_id = ok.id)
  • Rows Removed by Join Filter: 248344
  • Buffers: shared hit=190190
9. 4.538 275.340 ↓ 6,268.0 6,268 1

Nested Loop (cost=0.83..5,790.04 rows=1 width=39) (actual time=0.185..275.340 rows=6,268 loops=1)

  • Buffers: shared hit=177649
10. 9.718 106.904 ↓ 487.8 11,707 1

Nested Loop Anti Join (cost=0.42..4,436.58 rows=24 width=16) (actual time=0.042..106.904 rows=11,707 loops=1)

  • Buffers: shared hit=67776
11. 30.751 30.751 ↓ 59.7 22,145 1

Seq Scan on dt_medlic ml (cost=0.00..2,986.80 rows=371 width=42) (actual time=0.020..30.751 rows=22,145 loops=1)

  • Filter: ((now())::timestamp(6) without time zone <@ tsrange(lic_date_start, COALESCE((lic_date_term)::timestamp without time zone, lic_date_end), '[]'::text))
  • Rows Removed by Filter: 51877
  • Buffers: shared hit=1318
12. 66.435 66.435 ↓ 0.0 0 22,145

Index Only Scan using dt_medlic_rzn_number_date_start_idx on dt_medlic_rzn r (cost=0.42..3.90 rows=1 width=21) (actual time=0.003..0.003 rows=0 loops=22,145)

  • Index Cond: ((number = (ml.lic_number)::text) AND (date_start = ml.lic_date_start))
  • Heap Fetches: 1
  • Buffers: shared hit=66458
13. 105.383 163.898 ↑ 1.0 1 11,707

Index Scan using idx_medcom_temp_id on dt_medcom mc (cost=0.42..56.38 rows=1 width=23) (actual time=0.013..0.014 rows=1 loops=11,707)

  • Index Cond: (temp_id = ml.dt_medcom_id)
  • Filter: (((now())::timestamp(6) without time zone <@ tsrange(bdate, edate, '[]'::text)) AND (SubPlan 2))
  • Rows Removed by Filter: 7
  • Buffers: shared hit=109873
14.          

SubPlan (for Index Scan)

15. 58.515 58.515 ↑ 1.0 1 11,703

CTE Scan on cte (cost=4.32..4.36 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=11,703)

  • Filter: ((dt_medcom_id IS NOT NULL) AND (LEAST((now())::timestamp(6) without time zone, dt) >= (now())::timestamp(6) without time zone))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=23511
16.          

CTE cte

17. 23.406 46.812 ↑ 1.0 1 11,703

GroupAggregate (cost=0.29..4.32 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=11,703)

  • Group Key: mi.dt_medcom_id
  • Buffers: shared hit=23511
18. 23.406 23.406 ↑ 1.0 1 11,703

Index Only Scan using medinc_indate on dt_medinc mi (cost=0.29..4.30 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=11,703)

  • Index Cond: (dt_medcom_id = mc.temp_id)
  • Heap Fetches: 7
  • Buffers: shared hit=23511
19. 12.536 12.536 ↑ 2.1 41 6,268

Seq Scan on cb_okato ok (cost=0.00..3.86 rows=86 width=15) (actual time=0.001..0.002 rows=41 loops=6,268)

  • Buffers: shared hit=12541
20. 213.078 213.078 ↓ 86.0 86 6,267

Seq Scan on cd_rearea ra (cost=0.00..4.98 rows=1 width=47) (actual time=0.001..0.034 rows=86 loops=6,267)

  • Filter: (((nsi_version)::text = '0'::text) AND ((now())::timestamp(6) without time zone <@ tsrange(bdate, edate, '[]'::text)))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=18801
21. 25.068 25.068 ↓ 9.0 9 6,267

Seq Scan on cd_region rg (cost=0.00..1.23 rows=1 width=57) (actual time=0.001..0.004 rows=9 loops=6,267)

  • Filter: (((nsi_version)::text = '0'::text) AND ((now())::timestamp(6) without time zone <@ tsrange(bdate, edate, '[]'::text)))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=6267
22. 0.000 0.000 ↑ 1.0 1 6,267

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=6,267)

23. 50.136 50.136 ↑ 1.0 1 6,267

Index Scan using medsig_in on dt_medsig msg (cost=0.29..16.98 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=6,267)

  • Index Cond: (dt_medcom_id = mc.temp_id)
  • Filter: (date_part('year'::text, now()) = date_part('year'::text, notification_year))
  • Rows Removed by Filter: 6
  • Buffers: shared hit=45232
24. 5.838 46.752 ↑ 8.0 1 5,844

Nested Loop (cost=0.72..17.20 rows=8 width=8) (actual time=0.008..0.008 rows=1 loops=5,844)

  • Buffers: shared hit=42512
25. 23.376 23.376 ↑ 1.0 1 5,844

Index Scan using medlic_in on dt_medlic ml_1 (cost=0.29..12.18 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=5,844)

  • Index Cond: (dt_medcom_id = msg.dt_medcom_id)
  • Filter: ((now())::timestamp(6) without time zone <@ tsrange(lic_date_start, COALESCE((lic_date_term)::timestamp without time zone, lic_date_end), '[]'::text))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=24915
26. 17.538 17.538 ↑ 27.0 1 5,846

Index Only Scan using uq_dt_medcat on dt_medcat cat (cost=0.42..4.75 rows=27 width=8) (actual time=0.003..0.003 rows=1 loops=5,846)

  • Index Cond: ((dt_medlic_id = ml_1.id) AND (cd_cartyp_id IS NOT NULL))
  • Heap Fetches: 11
  • Buffers: shared hit=17597
Planning time : 4.064 ms
Execution time : 847.403 ms