How to make this PostgreSQL query faster?

Question

This is the table:

dt VARCHAR(65)
ct VARCHAR(65)
amount INT

This is the query:

SELECT SUM(CASE WHEN dt='peter' THEN amount ELSE -amount END) 
FROM receipt WHERE dt='peter' OR ct='peter'

I have 800k records in the table. And I have indexes on dt, ct, amount, and ct,dt. At the moment this query takes over 5 minutes (!).

Show us the execution plan (ideally as explain analyze)

user330315
– user330315

2013-09-11 11:21:20 +00:00
Commented Sep 11, 2013 at 11:21 — user330315
– user330315, Commented Sep 11, 2013 at 11:21

user2722968 · Accepted Answer · 2013-09-11 11:23:06Z

2

You can do

SELECT SUM(s) AS ss
FROM (SELECT SUM(amount) AS s
      FROM receipt
      WHERE dt = 'peter'
      UNION
      SELECT -SUM(amount)
      FROM receipt
      WHERE dt = 'peter')

This query should run faster than your original one. Otherwise you should normalize the dt/ct columns to use joins for better performance

answered Sep 11, 2013 at 11:23

user2722968

17.4k5 gold badges58 silver badges77 bronze badges

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

Collectives™ on Stack Overflow

How to make this PostgreSQL query faster?

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