explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 38zm

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 16,341.722 ↓ 0.0 0 1

Finalize GroupAggregate (cost=1,941,240.29..1,941,342.17 rows=647 width=49) (actual time=16,341.722..16,341.722 rows=0 loops=1)

  • Output: r.service_reg_id, r.rate_category_id, ev.form_id, ev.date, r.indicator_id, r.value, count(r.value), r.authority_reg_id
  • Group Key: r.authority_reg_id, r.service_reg_id, r.rate_category_id, ev.form_id, ev.date, r.indicator_id, r.value
  • Buffers: shared hit=228581 read=31762
  • I/O Timings: read=14943.338
2. 3.955 16,345.094 ↓ 0.0 0 1

Gather Merge (cost=1,941,240.29..1,941,322.74 rows=648 width=49) (actual time=16,341.720..16,345.094 rows=0 loops=1)

  • Output: r.service_reg_id, r.rate_category_id, ev.form_id, ev.date, r.indicator_id, r.value, r.authority_reg_id, (PARTIAL count(r.value))
  • Workers Planned: 4
  • Workers Launched: 0
  • Buffers: shared hit=228581 read=31762
  • I/O Timings: read=14943.338
3. 0.002 16,341.139 ↓ 0.0 0 1

Partial GroupAggregate (cost=1,940,240.23..1,940,245.49 rows=162 width=49) (actual time=16,341.139..16,341.139 rows=0 loops=1)

  • Output: r.service_reg_id, r.rate_category_id, ev.form_id, ev.date, r.indicator_id, r.value, r.authority_reg_id, PARTIAL count(r.value)
  • Group Key: r.authority_reg_id, r.service_reg_id, r.rate_category_id, ev.form_id, ev.date, r.indicator_id, r.value
  • Buffers: shared hit=228581 read=31762
  • I/O Timings: read=14943.338
4. 0.011 16,341.137 ↓ 0.0 0 1

Sort (cost=1,940,240.23..1,940,240.63 rows=162 width=41) (actual time=16,341.137..16,341.137 rows=0 loops=1)

  • Output: r.service_reg_id, r.rate_category_id, ev.form_id, ev.date, r.indicator_id, r.value, r.authority_reg_id
  • Sort Key: r.authority_reg_id, r.service_reg_id, r.rate_category_id, ev.form_id, ev.date, r.indicator_id, r.value
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=228581 read=31762
  • I/O Timings: read=14943.338
5. 0.001 16,341.126 ↓ 0.0 0 1

Nested Loop (cost=110,904.08..1,940,234.28 rows=162 width=41) (actual time=16,341.126..16,341.126 rows=0 loops=1)

  • Output: r.service_reg_id, r.rate_category_id, ev.form_id, ev.date, r.indicator_id, r.value, r.authority_reg_id
  • Inner Unique: true
  • Buffers: shared hit=228581 read=31762
  • I/O Timings: read=14943.338
6. 39.570 16,341.125 ↓ 0.0 0 1

Nested Loop (cost=110,903.79..1,940,095.74 rows=162 width=45) (actual time=16,341.125..16,341.125 rows=0 loops=1)

  • Output: r.service_reg_id, r.rate_category_id, r.indicator_id, r.value, r.authority_reg_id, ev.form_id, ev.date, ev.vendor_id
  • Buffers: shared hit=228581 read=31762
  • I/O Timings: read=14943.338
7. 99.117 6,164.495 ↓ 3.7 50,940 1

Parallel Bitmap Heap Scan on base.hershel_events ev (cost=110,903.22..165,665.60 rows=13,679 width=16) (actual time=6,077.685..6,164.495 rows=50,940 loops=1)

  • Output: ev.id, ev.packet_id, ev.vendor_id, ev.foreign_id, ev.authority_reg_id, ev.authority_name, ev.service_reg_id, ev.service_name, ev.user_id, ev.user_foreign_id, ev.email, ev.mobile, ev.date, ev.created_at, ev.updated_at, ev.okato, ev.procedure_reg_id, ev.procedure_name, ev.status, ev.commentary, ev.store, ev.form_id, ev.spot_id, ev.cashed_rate, ev.conversed_at, ev.authority_foreign_id, ev.review_status, ev.contact_method
  • Recheck Cond: (((ev.authority_reg_id)::text = '10001746905'::text) AND (ev.date >= '2018-01-01'::date) AND (ev.date <= '2018-02-28'::date))
  • Heap Blocks: exact=8885
  • Buffers: shared hit=7778 read=25903
  • I/O Timings: read=5020.698
8. 19.738 6,065.378 ↓ 0.0 0 1

BitmapAnd (cost=110,903.22..110,903.22 rows=54,716 width=0) (actual time=6,065.378..6,065.378 rows=0 loops=1)

  • Buffers: shared hit=93 read=24703
  • I/O Timings: read=4990.407
9. 1,718.934 1,718.934 ↓ 1.0 991,930 1

Bitmap Index Scan on idx_hershel_events_on_authority_reg_id (cost=0.00..11,066.53 rows=964,928 width=0) (actual time=1,718.934..1,718.934 rows=991,930 loops=1)

  • Index Cond: ((ev.authority_reg_id)::text = '10001746905'::text)
  • Buffers: shared hit=32 read=3822
  • I/O Timings: read=1585.643
10. 4,326.706 4,326.706 ↑ 1.0 7,663,586 1

Bitmap Index Scan on idx_hershel_events_on_date (cost=0.00..99,809.08 rows=7,779,551 width=0) (actual time=4,326.706..4,326.706 rows=7,663,586 loops=1)

  • Index Cond: ((ev.date >= '2018-01-01'::date) AND (ev.date <= '2018-02-28'::date))
  • Buffers: shared hit=61 read=20881
  • I/O Timings: read=3404.765
11. 10,137.060 10,137.060 ↓ 0.0 0 50,940

Index Scan using idx_hershel_rates_event_id on base.hershel_rates r (cost=0.57..129.71 rows=1 width=37) (actual time=0.199..0.199 rows=0 loops=50,940)

  • Output: r.id, r.value, r.indicator_id, r.event_id, r.created_at, r.updated_at, r.indicator_value_id, r.service_reg_id, r.authority_reg_id, r.received_date, r.signature, r.vendor_id, r.packet_id, r.""real"", r.rate_category_id
  • Index Cond: (r.event_id = ev.id)
  • Filter: (((r.service_reg_id)::text || ''::text) = '10000011206'::text)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=220803 read=5859
  • I/O Timings: read=9922.640
12. 0.000 0.000 ↓ 0.0 0

Index Only Scan using hershel_vendors_pkey on base.hershel_vendors ven (cost=0.29..0.86 rows=1 width=4) (never executed)

  • Output: ven.id
  • Index Cond: (ven.id = ev.vendor_id)
  • Heap Fetches: 0