explain.depesz.com

PostgreSQL's explain analyze made readable

Result: avjO

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 691.578 172,431.574 ↑ 2.4 399,807 1

Sort (cost=3,391,401.37..3,393,845.68 rows=977,722 width=117) (actual time=172,234.155..172,431.574 rows=399,807 loops=1)

  • Sort Key: product_variants.product_id
  • Sort Method: external merge Disk: 39584kB
2. 5,760.824 171,739.996 ↑ 2.4 399,807 1

Hash Right Join (cost=1,031,535.50..3,173,813.61 rows=977,722 width=117) (actual time=85,570.239..171,739.996 rows=399,807 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))
  • Rows Removed by Filter: 327197
3. 80,427.453 102,811.742 ↑ 1.9 488,950 1

Bitmap Heap Scan on product_variants (cost=266,160.12..2,027,387.62 rows=925,807 width=784) (actual time=22,400.535..102,811.742 rows=488,950 loops=1)

  • Recheck Cond: ((status)::text = ANY ('{available,active}'::text[]))
  • Rows Removed by Index Recheck: 86412
  • Heap Blocks: exact=49334 lossy=133501
4. 22,384.289 22,384.289 ↑ 1.7 554,803 1

Bitmap Index Scan on product_variants_status_index (cost=0.00..265,928.66 rows=925,807 width=0) (actual time=22,384.289..22,384.289 rows=554,803 loops=1)

  • Index Cond: ((status)::text = ANY ('{available,active}'::text[]))
5. 452.779 63,167.430 ↓ 1.0 509,965 1

Hash (cost=733,191.44..733,191.44 rows=486,076 width=414) (actual time=63,167.430..63,167.430 rows=509,965 loops=1)

  • Buckets: 16384 Batches: 64 Memory Usage: 2133kB
6. 21,841.923 62,714.651 ↓ 1.0 509,965 1

Hash Right Join (cost=640,804.98..733,191.44 rows=486,076 width=414) (actual time=40,016.038..62,714.651 rows=509,965 loops=1)

  • Hash Cond: (m2.supplier_product_variant_id = supplier_variants.id)
7. 1,825.496 1,825.496 ↓ 1.0 586,789 1

Seq Scan on master_product_variant_supplier_product_variant m2 (cost=0.00..55,366.81 rows=583,681 width=16) (actual time=0.835..1,825.496 rows=586,789 loops=1)

8. 1,446.853 39,047.232 ↓ 1.0 501,737 1

Hash (cost=609,096.03..609,096.03 rows=486,076 width=406) (actual time=39,047.232..39,047.232 rows=501,737 loops=1)

  • Buckets: 16384 (originally 16384) Batches: 16384 (originally 64) Memory Usage: 21152kB
9. 611.133 37,600.379 ↓ 1.0 501,737 1

Merge Right Join (cost=94,343.05..609,096.03 rows=486,076 width=406) (actual time=3,687.281..37,600.379 rows=501,737 loops=1)

  • Merge Cond: ((supplier_variants.data ->> 'sku'::text) = (master_variants.sku)::text)
10. 32,103.591 32,103.591 ↑ 6.7 103,237 1

Index Scan using supplier_product_variants_date_index on supplier_product_variants supplier_variants (cost=0.55..2,890,842.84 rows=692,922 width=385) (actual time=0.012..32,103.591 rows=103,237 loops=1)

  • Filter: ((status)::text = ANY ('{available,active}'::text[]))
  • Rows Removed by Filter: 45111
11. 77.742 4,885.655 ↓ 1.0 501,737 1

Materialize (cost=88,290.09..90,720.47 rows=486,076 width=21) (actual time=3,553.987..4,885.655 rows=501,737 loops=1)

12. 3,825.197 4,807.913 ↑ 1.0 485,818 1

Sort (cost=88,290.09..89,505.28 rows=486,076 width=21) (actual time=3,553.982..4,807.913 rows=485,818 loops=1)

  • Sort Key: master_variants.sku
  • Sort Method: external merge Disk: 15040kB
13. 982.716 982.716 ↑ 1.0 485,818 1

Seq Scan on master_product_variants master_variants (cost=0.00..32,406.71 rows=486,076 width=21) (actual time=0.987..982.716 rows=485,818 loops=1)

  • Filter: (sku IS NOT NULL)
  • Rows Removed by Filter: 199
Planning time : 17.930 ms
Execution time : 172,505.128 ms