[Pypgsql-devel] Note regarding bigint index performance...

[Mike C. F. <mcf...@ro...>]

Ivar Zarrans and myself have both encountered (separately) an 
interesting "bug" in PostgreSQL performance.  Basically, the query 
planner, if given a query like this:

    UPDATE trigger SET frozen=False WHERE object_id=23;

where object_id is an indexed big-int field of trigger, will *always* 
choose a sequential scan.  On a large table, that is going to be 
unacceptably slow, especially when one is writing the queries with the 
assumption that you are using an indexed field.  The problem here is 
that, even when a PgInt8 is passed as the object_id value, it is 
inserted into the query as an int4 literal (a simple number).  The 
server then sees a request to search for the field with a value of a 
*different type* than the available index, so ignores the index.

Apparently PostgreSQL 7.5 has a fix checked in for it, however, in the 
meantime, any 7.4 or below DB using bigints for specifying where's on 
large tables will be ridiculously slow on UPDATE (think multiple whole 
minutes to update a single row of a 88,000 row table).  Apparently this 
is a general problem, int2 indexed fields also won't use the index if an 
int4 literal is specified as the search term, though I'd guess that int8 
is the most common problem, as both people discovering it were using that.

This can be worked around by:

    * adding quotes around the value '23', which will make it an unknown
      type
    * coercing value to target type; i.e. 23::bigint anywhere you are
      using an integer as a query term against a field that is int8 and
      for which you need indexing
    * altering all queries to explicitly coerce to the target type
    * Greg Stark, on the postgres performance list, suggested using "the
      new binary protocol for parameters [within pyPgSQL] so that there
      are no quoting issues at all", but that's not going to work with
      older PostgreSQL versions, and still wouldn't help if someone had
      passed a regular integer (it would help if a PgInt8 was passed,
      however).

The first two need to be done by creating a subclass or wrapper of 
PgInt8 which does the munged representation in it's __str__/__repr__ 
method (so that pyPgSQL won't complain about an improperly formatted 
value).  Here's a sample class:

    class Wrapper:
        def __init__( self, value ):
            self.value = value
        def __repr__( self ):
            return '%s::int8'%(self.value, )
        __str__ = __repr__

It doesn't address the underlying problem, but it should allow people to 
move forward if they find themselves in this situation.

Enjoy,
Mike

_______________________________________
  Mike C. Fletcher
  Designer, VR Plumber, Coder
  http://members.rogers.com/mcfletch/