Robert Haas <robertmh...@gmail.com> wrote: > On Tue, May 3, 2011 at 10:07 PM, Kevin Grittner > <kevin.gritt...@wicourts.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
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
