Optimise aggregate postgres query with subqueries

Question

I have an aggregate query with about 4 subqueries. The issue is, with only 2500+ rows, it takes about 5~ sec to complete. Is there a way any of you can help optimize this query?

Any help will be much appreciated.

SELECT * FROM (
SELECT
    DISTINCT ON(i.name) i.name,
    i.invoice_number,
    i.created_date,
    (SELECT COUNT (i2.customer_id) FROM invoices i2 WHERE i.invoice_number = i2.invoice_number) AS total_customers,
    (SELECT COUNT (i2.status) FROM invoices i2 WHERE i.invoice_number = i2.invoice_number AND i2.status = 'PAID') AS total_full_payments,
    (SELECT COUNT (i2.status) FROM invoices i2 WHERE i.invoice_number = i2.invoice_number AND i2.status = 'PARTIALLY_PAID') AS total_part_payments,
    (SELECT COUNT (i2.status) FROM invoices i2 WHERE i.invoice_number = i2.invoice_number AND i2.status = 'UNPAID') AS total_no_payments
FROM
    invoices i WHERE i.client_id = 2
GROUP BY
    i.name,
    i.customer_id,
    i.invoice_number,
    i.created_date
) i
ORDER BY i.created_date DESC
OFFSET 0 LIMIT 10

Please provide sample data, desired results, and an explanation of what the code is supposed to be doing. — Gordon Linoff
– Gordon Linoff, Commented Aug 22, 2020 at 12:47

Gordon Linoff · Accepted Answer · 2020-08-22 12:50:29Z

4

Use conditional aggregation! It is rather unclear what your query is doing, but this seems like a reasonable query:

SELECT i.name, MIN(i.created_date),
       COUNT(*) as total_customers,
       COUNT(*) FILTER (WHERE i.status = 'PAID') AS total_full_payments,
       COUNT(*) FILTER (WHERE i.status = 'PARTIALLY_PAID') AS total_part_payments,
       COUNT(*) FILTER (WHERE i.status = 'UNPAID') AS total_no_payments
FROM invoices i
WHERE i.client_id = 2
GROUP BY i.name
ORDER BY MIN(created_date) DESC;

answered Aug 22, 2020 at 12:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Cole Over a year ago

is the FILTER keyword specific to postgresql? This seems similar to SUM(CASE WHEN i.status = 'PAID' THEN 1 ELSE 0) but I like this syntax better. +1

Gordon Linoff Over a year ago

@Cole . . . It is standard SQL. However, Postgres is one of the few databases that support it. In lieu of the SUM(CASE), though, I would suggest SUM( (i.status = 'PAID')::INT ).

Robert Wilson Over a year ago

Tested and it's very performant. Works pretty well. Thanks

Collectives™ on Stack Overflow

Optimise aggregate postgres query with subqueries

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related