On 29/11/2018 11:26, Chris Withers wrote:
Hi All,
We have an app that deals with a lot of queries, and we've been slowly
seeing performance issues emerge. We take a lot of free form queries
from users and stumbled upon a very surprising optimisation.
So, we have a 'state' column which is a 3 character string column with
an index on it. Despite being a string, this column is only used to
store one of three values: 'NEW', 'ACK', or 'RSV'.
One of our most common queries clauses is "state!='RSV'" and we've
found that by substituting this clause with "state='ACK' or
state='NEW'" wherever it was used, we've dropped the postgres server's
load average from 20 down to 4 and the CPU usage from 60% in user
space down to <5%.
This seems counter-intuitive to me, so thought I'd ask here. Why would
this be likely to make such a difference? We're currently on 9.4, is
this something that's likely to be different (better? worse?) if we
got all the way up to 10 or 11?
cheers,
Chris
At a guess...
"state!='RSV'" ==> pg only has to check one value
and
"state='ACK' or state='NEW'" ==> pg has to check two values
so I would expect the '!=' to be faster.
Cheers,
Gavin
