Postgresql: how to query hstore dynamically

Question

I have the following tables

ORDER (idOrder int, idCustomer int) [PK: idOrder]
ORDERLINE (idOrder int, idProduct int) [PK: idOrder, idProduct]
PRODUCT (idProduct int, rating hstore) [PK: idProduct]

In the PRODUCT table, 'rating' is a key/value column where the key is an idCustomer, and the value is an integer rating.

The query to count the orders containing a product on which the customer has given a good rating looks like this:

select count(distinct o.idOrder)
from order o, orderline l, product p 
where o.idorder = l.idorder and l.idproduct = p.idproduct
  and (p.rating->(o.idcust::varchar)::int) > 4;

The query plan seems correct, but this query takes forever. So I tried a different query, where I explode all the records in the hstore:

select count(distinct o.idOrder)
from order o, orderline l, 
  (select idproduct, skeys(p.rating) idcustomer, svals(p.rating) intrating from product) as p
where o.idorder = l.idorder and l.idproduct = p.idproduct
  and o.idcustomer = p.idcustomer and p.intrating > 4;

This query takes only a few seconds. How is this possible? I assumed that exploding all values of an hstore would be quite inefficient, but it seems to be the opposite. Is it possible that I am not writing the first query correctly?

Adrian Klaver · Accepted Answer · 2020-06-10 16:40:24Z

1

I'm suspecting it is because in the first query you are doing:

p.rating->(o.idcust::varchar)::int

a row at a time as the query iterates over the rest of the operations, whereas in the second query the hstore values are expanded in a single query. If you want more insight use EXPLAIN ANALYZE:

https://www.postgresql.org/docs/12/sql-explain.html

answered Jun 10, 2020 at 16:40

Adrian Klaver

20.4k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql: how to query hstore dynamically

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related