2017-01-11 4:05 GMT+13:00 Tomas Vondra <tomas.von...@2ndquadrant.com>:
> 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
