Heikki Linnakangas wrote: > Tom Lane wrote: > > Heikki Linnakangas <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend