Re: Tid scan improvements

Hi,

On 2018-12-20 18:06:41 -0500, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > On 2018-12-20 17:21:07 -0500, Tom Lane wrote:
> >> I'm having a hard time wrapping my mind around why you'd bother with
> >> backwards TID scans.
>
> > I've not followed this thread, but wouldn't that be quite useful to be
> > able to move old tuples to free space earlier in the table?
> > I've written multiple scripts that update the later pages in a table, to
> > force reuse of earlier free pages (in my case by generating ctid = ANY()
> > style queries with all possible tids for the last few pages, the most
> > efficient way I could think of).
>
> Sure, but wouldn't you now write those using something on the order of
>
> WHERE ctid >= '(cutoff_page_here, 1)'
>
> ? I don't see that you'd want to write "ORDER BY ctid DESC LIMIT n"
> because you wouldn't know what value of n to use to get all the
> tuples on some-number-of-ending-pages.

I think you'd want both, to make sure there's not more tuples than
estimated. With the limit calculated to ensure there's enough free space
for them to actually fit.

Greetings,

Andres Freund