explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Uet3

Settings
# exclusive inclusive rows x rows loops node
1. 1.214 6,668.206 ↓ 1,288.0 2,576 1

Sort (cost=51,554.60..51,554.60 rows=2 width=126) (actual time=6,668.088..6,668.206 rows=2,576 loops=1)

  • Output: (0), c.pnr_locator, c.pnr_create_date, c.primary_doc_number, c.coupon_number, ('P'::text), c.charge_date, ((c.points - c.used_points))
  • Sort Key: c.charge_date
  • Sort Method: quicksort Memory: 246kB
  • Buffers: shared hit=3 read=180477, temp read=39110 written=38984
2. 0.146 6,666.992 ↓ 1,288.0 2,576 1

Append (cost=2,771.78..51,554.59 rows=2 width=126) (actual time=5,195.190..6,666.992 rows=2,576 loops=1)

  • Buffers: shared hit=3 read=180477, temp read=39110 written=38984
3. 1,517.925 6,666.705 ↓ 2,576.0 2,576 1

Hash Join (cost=2,771.78..51,546.39 rows=1 width=104) (actual time=5,195.189..6,666.705 rows=2,576 loops=1)

  • Output: 0, c.pnr_locator, c.pnr_create_date, c.primary_doc_number, c.coupon_number, 'P'::text, c.charge_date, (c.points - c.used_points)
  • Hash Cond: ((t.primary_doc_number)::bpchar = c.primary_doc_number)
  • Buffers: shared hit=3 read=180471, temp read=39110 written=38984
4. 0.298 13.175 ↓ 1.9 1,658 1

Nested Loop (cost=486.54..49,251.49 rows=858 width=44) (actual time=0.480..13.175 rows=1,658 loops=1)

  • Output: t.primary_doc_number
  • Buffers: shared hit=3 read=1460
5. 0.031 0.031 ↑ 1.0 1 1

Index Scan using agents_pkey on public.agents a (cost=0.28..8.30 rows=1 width=11) (actual time=0.029..0.031 rows=1 loops=1)

  • Output: a.agent_id, a.org, a.validator, a.phone, a.promo, a.offer_confirm, a.pd_confirm, a.alp_code, a.validator_id, a.status, a.points_balance, a.additional_validators, a.points_blocked
  • Index Cond: (a.agent_id = 1308)
  • Buffers: shared hit=2 read=1
6. 12.560 12.846 ↑ 12.6 1,658 1

Bitmap Heap Scan on public.alp_tickets t (cost=486.26..49,034.38 rows=20,881 width=102) (actual time=0.444..12.846 rows=1,658 loops=1)

  • Output: t.pnr_locator, t.pnr_create_date, t.primary_doc_number, t.last_changed, t.validator, t.doc_type_code, t.doc_status_code, t.pos, t.agent_code, t.sale_channel, t.total_fare_amount, t.total_fare_currency, t.base_fare_amount, (...)
  • Recheck Cond: ((t.agent_code)::text = (a.alp_code)::text)
  • Heap Blocks: exact=1450
  • Buffers: shared hit=1 read=1459
7. 0.286 0.286 ↑ 12.6 1,660 1

Bitmap Index Scan on alp_tickets_agent_code_idx (cost=0.00..481.04 rows=20,881 width=0) (actual time=0.286..0.286 rows=1,660 loops=1)

  • Index Cond: ((t.agent_code)::text = (a.alp_code)::text)
  • Buffers: shared read=10
8. 1,252.234 5,135.605 ↓ 56,212.6 3,766,246 1

Hash (cost=2,284.40..2,284.40 rows=67 width=104) (actual time=5,135.605..5,135.605 rows=3,766,246 loops=1)

  • Output: c.pnr_locator, c.pnr_create_date, c.primary_doc_number, c.coupon_number, c.charge_date, c.points, c.used_points
  • Buckets: 1024 Batches: 64 (originally 1) Memory Usage: 4097kB
  • Buffers: shared read=179011, temp written=28941
9. 2,667.385 3,883.371 ↓ 56,212.6 3,766,246 1

Bitmap Heap Scan on public.alp_coupons c (cost=1,495.36..2,284.40 rows=67 width=104) (actual time=1,224.237..3,883.371 rows=3,766,246 loops=1)

  • Output: c.pnr_locator, c.pnr_create_date, c.primary_doc_number, c.coupon_number, c.charge_date, c.points, c.used_points
  • Recheck Cond: (((c.coupon_status)::text = 'USED'::text) AND (c.points_status = 'A'::bpchar))
  • Rows Removed by Index Recheck: 2660667
  • Filter: (c.points > c.used_points)
  • Rows Removed by Filter: 402183
  • Heap Blocks: exact=47328 lossy=98190
  • Buffers: shared read=179011
10. 14.721 1,215.986 ↓ 0.0 0 1

BitmapAnd (cost=1,495.36..1,495.36 rows=202 width=0) (actual time=1,215.986..1,215.986 rows=0 loops=1)

  • Buffers: shared read=33493
11. 642.134 642.134 ↓ 163.6 6,610,619 1

Bitmap Index Scan on alp_coupons_coupon_status_idx (cost=0.00..747.54 rows=40,414 width=0) (actual time=642.134..642.134 rows=6,610,619 loops=1)

  • Index Cond: ((c.coupon_status)::text = 'USED'::text)
  • Buffers: shared read=18068
12. 559.131 559.131 ↓ 139.7 5,644,488 1

Bitmap Index Scan on alp_coupons_points_status_idx (cost=0.00..747.54 rows=40,414 width=0) (actual time=559.131..559.131 rows=5,644,488 loops=1)

  • Index Cond: (c.points_status = 'A'::bpchar)
  • Buffers: shared read=15425
13. 0.141 0.141 ↓ 0.0 0 1

Index Scan using alp_charges_points_status_idx on public.alp_charges c_1 (cost=0.15..8.18 rows=1 width=20) (actual time=0.141..0.141 rows=0 loops=1)

  • Output: c_1.charge_id, NULL::bpchar, NULL::date, NULL::bpchar, NULL::integer, 'G'::text, date(c_1.charge_date), (c_1.points - c_1.used_points)
  • Index Cond: (c_1.points_status = 'A'::bpchar)
  • Filter: ((c_1.points > c_1.used_points) AND (c_1.user_id = 1308) AND (c_1.is_automatic = '0'::bpchar))
  • Rows Removed by Filter: 207
  • Buffers: shared read=6