explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aXMV : UNION ALL instead of OR

Settings
# exclusive inclusive rows x rows loops node
1. 11.594 3,093.432 ↑ 2.4 61,703 1

Append (cost=0.42..254,286.15 rows=150,570 width=270) (actual time=0.018..3,093.432 rows=61,703 loops=1)

  • Buffers: shared hit=68815, temp read=17190 written=17188
2. 0.017 0.017 ↑ 1.0 1 1

Index Scan using ix_booking_reference_number on booking (cost=0.42..8.44 rows=1 width=270) (actual time=0.017..0.017 rows=1 loops=1)

  • Index Cond: ((reference_number)::text = '9999999999'::text)
  • Filter: (NOT deleted)
  • Buffers: shared hit=4
3. 0.005 0.005 ↓ 0.0 0 1

Index Scan using booking_pkey on booking booking_1 (cost=0.42..8.44 rows=1 width=270) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: ((booking_id)::text = '9999999999'::text)
  • Filter: (NOT deleted)
  • Buffers: shared hit=3
4. 1,298.397 3,081.816 ↑ 2.4 61,702 1

Hash Join (cost=132,328.75..252,763.57 rows=150,568 width=270) (actual time=1,004.003..3,081.816 rows=61,702 loops=1)

  • Hash Cond: ((booking_2.booking_id)::text = ((booking_customer.booking_id)::text))
  • Buffers: shared hit=68808, temp read=17190 written=17188
5. 780.592 780.592 ↓ 1.0 958,694 1

Seq Scan on booking booking_2 (cost=0.00..46,013.73 rows=956,373 width=270) (actual time=0.006..780.592 rows=958,694 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=36450
6. 18.659 1,002.827 ↑ 1.8 65,204 1

Hash (cost=130,319.14..130,319.14 rows=115,569 width=15) (actual time=1,002.826..1,002.827 rows=65,204 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2999kB
  • Buffers: shared hit=32358, temp read=766 written=964
7. 27.642 984.168 ↑ 1.8 65,204 1

Unique (cost=129,566.30..130,319.14 rows=115,569 width=15) (actual time=698.548..984.168 rows=65,204 loops=1)

  • Buffers: shared hit=32358, temp read=766 written=766
8. 859.224 956.526 ↓ 1.0 156,385 1

Sort (cost=129,566.30..129,942.72 rows=150,568 width=15) (actual time=698.547..956.526 rows=156,385 loops=1)

  • Sort Key: ((booking_customer.booking_id)::text)
  • Sort Method: external merge Disk: 6104kB
  • Buffers: shared hit=32358, temp read=766 written=766
9. 74.105 97.302 ↓ 1.0 156,385 1

Bitmap Heap Scan on booking_customer (cost=4,268.39..114,041.41 rows=150,568 width=15) (actual time=28.814..97.302 rows=156,385 loops=1)

  • Recheck Cond: (((email)::text = '9999999999'::text) OR ((phone)::text = '9999999999'::text))
  • Filter: (NOT deleted)
  • Heap Blocks: exact=31610
  • Buffers: shared hit=32358
10. 0.000 23.197 ↓ 0.0 0 1

BitmapOr (cost=4,268.39..4,268.39 rows=151,499 width=0) (actual time=23.197..23.197 rows=0 loops=1)

  • Buffers: shared hit=748
11. 0.021 0.021 ↑ 3.7 11 1

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

  • Index Cond: ((email)::text = '9999999999'::text)
  • Buffers: shared hit=3
12. 23.176 23.176 ↓ 1.0 156,379 1

Bitmap Index Scan on ix_booking_customer_phone (cost=0.00..4,188.37 rows=151,459 width=0) (actual time=23.176..23.176 rows=156,379 loops=1)

  • Index Cond: ((phone)::text = '9999999999'::text)
  • Buffers: shared hit=745
Planning time : 0.700 ms
Execution time : 3,100.562 ms