explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fUPn

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 29,574.668 ↓ 0.0 0 1

Finalize GroupAggregate (cost=1,937,427.06..1,939,476.15 rows=13,033 width=49) (actual time=29,574.667..29,574.668 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=228918 read=31701
  • I/O Timings: read=28163.626
2. 4.700 29,578.333 ↓ 0.0 0 1

Gather Merge (cost=1,937,427.06..1,939,085.18 rows=13,032 width=49) (actual time=29,574.666..29,578.333 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=228918 read=31701
  • I/O Timings: read=28163.626
3. 0.003 29,573.633 ↓ 0.0 0 1

Partial GroupAggregate (cost=1,936,427.00..1,936,532.88 rows=3,258 width=49) (actual time=29,573.633..29,573.633 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=228918 read=31701
  • I/O Timings: read=28163.626
4. 0.015 29,573.630 ↓ 0.0 0 1

Sort (cost=1,936,427.00..1,936,435.14 rows=3,258 width=41) (actual time=29,573.630..29,573.630 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.rate_category_id, ev.form_id, ev.date, r.indicator_id, r.value
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=228918 read=31701
  • I/O Timings: read=28163.626
5. 0.038 29,573.615 ↓ 0.0 0 1

Hash Join (cost=111,300.52..1,936,236.90 rows=3,258 width=41) (actual time=29,573.615..29,573.615 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
  • Hash Cond: (ev.vendor_id = ven.id)
  • Buffers: shared hit=228918 read=31701
  • I/O Timings: read=28163.626
6. 21.676 29,568.192 ↓ 0.0 0 1

Nested Loop (cost=110,904.10..1,935,831.93 rows=3,258 width=45) (actual time=29,568.192..29,568.192 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=228642 read=31701
  • I/O Timings: read=28163.626
7. 79.140 6,368.816 ↓ 3.7 50,940 1

Parallel Bitmap Heap Scan on base.hershel_events ev (cost=110,903.53..165,665.91 rows=13,679 width=16) (actual time=6,293.341..6,368.816 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=7736 read=25945
  • I/O Timings: read=5221.875
8. 19.352 6,289.676 ↓ 0.0 0 1

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

  • Buffers: shared hit=15 read=24781
  • I/O Timings: read=5214.191
9. 2,340.974 2,340.974 ↓ 1.0 991,931 1

Bitmap Index Scan on idx_hershel_events_on_authority_reg_id (cost=0.00..11,066.55 rows=964,931 width=0) (actual time=2,340.974..2,340.974 rows=991,931 loops=1)

  • Index Cond: ((ev.authority_reg_id)::text = '10001746905'::text)
  • Buffers: shared hit=14 read=3840
  • I/O Timings: read=2203.872
10. 3,929.350 3,929.350 ↑ 1.0 7,663,586 1

Bitmap Index Scan on idx_hershel_events_on_date (cost=0.00..99,809.37 rows=7,779,580 width=0) (actual time=3,929.350..3,929.350 rows=7,663,586 loops=1)

  • Index Cond: ((ev.date >= '2018-01-01'::date) AND (ev.date <= '2018-02-28'::date))
  • Buffers: shared hit=1 read=20941
  • I/O Timings: read=3010.319
11. 23,177.700 23,177.700 ↓ 0.0 0 50,940

Index Scan using idx_hershel_rates_event_id on base.hershel_rates r (cost=0.57..129.21 rows=20 width=37) (actual time=0.455..0.455 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 = '10000011206'::text)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=220906 read=5756
  • I/O Timings: read=22941.750
12. 2.863 5.385 ↓ 1.0 13,024 1

Hash (cost=233.63..233.63 rows=13,023 width=4) (actual time=5.384..5.385 rows=13,024 loops=1)

  • Output: ven.id
  • Buckets: 16384 Batches: 1 Memory Usage: 586kB
  • Buffers: shared hit=276
13. 2.522 2.522 ↓ 1.0 13,024 1

Index Only Scan using hershel_vendors_pkey on base.hershel_vendors ven (cost=0.29..233.63 rows=13,023 width=4) (actual time=0.054..2.522 rows=13,024 loops=1)

  • Output: ven.id
  • Heap Fetches: 708
  • Buffers: shared hit=276