Re: surprising query optimisation

On 01/12/2018 04:33, Stephen Frost wrote:
> Greetings,
>
> * Chris Withers (chris(at)withers(dot)org) wrote:
>> On 28/11/2018 22:49, Stephen Frost wrote:
>>> * Chris Withers (chris(at)withers(dot)org) wrote:
>>>> 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'.
>>> Sounds like a horrible field to have an index on.
>> That's counter-intuitive for me. What leads you to say this and what would
>> you do/recommend instead?
> For this, specifically, it's because you end up with exactly what you
> have: a large index with tons of duplicate values. Indexes are
> particularly good when you have high-cardinality fields. Now, if you
> have a skewed index, where there's one popular value and a few much less
> popular ones, then that's where you really want a partial index (as I
> suggest earlier) so that queries against the non-popular value(s) is
> able to use the index and the index is much smaller.
>
> Of course, for this to work you need to set up the partial index
> correctly and make sure that your queries end up using it.
>
> Thanks!
>
> Stephen

An index simply tells pg which block to look at (assuming that the index
itself is not sufficient to satisfy the query), so if using an index
would still require that pg look at most blocks, then it cheaper to just
scan the whole table - rather than load the index and still look at all
blocks that contain the table data.  I've oversimplified slightly.

An index is best used when using it results in fewer blocks being read
from disk.

Also the use of RAM is better when the size of the index is kept small. 
For example having an index on sex for nurses is a waste of time because
most nurses are female.  However, a partial index (as suggested by
Stephen, for your query) that includes only males could be useful if you
have queries looking for male nurses (assumes male nurses are a very
small fraction of nurses such that most data blocks don't have rows for
males nurses, and the planner knows this).

I once optimised a very complex set queries that made extensive use of
indexes.  However, with the knowledge I have today, I would have most
likely had fewer and smaller indexes.  As I now realize, that some of my
indexes were probably counter productive, especially as I'd given no
thought to how much RAM would be required to host the data plus
indexes!  Fortunately, while the objective was to run all those queries
within 24 hours, they actually only took a couple of hours.

Chris, I would strongly suggest, you read up on the excellent
documentation pg has about indexes, but don't expect to understand it
all at one sitting...

Cheers,
Gavin