explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yyFx : Optimization for: plan #Rm9Q

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.170 127.777 ↑ 1.0 1 1

Aggregate (cost=50,640.34..50,640.35 rows=1 width=32) (actual time=127.776..127.777 rows=1 loops=1)

2. 0.085 127.607 ↑ 2,560.0 50 1

Subquery Scan on res (cost=41,680.33..50,320.33 rows=128,000 width=156) (actual time=127.379..127.607 rows=50 loops=1)

3. 0.140 127.522 ↑ 2,560.0 50 1

GroupAggregate (cost=41,680.33..49,040.33 rows=128,000 width=164) (actual time=127.367..127.522 rows=50 loops=1)

  • Group Key: a.row_num, (((((to_char((a.slot_dt)::timestamp with time zone, 'mm/dd/yyyy'::text) || ' - '::text) || to_char((a.slot_start_time)::interval, 'hh12:mi PM'::text)) || ' to '::text) || to_char((a.slot_end_time)::interval, 'hh12:mi PM'::text))), (COALESCE((COALESCE(pd.display_name, concat(pd.first_name, ' ', pd.last_name, CASE WHEN (btrim(COALESCE(pd.title, ''::text)) = ''::text) THEN ''::text ELSE concat(' ', pd.title) END))), ''::text)), (COALESCE(f.facility_name, ''::character varying)), (COALESCE(atd.appt_type_desc, ''::character varying))
4. 0.117 127.382 ↑ 2,415.1 53 1

Sort (cost=41,680.33..42,000.33 rows=128,000 width=209) (actual time=127.347..127.382 rows=53 loops=1)

  • Sort Key: a.row_num, (((((to_char((a.slot_dt)::timestamp with time zone, 'mm/dd/yyyy'::text) || ' - '::text) || to_char((a.slot_start_time)::interval, 'hh12:mi PM'::text)) || ' to '::text) || to_char((a.slot_end_time)::interval, 'hh12:mi PM'::text))), (COALESCE((COALESCE(pd.display_name, concat(pd.first_name, ' ', pd.last_name, CASE WHEN (btrim(COALESCE(pd.title, ''::text)) = ''::text) THEN ''::text ELSE concat(' ', pd.title) END))), ''::text)), (COALESCE(f.facility_name, ''::character varying)), (COALESCE(atd.appt_type_desc, ''::character varying))
  • Sort Method: quicksort Memory: 32kB
5. 4.399 127.265 ↑ 2,415.1 53 1

Hash Left Join (cost=6,401.34..17,697.23 rows=128,000 width=209) (actual time=122.634..127.265 rows=53 loops=1)

  • Hash Cond: ((unnest(a.eliminating_preferences)) = ppf.preference_id)
6. 0.304 19.654 ↑ 2,415.1 53 1

ProjectSet (cost=537.80..1,302.78 rows=128,000 width=136) (actual time=19.106..19.654 rows=53 loops=1)

7. 0.121 19.350 ↑ 25.6 50 1

Hash Left Join (cost=537.80..621.18 rows=1,280 width=125) (actual time=19.084..19.350 rows=50 loops=1)

  • Hash Cond: (a.provider_id = ((pd.provider_id)::character varying(40))::text)
8. 0.085 1.330 ↑ 1.0 50 1

Hash Left Join (cost=38.30..44.76 rows=50 width=98) (actual time=1.140..1.330 rows=50 loops=1)

  • Hash Cond: (a.appt_type_id = (atd.appt_type_id)::text)
9. 0.096 0.831 ↑ 1.0 50 1

Hash Left Join (cost=26.56..32.89 rows=50 width=83) (actual time=0.714..0.831 rows=50 loops=1)

  • Hash Cond: (a.facility_id = (f.facility_id)::text)
10. 0.047 0.047 ↑ 1.0 50 1

Seq Scan on findslot_eliminated_slots_d a (cost=0.00..5.50 rows=50 width=61) (actual time=0.012..0.047 rows=50 loops=1)

11. 0.318 0.688 ↑ 1.0 396 1

Hash (cost=21.61..21.61 rows=396 width=29) (actual time=0.688..0.688 rows=396 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
12. 0.370 0.370 ↑ 1.0 396 1

Seq Scan on facility_d f (cost=0.00..21.61 rows=396 width=29) (actual time=0.007..0.370 rows=396 loops=1)

  • Filter: (eff_end_ts > now())
  • Rows Removed by Filter: 45
13. 0.195 0.414 ↑ 1.0 245 1

Hash (cost=8.68..8.68 rows=245 width=21) (actual time=0.414..0.414 rows=245 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
14. 0.219 0.219 ↑ 1.0 245 1

Seq Scan on appt_type_d atd (cost=0.00..8.68 rows=245 width=21) (actual time=0.006..0.219 rows=245 loops=1)

  • Filter: (eff_end_ts > now())
15. 4.032 17.899 ↑ 1.0 5,120 1

Hash (cost=435.50..435.50 rows=5,120 width=36) (actual time=17.898..17.899 rows=5,120 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 336kB
16. 4.449 13.867 ↑ 1.0 5,120 1

Hash Right Join (cost=362.11..435.50 rows=5,120 width=36) (actual time=9.454..13.867 rows=5,120 loops=1)

  • Hash Cond: ((system_code_detail.system_code_value)::text = COALESCE((pd.gender)::text, 'U'::text))
17. 0.013 0.041 ↑ 12.0 2 1

Bitmap Heap Scan on system_code_detail (cost=4.71..46.33 rows=24 width=4) (actual time=0.035..0.041 rows=2 loops=1)

  • Recheck Cond: (((system_code_id)::text = '200'::text) AND ((system_code_type)::text = '01'::text) AND (eff_end_ts > now()))
  • Filter: (is_active = 'Y'::bpchar)
  • Heap Blocks: exact=2
18. 0.028 0.028 ↑ 15.0 2 1

Bitmap Index Scan on system_code_detail_pkey (cost=0.00..4.71 rows=30 width=0) (actual time=0.027..0.028 rows=2 loops=1)

  • Index Cond: (((system_code_id)::text = '200'::text) AND ((system_code_type)::text = '01'::text) AND (eff_end_ts > now()))
19. 4.586 9.377 ↑ 1.0 5,120 1

Hash (cost=293.40..293.40 rows=5,120 width=38) (actual time=9.377..9.377 rows=5,120 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 346kB
20. 4.791 4.791 ↑ 1.0 5,120 1

Seq Scan on provider pd (cost=0.00..293.40 rows=5,120 width=38) (actual time=0.005..4.791 rows=5,120 loops=1)

21. 49.492 103.212 ↓ 1.0 57,607 1

Hash (cost=4,411.74..4,411.74 rows=57,584 width=77) (actual time=103.212..103.212 rows=57,607 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 3648kB
22. 53.720 53.720 ↓ 1.0 57,607 1

Seq Scan on provider_preferences ppf (cost=0.00..4,411.74 rows=57,584 width=77) (actual time=0.013..53.720 rows=57,607 loops=1)

  • Filter: (eff_end_ts > now())
  • Rows Removed by Filter: 309