explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yNLF

Settings
# exclusive inclusive rows x rows loops node
1. 0.031 11,925.125 ↑ 1.0 25 1

Limit (cost=260,927.55..260,927.61 rows=25 width=64) (actual time=11,925.084..11,925.125 rows=25 loops=1)

2. 365.719 11,925.094 ↑ 7.9 25 1

Sort (cost=260,927.55..260,928.04 rows=198 width=64) (actual time=11,925.081..11,925.094 rows=25 loops=1)

  • Sort Key: (max((('40'::double precision + similarity(b2b_item_1.autokada_code, '0%'::text)))) OVER (?)) DESC, (COALESCE((b2b_item.vendor_id = ANY ('{3709598,2177,2081,1758,1719,3695948,2176,2348,1703,1784,1896,1977,2001,2180,1844,1743,2349,2347,2374,3201785,2481,2527,1834,2373,2045,2506,2436,1704}'::integer[])), false)) DESC, ((b2b_item.autokada_code = ''::text)), b2b_item.autokada_code, b2b_item.vendor_item_code
  • Sort Method: top-N heapsort Memory: 28kB
3. 1,007.703 11,559.375 ↓ 1,506.0 298,192 1

Nested Loop (cost=238,751.66..260,921.96 rows=198 width=64) (actual time=6,944.147..11,559.375 rows=298,192 loops=1)

4. 528.238 9,055.787 ↓ 1,495.9 299,177 1

Unique (cost=238,751.23..259,234.03 rows=200 width=12) (actual time=6,944.090..9,055.787 rows=299,177 loops=1)

5. 1,146.645 8,527.549 ↑ 1.9 545,791 1

WindowAgg (cost=238,751.23..256,673.68 rows=1,024,140 width=12) (actual time=6,944.087..8,527.549 rows=545,791 loops=1)

6. 981.735 7,380.904 ↑ 1.9 545,791 1

Sort (cost=238,751.23..241,311.58 rows=1,024,140 width=12) (actual time=6,944.053..7,380.904 rows=545,791 loops=1)

  • Sort Key: b2b_item_1.id
  • Sort Method: quicksort Memory: 50160kB
7. 1,592.584 6,399.169 ↑ 1.9 545,791 1

HashAggregate (cost=116,028.63..126,270.03 rows=1,024,140 width=11) (actual time=5,851.946..6,399.169 rows=545,791 loops=1)

  • Group Key: b2b_item_1.id, (('40'::double precision + similarity(b2b_item_1.autokada_code, '0%'::text)))
8. 1,098.666 4,806.585 ↑ 1.1 920,667 1

Append (cost=1,002.93..110,907.93 rows=1,024,140 width=11) (actual time=37.478..4,806.585 rows=920,667 loops=1)

9. 412.365 446.331 ↓ 1.0 108,008 1

Bitmap Heap Scan on b2b_item b2b_item_1 (cost=1,002.93..20,542.23 rows=107,217 width=6) (actual time=37.475..446.331 rows=108,008 loops=1)

  • Recheck Cond: (autokada_code ~~* '0%'::text)
  • Heap Blocks: exact=13914
10. 33.966 33.966 ↓ 1.9 204,233 1

Bitmap Index Scan on b2b_item_autokada_code_trgm (cost=0.00..976.13 rows=107,217 width=0) (actual time=33.966..33.966 rows=204,233 loops=1)

  • Index Cond: (autokada_code ~~* '0%'::text)
11. 378.322 395.050 ↓ 1.0 87,227 1

Bitmap Heap Scan on b2b_item b2b_item_2 (cost=813.84..19,989.38 rows=86,431 width=13) (actual time=19.641..395.050 rows=87,227 loops=1)

  • Recheck Cond: (vendor_item_code ~~* '0%'::text)
  • Heap Blocks: exact=12754
12. 16.728 16.728 ↓ 1.1 96,414 1

Bitmap Index Scan on b2b_item_vendor_item_code_trgm (cost=0.00..792.23 rows=86,431 width=0) (actual time=16.728..16.728 rows=96,414 loops=1)

  • Index Cond: (vendor_item_code ~~* '0%'::text)
13. 2,752.940 2,866.538 ↑ 1.1 725,432 1

Bitmap Heap Scan on cross_reference (cost=7,668.31..60,134.92 rows=830,492 width=12) (actual time=124.073..2,866.538 rows=725,432 loops=1)

  • Recheck Cond: (to_code ~~* '0%'::text)
  • Heap Blocks: exact=34778
14. 113.598 113.598 ↑ 1.1 725,432 1

Bitmap Index Scan on cross_reference_to_code_idx (cost=0.00..7,460.69 rows=830,492 width=0) (actual time=113.598..113.598 rows=725,432 loops=1)

  • Index Cond: (to_code ~~* '0%'::text)
15. 1,495.885 1,495.885 ↑ 1.0 1 299,177

Index Scan using b2b_item_pkey on b2b_item (cost=0.42..8.38 rows=1 width=56) (actual time=0.004..0.005 rows=1 loops=299,177)

  • Index Cond: (id = b2b_item_1.id)
  • Filter: in_scope_lat
  • Rows Removed by Filter: 0