Re: Synchronized scans

Heikki Linnakangas wrote:
> Tom Lane wrote:
> > Heikki Linnakangas <heikki(at)enterprisedb(dot)com> writes:
> >> For the record, this patch has a small negative impact on scans like
> >> "SELECT * FROM foo LIMIT 1000". If such a scan is run repeatedly, in CVS
> >> HEAD the first 1000 rows will stay in buffer cache, but with the patch
> >> each scan will start from roughly where previous one stopped, requiring
> >> more pages to be read from disk each time. I don't think it's something
> >> to worry about in practice, but I thought I'd mention it.
> >
> > Urgh. The answers change depending on (more or less) the phase of the
> > moon? I've got a serious problem with that. You might look back to
> > 1997 when GEQO very nearly got tossed out entirely because it destroyed
> > reproducibility of query results.
>
> That's a very fundamental result of this patch, unfortunately. It only
> happens on scans on tables larger than the threshold. And because we
> only report the current scan location every 128KB, if you repeat the
> same SELECT .. LIMIT X query with no other scanners on that table,
> you'll get the same results as long as X is smaller than 128KB.
>
> I thought we've been through this issue already...

Agreed. I thought we always said that a LIMIT without an ORDER BY was
meaningless, particuarly because an intervening UPDATE could have moved
rows to another place in the table. In fact, at one time we considered
prevening LIMIT without ORDER BY because it was meaningless, but decided
if people want unstable results, they should be able to get them.

An argument could be made that a LIMIT without ORDER BY on a table
locked read-only should be stable.

As I understand it, the problem is that while currently LIMIT without
ORDER BY always starts at the beginning of the table, it will not with
this patch. I consider that acceptable.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +