Re: Index scan optimization

On Tue, Sep 23, 2014 at 10:38 PM, Rajeev rastogi
<rajeev(dot)rastogi(at)huawei(dot)com> wrote:
> 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).

Hi,

I reviewed index scan optimization patch, the following are the observations.

- Patch applies cleanly.
- Compiles without warnings
- All regress tests are passed.

There is a good performance gain with the patch in almost all scenarios.

I have a question regarding setting of key flags matched. Only the
first key was set as matched
even if we have multiple index conditions. Is there any reason behind that?

If any volatile function is present in the index condition, the index
scan itself is not choosen,
Is there any need of handling the same similar to NULLS?

Thanks for the patch.

Regards,
Hari Babu
Fujitsu Australia