CODEWITHSUNDEEP
sqlpostgresql
Robert Wilson
Robert Wilson

Reputation: 669

Optimise aggregate postgres query with subqueries

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

Upvotes: 2

Views: 91

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

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;

Upvotes: 4

Related Questions