explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jq3g

Settings
# exclusive inclusive rows x rows loops node
1. 12,771.611 19,366.994 ↓ 1.3 16,000,000 1

Nested Loop (cost=46,317.97..3,722,456.19 rows=12,000,812 width=89) (actual time=346.518..19,366.994 rows=16,000,000 loops=1)

  • Output: fee.subtype, fee.price, (e.payload ->> 'amount'::text), (fee.price * ((e.payload ->> 'amount'::text))::numeric), e.id
2. 52.670 426.415 ↑ 1.9 8 1

Unique (cost=0.42..63,314.30 rows=15 width=90) (actual time=0.078..426.415 rows=8 loops=1)

  • Output: fee.price, fee.event_type, fee.subtype, fee.user_id, fee.warehouse_id
3. 373.745 373.745 ↑ 1.0 458,786 1

Index Scan Backward using idx_fee_sorted_cmpx_event_type_subtype on public.fee (cost=0.42..61,020.37 rows=458,787 width=90) (actual time=0.075..373.745 rows=458,786 loops=1)

  • Output: fee.price, fee.event_type, fee.subtype, fee.user_id, fee.warehouse_id
  • Filter: ((fee.created_at >= '2020-01-01 00:00:00'::timestamp without time zone) AND (fee.created_at <= '2020-02-01 00:00:00'::timestamp without time zone) AND (fee.domain = 'customer'::feedomainenum))
  • Rows Removed by Filter: 1
4. 4,774.648 6,168.968 ↑ 2.0 2,000,000 8

Bitmap Heap Scan on public.events e (cost=46,317.55..193,939.40 rows=4,000,271 width=79) (actual time=180.986..771.121 rows=2,000,000 loops=8)

  • Output: e.id, e.type, e.payload, e.created_at
  • Recheck Cond: ((fee.user_id = (e.payload ->> 'user_id'::text)) OR (fee.warehouse_id = (e.payload ->> 'warehouse_id'::text)) OR (fee.event_type = e.type))
  • Filter: ((e.created_at >= '2020-01-01 00:00:00'::timestamp without time zone) AND (e.created_at <= '2020-02-01 00:00:00'::timestamp without time zone))
  • Heap Blocks: exact=281397
5. 0.048 1,394.320 ↓ 0.0 0 8

BitmapOr (cost=46,317.55..46,317.55 rows=4,000,271 width=0) (actual time=174.290..174.290 rows=0 loops=8)

6. 0.112 0.112 ↓ 0.0 0 8

Bitmap Index Scan on idx_events_payload_user_id (cost=0.00..4.44 rows=1 width=0) (actual time=0.014..0.014 rows=0 loops=8)

  • Index Cond: (fee.user_id = (e.payload ->> 'user_id'::text))
7. 0.120 0.120 ↓ 0.0 0 8

Bitmap Index Scan on idx_events_payload_warehouse_id (cost=0.00..4.44 rows=1 width=0) (actual time=0.015..0.015 rows=0 loops=8)

  • Index Cond: (fee.warehouse_id = (e.payload ->> 'warehouse_id'::text))
8. 1,394.040 1,394.040 ↑ 2.0 2,000,000 8

Bitmap Index Scan on idx_events_sorted_type_asc (cost=0.00..43,308.46 rows=4,000,271 width=0) (actual time=174.255..174.255 rows=2,000,000 loops=8)

  • Index Cond: (fee.event_type = e.type)
Planning time : 0.921 ms
Execution time : 19,980.792 ms