explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 85M3

Settings
# exclusive inclusive rows x rows loops node
1. 5.321 3,401.559 ↑ 1.0 1 1

Aggregate (cost=21,396.64..21,396.65 rows=1 width=8) (actual time=3,401.558..3,401.559 rows=1 loops=1)

2.          

CTE leaf_info

3. 14.395 3,328.515 ↓ 1.8 48,653 1

Unique (cost=20,649.91..20,785.68 rows=27,154 width=33) (actual time=3,297.885..3,328.515 rows=48,653 loops=1)

4.          

Initplan (forUnique)

5. 0.001 1.001 ↑ 1.0 1 1

Subquery Scan on lf_seller_view (cost=87.46..87.49 rows=1 width=16) (actual time=1.000..1.001 rows=1 loops=1)

6. 0.000 1.000 ↑ 1.0 1 1

Group (cost=87.46..87.48 rows=1 width=442) (actual time=0.999..1.000 rows=1 loops=1)

  • Group Key: s.id, mme.bio_license_number, mme.global_id, c.id, l.id, mme.import_config
7. 0.017 1.000 ↑ 1.0 1 1

Sort (cost=87.46..87.47 rows=1 width=86) (actual time=0.999..1.000 rows=1 loops=1)

  • Sort Key: s.id, mme.bio_license_number, mme.global_id, c.id, l.id, mme.import_config
  • Sort Method: quicksort Memory: 25kB
8. 0.007 0.983 ↑ 1.0 1 1

Nested Loop (cost=0.56..87.45 rows=1 width=86) (actual time=0.913..0.983 rows=1 loops=1)

  • Join Filter: (c.location_id = l.id)
9. 0.002 0.963 ↑ 1.0 1 1

Nested Loop (cost=0.28..87.05 rows=1 width=102) (actual time=0.893..0.963 rows=1 loops=1)

10. 0.175 0.944 ↑ 1.0 1 1

Nested Loop (cost=0.00..78.74 rows=1 width=64) (actual time=0.875..0.944 rows=1 loops=1)

  • Join Filter: (s.company_id = c.id)
  • Rows Removed by Join Filter: 37
11. 0.595 0.595 ↓ 2.0 2 1

Seq Scan on company c (cost=0.00..77.31 rows=1 width=48) (actual time=0.278..0.595 rows=2 loops=1)

  • Filter: (name = 'HANNAH INDUSTRIES'::text)
  • Rows Removed by Filter: 2583
12. 0.174 0.174 ↑ 1.0 19 2

Seq Scan on seller s (cost=0.00..1.19 rows=19 width=32) (actual time=0.087..0.087 rows=19 loops=2)

13. 0.017 0.017 ↑ 1.0 1 1

Index Scan using uq_leaf_mme_location_id on mme (cost=0.28..8.30 rows=1 width=38) (actual time=0.016..0.017 rows=1 loops=1)

  • Index Cond: (location_id = c.location_id)
14. 0.013 0.013 ↑ 1.0 1 1

Index Only Scan using pk_location on location l (cost=0.28..0.39 rows=1 width=16) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: (id = mme.location_id)
  • Heap Fetches: 1
15. 285.756 3,313.119 ↓ 1.8 48,954 1

Sort (cost=20,562.41..20,630.30 rows=27,154 width=33) (actual time=3,297.885..3,313.119 rows=48,954 loops=1)

  • Sort Key: lii.global_id
  • Sort Method: external merge Disk: 2144kB
16. 678.277 3,027.363 ↓ 1.8 48,954 1

HashAggregate (cost=18,291.13..18,562.67 rows=27,154 width=33) (actual time=3,007.818..3,027.363 rows=48,954 loops=1)

  • Group Key: lii.global_inventory_type_id, lii.global_id
17. 2,283.619 2,349.086 ↓ 4.3 125,571 1

Bitmap Heap Scan on lf_inventory_item lii (cost=685.10..18,143.60 rows=29,507 width=33) (actual time=67.183..2,349.086 rows=125,571 loops=1)

  • Recheck Cond: (seller_id = $2)
  • Heap Blocks: exact=8915
18. 65.467 65.467 ↓ 4.3 125,571 1

Bitmap Index Scan on idx_lf_inventory_item_seller (cost=0.00..677.73 rows=29,507 width=0) (actual time=65.467..65.467 rows=125,571 loops=1)

  • Index Cond: (seller_id = $2)
19. 3,396.238 3,396.238 ↓ 1.8 48,653 1

CTE Scan on leaf_info (cost=0.00..543.08 rows=27,154 width=0) (actual time=3,297.886..3,396.238 rows=48,653 loops=1)