Speed up Min/Max operation on postgres with index for IN operator query

Question

I would like to optimize the following query in postgres

SELECT(MIN("products"."shipping") AS minimal FROM "products" WHERE "products"."tag_id" IN (?)

with an index like

CREATE INDEX my_index ON products (tag_id, shipping DESC);

Unfortunately this one is only used when it's just one tag. Almost alwayst it is queried for a handful of tags at the same time, but then postgres uses the index products (shipping DESC) which is quite slow. What could I do to speed up my query?

Have you tried: SELECT(MIN(minimal) AS minimal FROM ( SELECT(MIN("products"."shipping") AS minimal FROM "products" WHERE "products"."tag_id" IN (?,?,?,?,?,?,?) GROUP BY "tag_id" ) some_alias ? — krokodilko
– krokodilko, Commented Jun 16, 2015 at 20:02
Great! This seems to speed things up on my data :) You can also post as an answer and I can give you the points — Yo Ludke
– Yo Ludke, Commented Jun 17, 2015 at 8:55

krokodilko · Accepted Answer · 2015-06-17 18:43:28Z

2

It turned out (please see comments), that this query:

SELECT MIN(minimal) AS minimal 
FROM ( 
  SELECT  MIN("products"."shipping") AS minimal 
  FROM "products" 
  WHERE "products"."tag_id" IN (?,?,?,?,?,?,?) 
  GROUP BY "tag_id" 
) some_alias

is able to deceive PostgreSQL in such a way, that it performs better because, as I guess, it uses the index in this case.

answered Jun 17, 2015 at 18:43

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

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

Collectives™ on Stack Overflow

Speed up Min/Max operation on postgres with index for IN operator query

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