We have a database where we track the price of products from different sellers. If I strip everythin else it looks like this:
I need to make a report where given a date range I have to return a row for every day and the prices for every seller. There will be days that some seller don't have a price for a product and there will be days that a seller will have more than one price for a given product.
I'm doing it by creating a temp table with the date ranges then join them with the sellers and prices for this but once product_price have a couple thousands rows it get VERY slow (about 4'' to return a 200 rows) and I don't know how to optimize it to get it fast enought.
I can't think a workaround for the temp table nor which index should I create.
schema and query here: DB-fiddle