Re: [WIP PATCH] Index scan offset optimisation using visibility map

Hello.

Thanks a lot for your feedback. I'll try to update patch in few days
(currently stuck at small performance regression in unknown place).

Regarding issue with delete: yes, it is valid point, but record removing
should clear visibility buffer - and tuple will be fetched from heap to
test its existance. In such case expression are not evaluated at all. Not
sure for delete and query in same transaction - I'll check.
Also, need to recheck possible issues with EvalPlanQual.

PS.

Updated link in case someone want to briefly see code until git patch is
ready:
https://github.com/michail-nikolaev/postgres/compare/e3eb8be77ef82ccc8f87c515f96d01bf7c726ca8...michail-nikolaev:index_only_fetch?ts=4

сб, 14 июл. 2018 г. в 0:17, Heikki Linnakangas <hlinnaka(at)iki(dot)fi>:

> On 21/05/18 18:43, Michail Nikolaev wrote:
> > Hello everyone.
> > This letter related to “Extended support for index-only-scan” from my
> > previous message in the thread.
> >
> > WIP version of the patch is ready for a while now and I think it is time
> to
> > resume the work on the feature. BTW, I found a small article about Oracle
> > vs Postgres focusing this issue -
> > https://blog.dbi-services.com/postgres-vs-oracle-access-paths-viii/
> >
> > Current WIP version of the patch is located here -
> >
> https://github.com/postgres/postgres/compare/88ba0ae2aa4aaba8ea0d85c0ff81cc46912d9308...michail-nikolaev:index_only_fetch
> ,
> > passing all checks. In addition, patch includes small optimization for
> > caching of amcostestimate results.
>
> Please submit an actual path, extracted e.g. with "git format-patch
> -n1", rather than a link to an external site. That is a requirement for
> archival purposes, so that people reading the email archives later on
> can see what was being discussed. (And that link doesn't return a proper
> diff, anyway.)
>
> > For now, I decide to name the plan as “Index Only Fetch Scan”. Therefore:
> > * In case of “Index Scan” – we touch the index and heap for EVERY tuple
> we
> > need to test
> > * For “Index Only Scan” – we touch the index for every tuple and NEVER
> > touch the heap
> > * For “Index Only Fetch Scan” – we touch the index for every tuple and
> > touch the heap for those tuples we need to RETURN ONLY.
>
> Hmm. IIRC there was some discussion on doing that, when index-only scans
> were implemented. It's not generally OK to evaluate expressions based on
> data that has already been deleted from the table. As an example of the
> kind of problems you might get:
>
> Imagine that a user does "DELETE * FROM table WHERE div = 0; SELECT *
> FROM table WHERE 100 / div < 10". Would you expect the query to throw a
> "division by zero error"? If there was an index on 'div', you might
> evaluate the "100 / div" expression based on values from the index,
> which still includes entries for the just-deleted tuples with div = 0.
> They would be filtered out later, after performing the visibility
> checks, but it's too late if you already threw an error.
>
> Now, maybe there's some way around that, but I don't know what. Without
> some kind of a solution, this won't work.
>
> - Heikki
>