Re: Question about SQL performance

> What sort of speed increase is there usually with binding parameters
> (and thus preparing statements) v. straight sql with interpolated
> variables? Will Postgresql realize that the following queries are
> effectively the same (and thus re-use the query plan) or will it think
> they are different?
>
> SELECT * FROM mytable WHERE item = 5;
> SELECT * FROM mytable WHERE item = 10;

No, if you send the above as text (not prepared) they are two different
queries.
Postgres' query executor is so fast that parsing and planning can take
longer than query execution sometimes. This is true of very simple selects
like above, or some very complex queries which take a long time to plan
but don't actually process a lot of rows.
I had this huge query (1 full page of SQL) with 5 joins, aggregates and
subqueries, returning about 30 rows ; it executed in about 5 ms, planning
and parsing time was significant...

> Obviously to me or you they could use the same plan. From what I
> understand (correct me if I'm wrong), if you use parameter binding -
> like "SELECT * FROM mytable WHERE item = ?" - Postgresql will know that
> the queries can re-use the query plan, but I don't know if the system
> will recognize this with above situation.

It depends if your client library is smart enough to prepare the
statements...

> Also, what's the difference between prepared statements (using PREPARE
> and EXECUTE) and regular functions (CREATE FUNCTION)? How do they impact
> performance? From what I understand there is no exact parallel to stored
> procedures (as in MS SQL or oracle, that are completely precompiled) in
> Postgresql. At the same time, the documentation (and other sites as
> well, probably because they don't know what they're talking about when
> it comes to databases) is vague because PL/pgSQL is often said to be
> able to write stored procedures but nowhere does it say that PL/pgSQL
> programs are precompiled.

PG stores the stored procedures as text. On first invocation, in each
connection, they are "compiled", ie. all statements in the SP are
prepared, so the first invocation in a connection is slower than next
invocations. This is a problem if you do not use persistent connections.

A simple select, when prepared, will take about 25 microseconds inside a
SP and 50-100 microseconds as a query over the network. If not prepared,
about 150 µs or 2-3x slower.

FYI Postgres beats MyISAM on "small simple selects" if you use prepared
queries.

I use the following Python code to auto-prepare my queries :

db = PGConn( a function that returns a DB connection )
db.prep_exec( "SELECT * FROM stuff WHERE id = %s", 1 ) # prepares and
executes
db.prep_exec( "SELECT * FROM stuff WHERE id = %s", 2 ) # executes only

class PGConn( object ):

def __init__( self, db_connector ):
self.db_connector = db_connector
self.reconnect()

def reconnect( self ):
self.prep_cache = {}
self.db = self.db_connector()
self.db.set_isolation_level( 0 ) # autocommit

def cursor( self ):
# return self.db.cursor( cursor_factory=psycopg2.extras.DictCursor )
return self.db.cursor( )

def execute( self, sql, *args ):
cursor = self.cursor()
try:
cursor.execute( sql, args )
except:
cursor.execute( "ROLLBACK" )
raise
return cursor

def executemany( self, sql, *args ):
cursor = self.cursor()
try:
cursor.executemany( sql, args )
except:
cursor.execute( "ROLLBACK" )
raise
return cursor

def prep_exec( self, sql, *args ):
cursor = self.cursor()
stmt = self.prep_cache.get( sql )
if stmt is None:
name = "stmt_%s" % (len( self.prep_cache ) + 1)
if args:
prep = sql % tuple( "$%d"%(x+1) for x in xrange( len( args )) )
else:
prep = sql
prep = "PREPARE %s AS %s" % (name, prep)
cursor.execute( prep )
if args:
stmt = "EXECUTE %s( %s )" % (name, ", ".join( ["%s"] * len( args ) ))
else:
stmt = "EXECUTE %s" % (name,)
self.prep_cache[ sql ] = stmt

try:
cursor.execute( stmt, args )
except Exception, e:
traceback.print_exc()
print "Error while executing prepared SQL statement :", stmt
print "Arguments :", args
print "Original SQL is :", sql
cursor.execute( "ROLLBACK" )
raise

return cursor