From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Chris Withers <chris(at)withers(dot)org>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: surprising query optimisation |
Date: | 2018-11-28 22:32:57 |
Message-ID: | 86d92c50-4f1e-3178-5de0-1d57f551bcb8@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Gavin Flower | 2018-11-28 22:41:09 | Re: surprising query optimisation |
Previous Message | Chris Withers | 2018-11-28 22:26:48 | surprising query optimisation |