explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y9gp

Settings
# exclusive inclusive rows x rows loops node
1. 32.884 212,621.271 ↑ 646,107.8 4 1

Merge Anti Join (cost=0.57..14,019,509.48 rows=2,584,431 width=115) (actual time=161,981.324..212,621.271 rows=4 loops=1)

  • Merge Cond: (o.bill_id = m.bill_id)
  • Join Filter: (b.batch_id = (m.batch_id)::numeric)
  • Rows Removed by Join Filter: 5,218
2. 176,142.406 212,547.076 ↑ 284.4 12,258 1

Nested Loop (cost=0.29..13,947,427.37 rows=3,485,938 width=115) (actual time=129,936.974..212,547.076 rows=12,258 loops=1)

  • Join Filter: ((b.book_desc)::text ~~ (('POS:'::text || (o.bill_num)::text) || '%'::text))
  • Rows Removed by Join Filter: 707,609,498
3. 99.816 99.816 ↓ 1.0 28,078 1

Index Scan using pos_bill_pk on pos_bill o (cost=0.29..1,897.55 rows=27,559 width=26) (actual time=0.014..99.816 rows=28,078 loops=1)

4. 36,282.158 36,304.854 ↑ 1.0 25,202 28,078

Materialize (cost=0.00..1,841.43 rows=25,298 width=89) (actual time=0.000..1.293 rows=25,202 loops=28,078)

5. 22.696 22.696 ↑ 1.0 25,202 1

Seq Scan on im_book b (cost=0.00..1,714.94 rows=25,298 width=89) (actual time=0.017..22.696 rows=25,202 loops=1)

  • Filter: (book_type_id = ANY ('{6,17}'::numeric[]))
  • Rows Removed by Filter: 11,842
6. 7.081 41.311 ↓ 1.1 26,864 1

Materialize (cost=0.29..1,751.69 rows=24,380 width=16) (actual time=0.050..41.311 rows=26,864 loops=1)

7. 34.230 34.230 ↑ 1.0 24,313 1

Index Scan using pos_bill_batch_pk on pos_bill_batch m (cost=0.29..1,690.74 rows=24,380 width=16) (actual time=0.044..34.230 rows=24,313 loops=1)

Planning time : 0.961 ms
Execution time : 212,621.668 ms