CODEWITHSUNDEEP
postgresqlpostgresql-9.2
yegor256
yegor256

Reputation: 105213

How to make this PostgreSQL query faster?

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 (!).

Upvotes: 0

Views: 214

Answers (1)

user2722968
user2722968

Reputation: 16535

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

Upvotes: 2

Related Questions