From: | Patrick B <patrickbakerbr(at)gmail(dot)com> |
---|---|
To: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> |
Cc: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Slow index scan - Pgsql 9.2 |
Date: | 2017-01-12 00:05:28 |
Message-ID: | CAJNY3isYWZ=0RdZf9qT0L3-_+FENpf+o2reT2twjgB0JW9rNpw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2017-01-11 4:05 GMT+13:00 Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Patrick B | 2017-01-12 00:08:14 | ERROR: canceling statement due to statement timeout |
Previous Message | John R Pierce | 2017-01-11 23:24:56 | Re: Database of articles, LaTeX code and pictures |