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