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.knizh...@postgrespro.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 -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers