explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xJjF

Settings
# exclusive inclusive rows x rows loops node
1. 11.541 177.411 ↓ 4,082.0 8,164 1

Unique (cost=6,518.26..6,518.29 rows=2 width=278) (actual time=160.447..177.411 rows=8,164 loops=1)

2. 20.388 165.870 ↓ 4,082.0 8,164 1

Sort (cost=6,518.26..6,518.26 rows=2 width=278) (actual time=160.444..165.870 rows=8,164 loops=1)

  • Sort Key: pref.rule_id, t.facility_id, t.provider_id, pref.negate_rule_codition, pref.preference_id, pref.preference_desc
  • Sort Method: quicksort Memory: 1334kB
3. 28.184 145.482 ↓ 4,082.0 8,164 1

Hash Join (cost=1,391.20..6,518.25 rows=2 width=278) (actual time=6.956..145.482 rows=8,164 loops=1)

  • Hash Cond: ((pref.provider_id)::text = (t.provider_id)::text)
  • Join Filter: (((t.facility_id)::text = (pref.facility_id)::text) OR ((pref.facility_id)::text ~~* ANY ('{All,-All-}'::text[])))
4. 110.858 114.909 ↓ 508.2 24,393 1

Bitmap Heap Scan on provider_preferences pref (cost=1,374.51..6,499.91 rows=48 width=89) (actual time=4.549..114.909 rows=24,393 loops=1)

  • Recheck Cond: (end_time >= '23:00:00'::time without time zone)
  • Filter: ((start_time <= '01:00:00'::time without time zone) AND (preference_bgn_dt <= '2019-09-25'::date) AND (preference_end_dt >= '2019-10-30'::date) AND (enabled_for_call_schd ~~* 'Y'::text) AND (NOT (hashed SubPlan 1)) AND ((preference_action)::text = 'Eliminate'::text) AND ((can_be_overridden)::text = '0'::text) AND (eff_end_ts > now()) AND ((string_to_array(regexp_replace(regexp_replace(replace((days_of_week)::text, ' '::text, ''::text), ',$'::text, ''::text, 'g'::text), '^,'::text, ''::text, 'g'::text), ','::text) @> '{1,2,3,4,5,6,7}'::text[]) OR (btrim((COALESCE(days_of_week, ''::character varying))::text) = ANY ('{"",0,","}'::text[]))))
  • Rows Removed by Filter: 4666
  • Heap Blocks: exact=3309
5. 2.857 2.857 ↑ 1.0 29,059 1

Bitmap Index Scan on provider_preferences_provider_id_idx_1 (cost=0.00..1,338.78 rows=29,440 width=0) (actual time=2.856..2.857 rows=29,059 loops=1)

  • Index Cond: (end_time >= '23:00:00'::time without time zone)
6.          

SubPlan (forBitmap Heap Scan)

7. 0.520 1.194 ↑ 5.9 63 1

Hash Join (cost=11.52..34.79 rows=369 width=4) (actual time=0.409..1.194 rows=63 loops=1)

  • Hash Cond: ((rule_attribute_xref.attribute_name)::text = (appointment_attributes_d.attribute_name)::text)
8. 0.511 0.511 ↑ 1.0 667 1

Seq Scan on rule_attribute_xref (cost=0.00..21.47 rows=667 width=18) (actual time=0.006..0.511 rows=667 loops=1)

  • Filter: (eff_end_ts > now())
  • Rows Removed by Filter: 31
9. 0.030 0.163 ↑ 2.6 36 1

Hash (cost=10.36..10.36 rows=93 width=16) (actual time=0.162..0.163 rows=36 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
10. 0.133 0.133 ↑ 2.6 36 1

Seq Scan on appointment_attributes_d (cost=0.00..10.36 rows=93 width=16) (actual time=0.039..0.133 rows=36 loops=1)

  • Filter: (("position"(attribute_answer_query, '@date@'::text) > 0) OR ("position"(attribute_answer_query, '@time@'::text) > 0))
  • Rows Removed by Filter: 132
11. 1.229 2.389 ↓ 6.4 1,611 1

Hash (cost=13.53..13.53 rows=253 width=196) (actual time=2.388..2.389 rows=1,611 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 81kB
12. 1.160 1.160 ↓ 6.4 1,611 1

Seq Scan on _template_base00 t (cost=0.00..13.53 rows=253 width=196) (actual time=0.010..1.160 rows=1,611 loops=1)

Planning time : 0.546 ms
Execution time : 185.218 ms