Simple query is really slow

Question

I have this query that it takes 36s to execute and I don't understand why or how could I improve it. Any help?

SELECT p.* FROM products p INNER JOIN product_store ps ON p.id = ps.product_id
INNER JOIN stores s ON s.id = ps.store_id WHERE s.city = 'Berlin' GROUP BY p.id LIMIT 16 OFFSET 0;

Those are the following numbers:

SELECT count(*) FROM products;

43309

SELECT count(*) FROM product_store;

1456445

SELECT count(*) FROM stores;

155

I imagine that it could be for the relationship table but 36s is too much for 16 rows.

Any idea how to improve this query?

EDIT:

For any reason, the problem is not by Postgres but by Hibernate. The query is quite fast in pgAdmin 4 but really slow in Hibernate.

Thanks, everybody!

EDIT 2:

Analysis

EDIT 3:

Sorry, this is the real problem when I add "DISTINCT"

SELECT DISTINCT p.* FROM products p INNER JOIN product_store ps ON p.id = ps.product_id
INNER JOIN stores s ON s.id = ps.store_id WHERE s.city = 'Berlin' GROUP BY p.id LIMIT 16 OFFSET 0;

PRODUCTION

LOCAL

Answer 1

The aggregation is a big issue. I would suggest using EXISTS instead:

SELECT p.*
FROM products p 
WHERE EXISTS (SELECT 1
              FROM product_store ps INNER JOIN
                   stores s 
                   ON s.id = ps.store_id
              WHERE p.id = ps.product_id AND s.city = 'Berlin' 
             )
LIMIT 16 OFFSET 0;

Then be sure you have indexes on product_store(product_id, store_id). I assume you already have an index on stores(id) -- because that should be the primary key.