Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, May 3, 2011 at 10:07 PM, Kevin Grittner
> <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> ... on a toy table with contrived values. How different is this
>> from the often-asked question about why a query against a
>> four-line table is not using the index they expect, and how can
>> we expect it to scale if it doesn't? I agree that it's not
>> unreasonable for someone to ask either question. If my response
>> falls short, I'm game to try again.
>
> I guess what surprises me about this a bit is that we have to
> predicate-lock the whole table even if we're not actually looking
> at all the rows. I can sort of see why that's necessary, but I'm
> a bit fuzzy on the details, and it does seem a little unfortunate
> in this instance...
Well, as far as I can tell, every production-quality database with
predicate locking models the predicates based on the rows actually
accessed. Until now, that has been every popular SQL database
except PostgreSQL and Oracle. That makes predicate locking
sensitive to the plan chosen. It was because of this that I thought
it might be wise to include a bump to the seq_page_cost and/or
cpu_tuple_cost for plans inside a serializable transaction. This
would encourage indexed access rather than a table scan at an
earlier threshold, thereby reducing false positive serialization
failures. At the time the suggestion got a rather cool reception.
Is it time to reconsider that?
On the other hand, as a shop where we're probably going to set
default_transaction_isolation = serializable in our postgresql.conf
files and include trigger checks that we're running at that level,
we can just boost those globally. That may also work for others.
Once I wrap up these changes to our replication system I'm in the
middle of coding, I'll see about getting all our development
machines onto 9.1beta with default serialization and see how much
trouble our apps have. Even on our development machines we run with
a copy of real data from a circuit court county database.
-Kevin