From: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> |
---|---|
To: | Patrick B <patrickbakerbr(at)gmail(dot)com>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Slow index scan - Pgsql 9.2 |
Date: | 2017-01-10 15:05:52 |
Message-ID: | 1fdb22e5-eb6f-236a-56cb-469e046e1787@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | George Neuner | 2017-01-10 17:09:01 | Re: Not clear how to switch role without permitting switch back |
Previous Message | Tom DalPozzo | 2017-01-10 13:35:16 | Re: requested timeline doesn't contain minimum recovery point |