explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zUCN

Settings
# exclusive inclusive rows x rows loops node
1. 0.134 71,645.463 ↑ 1.0 1,501 1

Limit (cost=38,697.51..54,942.09 rows=1,501 width=944) (actual time=71,642.033..71,645.463 rows=1,501 loops=1)

2. 0.526 71,645.329 ↑ 10.8 1,501 1

Result (cost=38,697.51..213,675.69 rows=16,168 width=944) (actual time=71,642.031..71,645.329 rows=1,501 loops=1)

3. 70.976 71,640.300 ↑ 10.8 1,501 1

Sort (cost=38,697.51..38,737.93 rows=16,168 width=854) (actual time=71,640.180..71,640.300 rows=1,501 loops=1)

  • Sort Key: sl.created_on DESC, sl.id DESC
  • Sort Method: top-N heapsort Memory: 308kB
4. 158.246 71,569.324 ↓ 7.8 126,444 1

Nested Loop (cost=2,023.18..37,763.67 rows=16,168 width=854) (actual time=35,324.470..71,569.324 rows=126,444 loops=1)

5. 35.634 37,397.642 ↓ 18.6 126,444 1

Hash Join (cost=2,022.75..33,566.61 rows=6,799 width=582) (actual time=35,317.001..37,397.642 rows=126,444 loops=1)

  • Hash Cond: (sl.agent_id = a.id)
6. 490.351 37,357.322 ↓ 18.6 126,444 1

Hash Join (cost=2,011.85..33,516.61 rows=6,799 width=556) (actual time=35,312.289..37,357.322 rows=126,444 loops=1)

  • Hash Cond: (sl.sku_id = s.id)
7. 2,353.247 2,353.247 ↑ 1.0 1,349,125 1

Seq Scan on sku_location sl (cost=0.00..26,332.47 rows=1,361,147 width=40) (actual time=27.873..2,353.247 rows=1,349,125 loops=1)

8. 64.019 34,513.724 ↓ 279.7 132,301 1

Hash (cost=2,005.94..2,005.94 rows=473 width=532) (actual time=34,513.724..34,513.724 rows=132,301 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 4 (originally 1) Memory Usage: 3585kB
9. 29,806.737 34,449.705 ↓ 279.7 132,301 1

Bitmap Heap Scan on sku s (cost=292.08..2,005.94 rows=473 width=532) (actual time=4,648.917..34,449.705 rows=132,301 loops=1)

  • Recheck Cond: (retailer_id = 9)
  • Heap Blocks: exact=19890
10. 4,642.968 4,642.968 ↓ 279.7 132,302 1

Bitmap Index Scan on idx_sku_retailer_id (cost=0.00..291.97 rows=473 width=0) (actual time=4,642.968..4,642.968 rows=132,302 loops=1)

  • Index Cond: (retailer_id = 9)
11. 0.008 4.686 ↓ 1.2 49 1

Hash (cost=10.40..10.40 rows=40 width=42) (actual time=4.686..4.686 rows=49 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
12. 4.678 4.678 ↓ 1.2 49 1

Seq Scan on agent a (cost=0.00..10.40 rows=40 width=42) (actual time=1.091..4.678 rows=49 loops=1)

13. 34,013.436 34,013.436 ↑ 1.0 1 126,444

Index Scan using idx_inventory_sku_location_id on sku_location_inventory sli (cost=0.43..0.58 rows=1 width=20) (actual time=0.260..0.269 rows=1 loops=126,444)

  • Index Cond: (sku_location_id = sl.id)
14.          

SubPlan (forResult)

15. 4.503 4.503 ↑ 1.0 1 1,501

Seq Scan on inventory_catalogue cat (cost=0.00..10.80 rows=1 width=90) (actual time=0.003..0.003 rows=1 loops=1,501)

  • Filter: ((external_reference)::text = ('COMPATIBILITY:'::text || (s.retailer_id)::text))
  • Rows Removed by Filter: 4