explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8Hyt : With EXISTS -> smaller matched record

Settings
# exclusive inclusive rows x rows loops node
1. 1.348 639.807 ↑ 994.2 481 1

Sort (cost=39,770,772.51..39,771,967.98 rows=478,187 width=270) (actual time=639.763..639.807 rows=481 loops=1)

  • Sort Key: booking.checkin_date, booking.created_at DESC
  • Sort Method: quicksort Memory: 197kB
  • Buffers: shared hit=36,974
2. 637.303 638.459 ↑ 994.2 481 1

Seq Scan on booking (cost=0.00..39,604,709.22 rows=478,187 width=270) (actual time=1.985..638.459 rows=481 loops=1)

  • Filter: ((NOT deleted) AND (((reference_number)::text = '99999'::text) OR ((booking_id)::text = '99999'::text) OR (alternatives: SubPlan 1 or hashed SubPlan 2)))
  • Rows Removed by Filter: 957,957
  • Buffers: shared hit=36,974
3.          

SubPlan (for Seq Scan)

4. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on booking_customer (cost=37.34..41.36 rows=1 width=0) (never executed)

  • Recheck Cond: (((booking_id)::text = (booking.booking_id)::text) AND (((email)::text = '99999'::text) OR ((phone)::text = '99999'::text)))
  • Filter: (NOT deleted)
5. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=37.34..37.34 rows=1 width=0) (never executed)

6. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on book_cust_idx (cost=0.00..4.50 rows=10 width=0) (never executed)

  • Index Cond: ((booking_id)::text = (booking.booking_id)::text)
7. 0.000 0.000 ↓ 0.0 0

BitmapOr (cost=32.59..32.59 rows=1,030 width=0) (never executed)

8. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_booking_customer_email (cost=0.00..4.74 rows=41 width=0) (never executed)

  • Index Cond: ((email)::text = '99999'::text)
9. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ix_booking_customer_phone (cost=0.00..27.85 rows=989 width=0) (never executed)

  • Index Cond: ((phone)::text = '99999'::text)
10. 0.941 1.156 ↑ 1.7 595 1

Bitmap Heap Scan on booking_customer booking_customer_1 (cost=33.10..3,841.01 rows=1,023 width=32) (actual time=0.289..1.156 rows=595 loops=1)

  • Recheck Cond: (((email)::text = '99999'::text) OR ((phone)::text = '99999'::text))
  • Filter: (NOT deleted)
  • Heap Blocks: exact=514
  • Buffers: shared hit=524
11. 0.000 0.215 ↓ 0.0 0 1

BitmapOr (cost=33.10..33.10 rows=1,030 width=0) (actual time=0.215..0.215 rows=0 loops=1)

  • Buffers: shared hit=10
12. 0.022 0.022 ↑ 20.5 2 1

Bitmap Index Scan on ix_booking_customer_email (cost=0.00..4.74 rows=41 width=0) (actual time=0.022..0.022 rows=2 loops=1)

  • Index Cond: ((email)::text = '99999'::text)
  • Buffers: shared hit=3
13. 0.193 0.193 ↑ 1.7 594 1

Bitmap Index Scan on ix_booking_customer_phone (cost=0.00..27.85 rows=989 width=0) (actual time=0.193..0.193 rows=594 loops=1)

  • Index Cond: ((phone)::text = '99999'::text)
  • Buffers: shared hit=7
Planning time : 0.422 ms
Execution time : 639.906 ms