Search Postgresql Archives

Re: surprising query optimisation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux