Re: Reasons to reorder results *within* a transaction? - Mailing list pgsql-general

From Evan Jones
Subject Re: Reasons to reorder results *within* a transaction?
Date
Msg-id B3D403E6-A00F-459E-8A5C-F846884CE82C@evanjones.ca
Whole thread Raw
In response to Re: Reasons to reorder results *within* a transaction?  (Kevin Grittner <kgrittn@ymail.com>)
List pgsql-general
On Oct 4, 2013, at 13:03 , Kevin Grittner <kgrittn@ymail.com> wrote:
> That is not a valid assumption.  For one thing, the default
> transaction isolation level is read committed, and at that
> isolation level you are not guaranteed to even get the same *rows*
> running the same query twice within the same transaction, much less
> in the same order.

I guess I should have mentioned that we are using serializable snapshot isolation (thanks for that, BTW!)

> if there is already a sequential
> scan in progress for another process, the new one will start at the
> point the other one is at, and "wrap around".  This can save a lot
> of physical disk access, resulting in better performance.

OH! This totally, totally makes sense. This is *exactly* the kind of thing I was looking for, and I'll bet that is
exactlywhat was happening in our case. The table is pretty small, so Postgres explain says it is doing a full table
scanfor this query. Thanks for the speedy insightful answer! 

This is yet another example of something that when tracking down the bug, we knew immediately it was incorrect and
probablywrong, but sometimes you don't notice these things the first time. The joys of software. 

Evan

--
Work: https://www.mitro.co/    Personal: http://evanjones.ca/



pgsql-general by date:

Previous
From: Kevin Grittner
Date:
Subject: Re: Reasons to reorder results *within* a transaction?
Next
From: Alvaro Herrera
Date:
Subject: Re: Reasons to reorder results *within* a transaction?