Re: surprising query optimisation

On 11/28/18 2:26 PM, 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 

The way I see it is state = "something" is a confined question. state != 
'something' is potentially unbounded.

Does EXPLAIN ANALYZE shed any light?

> 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
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com