On 22 September 2014 19:17, Heikki Linnakangas wrote:
> On 09/22/2014 04:45 PM, Tom Lane wrote:
> > Heikki Linnakangas <hlinnakan...@vmware.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

Attachment: index_scan_opt.patch
Description: index_scan_opt.patch

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to