Re: [GENERAL] Slow index scan - Pgsql 9.2

On 01/10/2017 04:05 AM, Patrick B wrote:
>     ​3,581​ individual pokes into the heap to confirm tuple visibility
>     and apply the deleted filter - that could indeed take a while.
>     David J.
>
>
> I see.. The deleted column is:
>
> deleted boolean
>
> Should I create an index for that? How could I improve this query?
>
>
>     Does it execute as slowly when you run it for a 2nd time?
>
>
> No, it doesn't. I think it's because of cache?
>
>
>     I would think because of the NOT "deleted" clause. Which is
>     interesting, because that's a column which you conveniently didn't
>     include in the definition below.
>
>
> My mistake.
>
>
> Would an Index be sufficient to solve the problem?
>

Not a separate index - the query probably would not benefit from two
separate indexes. But you can amend the existing index, to allow
index-only scans, i.e. creating an index like this:

   CREATE INDEX ON (clientid, is_demo, deleted, id, job, job_share_mode)

This will make the index larger, but it should allow index-only scans.

The other thing you could try is partial index, i.e.

   CREATE INDEX ON (clientid) WHERE NOT is_demo AND NOT deleted;

You can also combine those approaches, but you'll have to include all
columns into the index, even those in the index predicate:

   CREATE INDEX ON (clientid, is_demo, deleted, id, job, job_share_mode)
   WHERE NOT is_demo AND NOT deleted;

I'd bet all of those will outperform the current plan.

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services