Slow Postgres Query when using GROUP BY

Question

I have table in postgres database. After the row reach 2 mio rows, the query become slower. This is my query

SELECT
c.source,
c.destination,
c.product_id,
sum(c.weight),
count(c.weight),
c.owner_id 
FROM stock c 
GROUP BY c.source, c.destination, c.product_id, c.owner_id;

I already add index

CREATE INDEX stock_custom_idx ON public.stock USING btree (source, destination, product_id, owner_id)

The query is very slow, so I do explain analyze, but the index not called.

So, how to optimize this query, because its take too long time and not return data?

This looks really strange to me, because the exact index you added should really be helping the query. Are you sure you ran EXPLAIN after adding this index and not at some other point? — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 14, 2021 at 9:26
What was the size of the table when you ran EXPLAIN? Note that for fairly small table sizes, Postgres may choose to not use the index. The big issue here would be if you have a very large table plus the index, but Postgres is still choosing not to use that index. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 14, 2021 at 9:29
Please edit your question and add the execution plan generated using explain (analyze, buffers, format text) (not just a "simple" explain) as formatted text and make sure you preserve the indention of the plan. Paste the text, then put ``` on the line before the plan and on a line after the plan. Please also include complete create index statements for all indexes as well. — user330315
– user330315, Commented Jun 14, 2021 at 10:38
Your query needs to read and group all rows of the table - no index is going to speed that up. — user330315
– user330315, Commented Jun 14, 2021 at 10:39

Islingre · Accepted Answer · 2021-06-14 13:24:14Z

2

Try this index:

CREATE INDEX better_index ON public.stock USING btree
    (source, destination, product_id, owner_id, weight);

If you do not include the weight, this information still needs to be fetched from the table, thus you have a full table scan.

With the new index, you should have an index only scan. Also, the query planner can make use of the sorting order of the index for the grouping (just as it could have done with your index).

In newer versions of PostgreSQL, there would also exist the INCLUDE clause, where you can "add" columns to an index without this having any impact on the sorting order (the data is there, but this part of the data is not sorted). This would make the index yet another bit more performant for your query, I guess.

answered Jun 14, 2021 at 13:24

Islingre

2,37810 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

Slow Postgres Query when using GROUP BY

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