Re: Secondary index access optimizations

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>
Cc: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Secondary index access optimizations
Date: 2017-09-04 09:59:25
Message-ID: 64e684ec-afe6-8bbe-ca1a-221d6eee0409@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Konstantin,

On 2017/09/04 18:19, Konstantin Knizhnik wrote:
> On 04.09.2017 05:38, Amit Langote wrote:
>> On 2017/09/02 12:44, Thomas Munro wrote:
>>> On Wed, Aug 16, 2017 at 9:23 PM, Konstantin Knizhnik
>>> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>>>> postgres=# explain select * from bt where k between 1 and 20000 and v
>>>> = 100;
>>>>                                QUERY PLAN
>>>> ----------------------------------------------------------------------
>>>>   Append  (cost=0.29..15.63 rows=2 width=8)
>>>>     ->  Index Scan using dti1 on dt1  (cost=0.29..8.30 rows=1 width=8)
>>>>           Index Cond: (v = 100)
>>>>     ->  Index Scan using dti2 on dt2  (cost=0.29..7.33 rows=1 width=8)
>>>>           Index Cond: (v = 100)
>>>>           Filter: (k <= 20000)
>>>> (6 rows)
>>> +1
>>>
>>> This seems like a good feature to me: filtering stuff that is
>>> obviously true is a waste of CPU cycles and may even require people to
>>> add redundant stuff to indexes.  I was pondering something related to
>>> this over in the partition-wise join thread (join quals that are
>>> implied by partition constraints and should be discarded).
>>>
>>> It'd be interesting to get Amit Langote's feedback, so I CC'd him.
>>> I'd be surprised if he and others haven't got a plan or a patch for
>>> this down the back of the sofa.
>> I agree that that's a good optimization in the cases it's correct.  Given
>> that check_index_predicates() already applies the same optimization when
>> considering using a partial index, it might make sense to try to do the
>> same even earlier for the table itself using its CHECK / NOT NULL
>> constraints as predicates (I said *earlier* because
>> relation_excluded_by_constrains happens for a relation before we look at
>> its indexes).  Also, at the end of relation_excluded_by_constraints() may
>> not be such a bad place to do this.
>>
>> By the way, I read in check_index_predicates() that we should not apply
>> this optimization if the relation in question is a target of UPDATE /
>> DELETE / SELECT FOR UPDATE.
>
> Please correct me if I wrong, but it seems to me that in case of table
> constraints it is not necessary to specially handle update case.
> As far as I understand we need to leave predicate in the plan in case of
> partial indexes because due to "read committed" isolation policy
> we may need to recheck that tuple still satisfies update condition (tuple
> can be changed by some other committed transaction while we are waiting
> for it and not satisfying this condition any more).
> But no transaction can change tuple in such way that it violates table
> constraints,  right? So we do not need to recheck it.

Actually, I don't really know why check_index_predicates() skips this
optimization in the target relation case, just wanted to point out that
that's so.

Thinking a bit from what you wrote, maybe we need not worry about
EvalPlanQual in the context of your proposed optimization based on the
table's constraints.

> Concerning your suggestion to merge check_index_predicates() and
> remove_restrictions_implied_by_constraints() functions: may be it can be
> done, but frankly speaking I do not see much sense in it - there are too
> much differences between this functions and too few code reusing.

Maybe, you meant to address Thomas here. :) Reading his comment again, I
too am a bit concerned about destructively modifying the input rel's
baserestrictinfo. There should at least be a comment that that's being done.

Thanks,
Amit

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2017-09-04 10:03:57 Re: Re: [COMMITTERS] pgsql: pg_rewind: Fix some problems when copying files >2GB.
Previous Message Aleksander Alekseev 2017-09-04 09:57:20 Re: [PATCH] Improve geometric types