Re: Optimize single tuple fetch from nbtree index

Hello everyone.

I am also was looking into possibility of such optimisation few days ago (attempt to reduce memcpy overhead on IndexOnlyScan).

One thing I noticed here - whole page is scanned only if index quals are "opened" at some side.

So,  in case of
     SELECT* FROM tbl WHERE k=:val AND ts<=:timestamp ORDER BY k, ts DESC LIMIT 1;
whole index page will be read.

But
    SELECT* FROM tbl WHERE k=:val AND ts<=:timestamp AND ts<:=timestamp - :interval  ORDER BY k, ts DESC LIMIT 1;
is semantically the same, but only few :interval records will be processed.

So, you could try to compare such query in your benchmarks.

Also, some info about current design is contained in src\backend\access\nbtree\README ("To minimize lock/unlock traffic, an index scan always searches a leaf page
to identify all the matching items at once").

Thanks,
 Michail.