Styjun

I'm having trouble coming up with an efficient query that compares two tables with various attributes. This is for a report for an online retailer who has several hundred thousand SKUs available for sale. Each SKU is a variation of a "parent" product. They sell on various marketplaces and need to see if there are items that are not available for sale in various places.

There is a table with all parent products, and another table with all variations with their corresponding SKU. In a third table they have a complete list of each sku (variation) and it's corresponding marketplace where the combination of sku + marketplace is unique.

Database uses PostgreSQL

Table structures are as follows:

Product Table:

Products
id | parent_sku | vendor_id
-------------------------------
 1 | ABC | 100
 2 | DEF | 200
 3 | XYZ | 100

Variation Table:

Variations
id | parent_id | sku
----------------------------
 1 | 1 | ABC-1
 2 | 1 | ABC-2
 3 | 1 | ABC-3
 4 | 2 | DEF-1
 5 | 2 | DEF-2
 6 | 3 | XYZ-1
 7 | 3 | XYZ-2

Marketplace Table:

MarketplaceData
 id | sku | marketplace | price
----------------------------
 1 | ABC-1 | website1 | 99.99
 2 | ABC-2 | website1 | 99.99
 3 | ABC-3 | website1 | 89.99
 4 | DEF-1 | website1 | 29.99
 5 | DEF-2 | website1 | 29.99
 6 | XYZ-1 | website1 | 39.99
 7 | XYZ-2 | website1 | 39.99
 8 | ABC-1 | website2 | 99.99
 9 | ABC-2 | website2 | 99.99
 10 | ABC-3 | website2 | 99.99
 11 | DEF-1 | website2 | 29.99
 12 | DEF-2 | website2 | 29.99
 13 | XYZ-1 | website2 | 34.99
 14 | XYZ-2 | website2 | 34.99

I have a working query, but it takes extremely long to execute and is very taxing.

SELECT DISTINCT parent_id FROM Variations 
WHERE sku IN (SELECT sku FROM MarketplaceData WHERE marketplace IN ('website1','website2')) 
AND sku NOT IN (SELECT sku FROM MarketplaceData WHERE marketplace IN ('website3','website4')) 
LIMIT 20 OFFSET 0 

Since each sku + marketplace dataset has close to 400,000 rows and the MarketplaceData table contains over 2 million rows, this query takes forever to execute.

In terms of indexing, the id column is the primary key for each. The Variations table has an index on sku (must be unique) and the MarketplaceData is indexed on sku + marketplace.

Ultimately, what I need is a list of unique parent_id's that meet the criteria.

Any help or guidance would be greatly appreciated.

Thanks!

instead of IN and NOT In you could use INNER JOIN and LEFT JOIN with checking for null

SELECT DISTINCT v.parent_id 
FROM Variations v
INNER JOIN (
 SELECT sku FROM MarketplaceData WHERE marketplace IN ('website1','website2')
) t1 on t1.sku = v.sku 
LEFT JOIN (
 SELECT sku FROM MarketplaceData WHERE marketplace IN ('website3','website4')
) t2 On t2.sku = v.sku 
WHERE t2.sku is null

Why if you only use a single subquery?

SELECT DISTINCT parent_id 
FROM Variations 
WHERE sku IN (SELECT sku FROM MarketplaceData WHERE marketplace IN ('website1','website2')
 except
 SELECT sku FROM MarketplaceData WHERE marketplace IN ('website3','website4'))
LIMIT 20 OFFSET 0 

How about a simple aggregation to get the skus?

select mpd.sku
from MarketplaceData mpd
where mpd.marketplace in ('website1', 'website2', 'website3', 'website4')
group by mpd.sku
having count(*) filter (where mpd.marketplace in ('website1', 'website2')) > 0 and
 count(*) filter (where mpd.marketplace in ('website3', 'website4')) = 0;

Then to get the parent ids:

select distinct v.parent_id
from variations v join
 (select mpd.sku
 from MarketplaceData mpd
 where mpd.marketplace in ('website1', 'website2', 'website3', 'website4')
 group by mpd.sku
 having count(*) filter (where mpd.marketplace in ('website1', 'website2')) > 0 and
 count(*) filter (where mpd.marketplace in ('website3', 'website4')) = 0
 ) m
 on m.sku = v.sku;