Search Postgresql Archives

Re: Slow index scan - Pgsql 9.2

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





2017-01-11 4:05 GMT+13:00 Tomas Vondra <tomas.vondra@xxxxxxxxxxxxxxx>:
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


Thanks for the reply!

I decided to create a partial index for that query, as it is part of a much bigger one and it is run at all the time.

Patrick

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux