Re: Index scan optimization

On 22 September 2014 19:17, Heikki Linnakangas wrote:

> On 09/22/2014 04:45 PM, Tom Lane wrote:
> > Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> writes:
> >> On 09/22/2014 07:47 AM, Rajeev rastogi wrote:
> >>> 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.
> >
> > ... unless you're doing a backwards scan.
>
> Sure. And you have to still check for NULLs. Have to get the details
> right..

I have finished implementation of the discussed optimization.
I got a performance improvement of around "30%" on the schema and data shared in earlier mail.

I also tested for the index scan case, where our optimization is not done and observed that there
is no effect on those query because of this change.

Change details:
I have added a new flag as SK_BT_MATCHED as part of sk_flags (ScanKey structure), the value used for this
0x00040000, which was unused.
Inside the function _bt_first, once we finish finding the start scan position based on the first key,
I am appending the flag SK_BT_MATCHED to the first key.
Then in the function _bt_checkkeys, during the key comparison, I am checking if the key has SK_BT_MATCHED flag set, if yes then
there is no need to further comparison. But if the tuple is having NULL value, then even if this flag is set, we will continue
with further comparison (this handles the Heikki point of checking NULLs).

I will add this patch to the next CommitFest.

Please let me know your feedback.

Thanks and Regards,
Kumar Rajeev Rastogi