Running a long sorting query in PostgreSQL

Question

I have this bundles table and each bundles can have between 0 and n items from the items table, this is my query to get the median amount of items

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY r.count) AS median
FROM (
    SELECT bundle.id, COUNT(*)
    FROM items
    JOIN bundle ON bundle.id=items.bundle_id
    GROUP BY bundle.id
) AS r;

But this query takes too long and times out, what would be the best way to split this query into batches using postgreSQL?

I hope you have an index on bundle.id and another one on items.bundle_id? How long does the inner SQL statement take ? How long is "too long time" ? How does the EXPLAIN PLAN look like (see: stackoverflow.com/questions/61819909/… and stackoverflow.com/questions/117262/… and Postgres query is sometimes taking a very long time — Luuk
– Luuk, Commented Jun 18, 2024 at 16:05

Jon Waterhouse · Accepted Answer · 2024-06-18 15:49:59Z

1

I think the only sensible way of splitting the query is to create a temporary table with the result of select bundle_id,count(*) ct from items (adding the bundle table in that first query gains you nothing), and then possibly create an index on ct.

If you did it in batches, what would you measure, and how would you come up with your result? For example, if you have two groups: 1,2,8,9,10 and 1,2,2,2,3 then the median of the first group is 8 and the median of the second group is 2. The median of the whole group is 2.

answered Jun 18, 2024 at 15:49

Jon Waterhouse

962 bronze badges

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

1 Comment

Sassy Over a year ago

I figured this was the answer, and that is exactly what I need, given groups 1,2,8,9,10 and 1,2,2,2,3 , them combined and sorted => 1,1,2,2,2,2,3,8,9,10 and the median would be 1,1,2,2,2,2,3,8,9,10

Collectives™ on Stack Overflow

Running a long sorting query in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related