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.