Re: Secondary index access optimizations

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.

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: http://www.postgrespro.com
The Russian Postgres Company