explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C5Dq

Settings
# exclusive inclusive rows x rows loops node
1. 3.079 90.745 ↑ 2.6 1,611 1

Unique (cost=22,505.34..22,537.25 rows=4,254 width=135) (actual time=85.866..90.745 rows=1,611 loops=1)

2. 7.035 87.666 ↑ 1.5 2,834 1

Sort (cost=22,505.34..22,515.98 rows=4,254 width=135) (actual time=85.863..87.666 rows=2,834 loops=1)

  • Sort Key: cal.facility_id, cal.provider_id
  • Sort Method: quicksort Memory: 229kB
3. 8.137 80.631 ↑ 1.5 2,834 1

Hash Right Join (cost=793.28..22,248.94 rows=4,254 width=135) (actual time=43.661..80.631 rows=2,834 loops=1)

  • Hash Cond: ((((provider.provider_id)::character varying(40)))::text = (cal.provider_id)::text)
4. 12.368 28.950 ↑ 59.3 8,639 1

Result (cost=0.00..14,373.40 rows=512,000 width=364) (actual time=0.025..28.950 rows=8,639 loops=1)

5. 12.916 16.582 ↑ 59.3 8,639 1

ProjectSet (cost=0.00..2,853.40 rows=512,000 width=36) (actual time=0.020..16.582 rows=8,639 loops=1)

6. 3.666 3.666 ↑ 1.0 5,120 1

Seq Scan on provider (cost=0.00..242.20 rows=5,120 width=10) (actual time=0.007..3.666 rows=5,120 loops=1)

7. 1.322 43.544 ↓ 38.4 1,611 1

Hash (cost=792.76..792.76 rows=42 width=7) (actual time=43.544..43.544 rows=1,611 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 81kB
8. 4.064 42.222 ↓ 38.4 1,611 1

Nested Loop Semi Join (cost=331.25..792.76 rows=42 width=7) (actual time=7.881..42.222 rows=1,611 loops=1)

9. 5.101 31.597 ↓ 37.7 2,187 1

Nested Loop (cost=330.98..774.03 rows=58 width=7) (actual time=7.866..31.597 rows=2,187 loops=1)

10. 3.166 9.396 ↓ 37.3 1,900 1

HashAggregate (cost=330.55..331.06 rows=51 width=4) (actual time=7.838..9.396 rows=1,900 loops=1)

  • Group Key: ((pd.provider_id)::character varying(40))::text
11. 3.121 6.230 ↓ 37.3 1,900 1

Hash Left Join (cost=46.63..330.43 rows=51 width=4) (actual time=0.048..6.230 rows=1,900 loops=1)

  • Hash Cond: (COALESCE((pd.gender)::text, 'U'::text) = (system_code_detail.system_code_value)::text)
12. 3.081 3.081 ↓ 37.3 1,900 1

Seq Scan on provider pd (cost=0.00..280.60 rows=51 width=6) (actual time=0.012..3.081 rows=1,900 loops=1)

  • Filter: (((CASE WHEN enable_for_dash THEN 'Y'::text ELSE 'N'::text END)::character(1) = 'Y'::bpchar) OR ((CASE WHEN enable_for_dash THEN 'Y'::text ELSE 'N'::text END)::character(1) IS NULL))
  • Rows Removed by Filter: 3220
13. 0.005 0.028 ↑ 12.0 2 1

Hash (cost=46.33..46.33 rows=24 width=4) (actual time=0.027..0.028 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.010 0.023 ↑ 12.0 2 1

Bitmap Heap Scan on system_code_detail (cost=4.71..46.33 rows=24 width=4) (actual time=0.019..0.023 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
15. 0.013 0.013 ↑ 15.0 2 1

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

  • Index Cond: (((system_code_id)::text = '200'::text) AND ((system_code_type)::text = '01'::text) AND (eff_end_ts > now()))
16. 17.100 17.100 ↑ 1.0 1 1,900

Index Only Scan using provider_facility_xref_pkey on provider_facility_xref cal (cost=0.42..8.68 rows=1 width=7) (actual time=0.008..0.009 rows=1 loops=1,900)

  • Index Cond: ((provider_id = ((pd.provider_id)::character varying(40))::text) AND (service_id = '1015'::text))
  • Heap Fetches: 2187
17. 6.561 6.561 ↑ 1.0 1 2,187

Index Scan using facility_d_pkey on facility_d (cost=0.28..0.32 rows=1 width=3) (actual time=0.003..0.003 rows=1 loops=2,187)

  • Index Cond: (((facility_id)::text = (cal.facility_id)::text) AND (now() <= eff_end_ts))
  • Filter: ((can_be_scheduled = 'Y'::bpchar) AND (now() >= eff_bgn_ts))
  • Rows Removed by Filter: 0