Re: Index scan optimization

On 09/22/2014 07:47 AM, Rajeev rastogi wrote:
> I have observed a scope of considerable performance improvement in-case of index by a very minor code change.
> Consider the below schema:
>
> create table tbl2(id1 int, id2 varchar(10), id3 int);
> create index idx2 on tbl2(id2, id3);
>
> Query as:
>                  select count(*) from tbl2 where id2>'a' and id3>990000;
>
> As per current design, it takes following steps to retrieve index tuples:
>
> 1.       Find the scan start position by searching first position in BTree as per the first key condition i.e. as per
id2>'a'
>
> 2.       Then it fetches each tuples from position found in step-1.
>
> 3.       For each tuple, it matches all scan key condition, in our example it matches both scan key condition.
>
> 4.       If condition match, it returns the tuple otherwise scan stops.
>
> Now problem is here that already first scan key condition is matched to find the scan start position (Step-1), so it
isobvious that any further tuple also will match the first scan key condition (as records are sorted).
 
> So comparison on first scan key condition again in step-3 seems to be redundant.
>
> So my proposal is to skip the condition check on the first scan key condition for every tuple.

The same happens in a single-column case. If you have a query like 
"SELECT * FROM tbl2 where id2 > 'a'", once you've found the start 
position of the scan, you know that all the rows that follow match too.

> I would like to submit the patch for this improvement.
> Please provide your feedback. Also let me know if I am missing something.

Yeah, sounds like a good idea. This scenario might not arise very often, 
but it should be cheap to check, so I doubt it will add any measurable 
overhead to the cases where the optimization doesn't help.

- Heikki