explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iVVn

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 18,564.263 ↑ 38.5 13 1

Limit (cost=93,015.93..93,017.18 rows=500 width=104) (actual time=18,564.261..18,564.263 rows=13 loops=1)

2.          

Initplan (for Limit)

3. 0.011 0.011 ↑ 1.0 1 1

Result (cost=0.00..0.03 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=1)

4. 0.003 0.003 ↑ 1.0 1 1

Result (cost=0.00..0.03 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)

5. 0.061 18,564.260 ↑ 785.3 13 1

Sort (cost=93,015.87..93,041.39 rows=10,209 width=104) (actual time=18,564.259..18,564.260 rows=13 loops=1)

  • Sort Key: (to_char((date_trunc('month'::text, v.actual_discharge_date)), 'YYYY-MM'::text)) DESC
  • Sort Method: quicksort Memory: 26kB
6. 454.336 18,564.199 ↑ 785.3 13 1

GroupAggregate (cost=91,585.24..92,507.17 rows=10,209 width=104) (actual time=18,057.783..18,564.199 rows=13 loops=1)

  • Group Key: (date_trunc('month'::text, v.actual_discharge_date))
7. 466.764 18,109.863 ↓ 73.4 753,277 1

Sort (cost=91,585.24..91,610.89 rows=10,261 width=38) (actual time=18,001.181..18,109.863 rows=753,277 loops=1)

  • Sort Key: (date_trunc('month'::text, v.actual_discharge_date))
  • Sort Method: external merge Disk: 39824kB
8. 10,979.419 17,643.099 ↓ 73.4 753,277 1

Bitmap Heap Scan on visit_partial v (cost=51,322.01..90,901.60 rows=10,261 width=38) (actual time=6,695.310..17,643.099 rows=753,277 loops=1)

  • Recheck Cond: ((actual_discharge_date >= $0) AND (actual_discharge_date < $1) AND (facility_filter_name = ANY ('{hcasatx.COCMHL,hcasatx.COCAY,hcasatx.COCMT,hcasatx.COCVO,hcasatx.COCSN,hcasatx.COCMSO,hcasatx.COCMTA,hcasatx.COCWM}'::text[])))
  • Rows Removed by Index Recheck: 8415254
  • Heap Blocks: exact=74439 lossy=582249
9. 298.555 6,663.680 ↓ 0.0 0 1

BitmapAnd (cost=51,322.01..51,322.01 rows=10,261 width=0) (actual time=6,663.680..6,663.680 rows=0 loops=1)

10. 5,919.006 5,919.006 ↓ 73.7 17,664,241 1

Bitmap Index Scan on idx_visit_actual_discharge_date (cost=0.00..5,029.32 rows=239,676 width=0) (actual time=5,919.006..5,919.006 rows=17,664,241 loops=1)

  • Index Cond: ((actual_discharge_date >= $0) AND (actual_discharge_date < $1))
11. 446.119 446.119 ↓ 1.0 2,078,276 1

Bitmap Index Scan on idx_visit_facility_filter_name (cost=0.00..46,287.30 rows=2,052,102 width=0) (actual time=446.119..446.119 rows=2,078,276 loops=1)

  • Index Cond: (facility_filter_name = ANY ('{hcasatx.COCMHL,hcasatx.COCAY,hcasatx.COCMT,hcasatx.COCVO,hcasatx.COCSN,hcasatx.COCMSO,hcasatx.COCMTA,hcasatx.COCWM}'::text[]))