2

I have Postgresql 9.4 database and products table. For each product there must be list of pairs: price; quantity for which this price is acutal

Products table may contain millions and billions of records. For this products table i must provide filtering by price, filter by quantity, and filter by price+quantity. If there is only price filter than if product has at least one price variant that satisfites filter then this product will be in result list. If there is price+quantity filter then product will be in result list only if there is at least one price variant which has price AND quantity that satisfies filters.

If I create separate tables

create table prod (id integer primary key);
create table optprice (prod integer, price decimal, q integer);

than with millions of products query takes realy long time:

select * from prod where id in (select o.prod from optprice o where price between 10 and 500 ) limit 20;
 Planning time: 0.166 ms
 Execution time: 867.663 ms

select count(*) from prod where id in (select o.prod from optprice o where price between 10 and 500 );
 Planning time: 0.166 ms
 Execution time: 867.663 ms

Even if I replace first query with joins, count query still too slow

select count(*) from prod left join optprice on id=optprice.prod where price between 10 and 500 limit 20;
 Planning time: 0.149 ms
 Execution time: 1478.455 ms

I decided to use postgresql arrays, so each product has field optprice with something like: {{112.3, 33}, {555.12, 66}, {77.8, 88}}

But I can't understand how can I implement filtering, described earlier. I can implement separate price or quantity filters. I can't see how price+query filtering is possible here. I can write some function but, if i not mistaken, i lose indexing ability and again queries become too slow.

Is it possible to do something like this in postgresql, so it will work relatively fast even on large datasets? (Also, sorry for my bad english).

1

1 Answer 1

0

adjust you query for:

select count(*) from optprice  
left join prod  on optprice.prod = prod.id 
where price between 10 and 500 limit 20;

and create a index optprice.price

I believe that performance is better. So he doesn't need to join before the filters

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.