explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nan4 : Optimization for: plan #avjO

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 14,047.804 25,546.725 ↓ 1.1 10,243,617 1

Sort (cost=3,154,812.48..3,178,956.31 rows=9,657,531 width=113) (actual time=22,981.486..25,546.725 rows=10,243,617 loops=1)

  • Sort Key: product_variants.product_id
  • Sort Method: external merge Disk: 981200kB
2. 11,290.111 11,498.921 ↓ 1.1 10,243,617 1

Hash Right Join (cost=2,268.70..251,882.26 rows=9,657,531 width=113) (actual time=43.965..11,498.921 rows=10,243,617 loops=1)

  • Hash Cond: ((product_variants.data ->> 'sku'::text) = (master_variants.sku)::text)
  • Filter: (((product_variants.data ->> 'sku'::text) IS NOT NULL) OR ((supplier_variants.data ->> 'sku'::text) IS NOT NULL))
3. 164.900 164.900 ↑ 1.0 255,621 1

Seq Scan on product_variants (cost=0.00..31,536.26 rows=255,621 width=772) (actual time=0.025..164.900 rows=255,621 loops=1)

  • Filter: ((status)::text = ANY ('{available,active}'::text[]))
4. 6.090 43.910 ↓ 1.0 13,251 1

Hash (cost=2,104.35..2,104.35 rows=13,148 width=222) (actual time=43.909..43.910 rows=13,251 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 3478kB
5. 11.130 37.820 ↓ 1.0 13,251 1

Hash Right Join (cost=1,434.62..2,104.35 rows=13,148 width=222) (actual time=14.125..37.820 rows=13,251 loops=1)

  • Hash Cond: ((supplier_variants.data ->> 'sku'::text) = (master_variants.sku)::text)
6. 9.571 26.228 ↑ 1.0 13,136 1

Hash Right Join (cost=1,405.79..1,878.30 rows=13,148 width=205) (actual time=13.651..26.228 rows=13,136 loops=1)

  • Hash Cond: (m2.supplier_product_variant_id = supplier_variants.id)
7. 3.033 3.033 ↓ 1.0 26,195 1

Seq Scan on master_product_variant_supplier_product_variant m2 (cost=0.00..403.78 rows=26,178 width=16) (actual time=0.010..3.033 rows=26,195 loops=1)

8. 4.501 13.624 ↑ 1.0 13,136 1

Hash (cost=1,241.44..1,241.44 rows=13,148 width=197) (actual time=13.624..13.624 rows=13,136 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 3069kB
9. 9.123 9.123 ↑ 1.0 13,136 1

Seq Scan on supplier_product_variants supplier_variants (cost=0.00..1,241.44 rows=13,148 width=197) (actual time=0.006..9.123 rows=13,136 loops=1)

  • Filter: ((status)::text = ANY ('{available,active}'::text[]))
  • Rows Removed by Filter: 13059
10. 0.158 0.462 ↓ 1.0 353 1

Hash (cost=24.48..24.48 rows=348 width=17) (actual time=0.462..0.462 rows=353 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
11. 0.304 0.304 ↓ 1.0 353 1

Seq Scan on master_product_variants master_variants (cost=0.00..24.48 rows=348 width=17) (actual time=0.015..0.304 rows=353 loops=1)

  • Filter: (sku IS NOT NULL)
Planning time : 1.360 ms
Execution time : 27,484.609 ms