explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 78Nl

Settings
# exclusive inclusive rows x rows loops node
1. 1,194.517 7,136.683 ↑ 4.1 169,798 1

GroupAggregate (cost=321,948.36..359,985.92 rows=691,592 width=221) (actual time=5,838.758..7,136.683 rows=169,798 loops=1)

  • Output: o.event_uid, o.chain_hash, o.init_point_id, timezone('UTC'::text, o.date_from), (timezone('MSK'::text, o.date_from))::time without time zone, array_agg(DISTINCT o.point_from_id), array_agg(DISTINCT o.point_to_id), o.organization_id, min(o.created_at), max(o.updated_at), o.is_imported, o.is_fbs, o.autorepeat_hash, o.date_from
  • Group Key: o.event_uid, o.chain_hash, o.date_from, o.organization_id, o.init_point_id, o.is_imported, o.is_fbs, o.autorepeat_hash
  • Buffers: shared hit=28,389 read=177,677
2. 2,340.020 5,942.166 ↑ 1.0 666,886 1

Sort (cost=321,948.36..323,677.34 rows=691,592 width=157) (actual time=5,838.624..5,942.166 rows=666,886 loops=1)

  • Output: o.event_uid, o.chain_hash, o.init_point_id, o.organization_id, o.is_imported, o.is_fbs, o.autorepeat_hash, o.date_from, o.point_from_id, o.point_to_id, o.created_at, o.updated_at
  • Sort Key: o.event_uid, o.chain_hash, o.date_from, o.organization_id, o.init_point_id, o.is_imported, o.is_fbs, o.autorepeat_hash
  • Sort Method: quicksort Memory: 201,718kB
  • Buffers: shared hit=28,389 read=177,677
3. 248.392 3,602.146 ↑ 1.0 666,886 1

Hash Join (cost=4,533.98..254,865.45 rows=691,592 width=157) (actual time=195.616..3,602.146 rows=666,886 loops=1)

  • Output: o.event_uid, o.chain_hash, o.init_point_id, o.organization_id, o.is_imported, o.is_fbs, o.autorepeat_hash, o.date_from, o.point_from_id, o.point_to_id, o.created_at, o.updated_at
  • Inner Unique: true
  • Hash Cond: (o.point_to_id = pt.id)
  • Buffers: shared hit=28,389 read=177,677
4. 330.374 3,331.953 ↑ 1.0 666,886 1

Hash Join (cost=2,266.99..250,782.87 rows=691,592 width=157) (actual time=173.600..3,331.953 rows=666,886 loops=1)

  • Output: o.event_uid, o.chain_hash, o.init_point_id, o.date_from, o.point_from_id, o.point_to_id, o.organization_id, o.created_at, o.updated_at, o.is_imported, o.is_fbs, o.autorepeat_hash
  • Inner Unique: true
  • Hash Cond: (o.point_from_id = p.id)
  • Buffers: shared hit=27,250 read=177,677
5. 2,984.502 2,984.502 ↑ 1.0 666,886 1

Seq Scan on public.org_schedule_calendar o (cost=0.00..246,700.29 rows=691,592 width=157) (actual time=156.220..2,984.502 rows=666,886 loops=1)

  • Output: o.id, o.org_schedule_id, o.organization_id, o.point_from_id, o.point_to_id, o.date_from, o.date_to, o.created_at, o.updated_at, o.is_deleted, o.is_generated, o.is_imported, o.import_uid, o.point_from_lozon_id, o.point_to_lozon_id, o.organization_lozon_contract_id, o.is_active, o.event_uid, o.chain_hash, o.init_point_id, o.is_fbs, o.autorepeat_hash
  • Filter: (o.is_active AND (o.event_uid IS NOT NULL) AND (o.init_point_id IS NOT NULL) AND (o.date_from >= (CURRENT_TIMESTAMP - '30 days'::interval)))
  • Rows Removed by Filter: 1,694,609
  • Buffers: shared hit=26,111 read=177,677
6. 7.212 17.077 ↑ 1.4 35,785 1

Hash (cost=1,640.33..1,640.33 rows=50,133 width=8) (actual time=17.077..17.077 rows=35,785 loops=1)

  • Output: p.id
  • Buckets: 65,536 Batches: 1 Memory Usage: 1,910kB
  • Buffers: shared hit=1,139
7. 9.865 9.865 ↑ 1.4 35,785 1

Seq Scan on views.valid_point p (cost=0.00..1,640.33 rows=50,133 width=8) (actual time=0.013..9.865 rows=35,785 loops=1)

  • Output: p.id
  • Filter: (NOT p.is_deleted)
  • Buffers: shared hit=1,139
8. 7.320 21.801 ↑ 1.4 35,785 1

Hash (cost=1,640.33..1,640.33 rows=50,133 width=8) (actual time=21.801..21.801 rows=35,785 loops=1)

  • Output: pt.id
  • Buckets: 65,536 Batches: 1 Memory Usage: 1,910kB
  • Buffers: shared hit=1,139
9. 14.481 14.481 ↑ 1.4 35,785 1

Seq Scan on views.valid_point pt (cost=0.00..1,640.33 rows=50,133 width=8) (actual time=0.019..14.481 rows=35,785 loops=1)

  • Output: pt.id
  • Filter: (NOT pt.is_deleted)
  • Buffers: shared hit=1,139
Planning time : 1.147 ms
Execution time : 7,180.525 ms