Re: Secondary index access optimizations

From: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
To: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>, 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:19:24
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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;
>>> ----------------------------------------------------------------------
>>> 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 /
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.

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.

Konstantin Knizhnik
Postgres Professional:
The Russian Postgres Company

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Sokolov Yura 2017-09-04 09:29:06 Re: Proposal: pg_rewind to skip config files
Previous Message Chris Travers 2017-09-04 08:53:15 Proposal: pg_rewind to skip config files