Re: Slow UPADTE, compared to INSERT

On Fri, Dec 05, 2003 at 10:08:20AM +0000, Richard Huxton wrote:

> > numeric constant must be typecasted in order to function properly.
> >
> > Is this normal behaviour of fields with bigint type?
>
> As Christopher says, normal (albeit irritating). Not sure it applies here -
> all the examples you've shown me are using the index.

I guess i have solved this mystery. Problem appears to be exactly with
this - numeric constant representation in query.

I am using PyPgSQL for PostgreSQL access and making update queries as this:

qry = "UPDATE table1 SET status = %s WHERE recid = %s"
cursor.execute(qry, status, recid)

Execute method of cursor object is supposed to merge "status" and
"recid" values into "qry", using proper quoting. When i started to play
around with debug information i noticed, that this query used sequential
scan for "recid". Then i also noticed, that query, sent to server looked
like this:
"UPDATE table1 SET status = 'SKIP' WHERE recid = 199901"

Sure enough, when i used psql and EXPLAIN on this query, i got query
plan with sequential scan. And using recid value as string or typecasted
integer gave correct results with index scan. I wrote about this in my
previous message.

It seems, that PyPgSQL query quoting is not aware of this performance
problem (to which Cristopher referred) and final query, sent to server
is correct SQL, but not correct, considering PostgreSQL bugs.

One more explanation - previously i posted some logs, showing correct
query, using index scan, but still taking 0.29 seconds. Reason for this
delay is logging itself - it generates enough IO traffic to have impact
on query speed. With logging disabled, this query takes around 0.0022
seconds, which is perfectly normal.

Finally - what would be correct solution to this problem? Upgrading to
7.5 CVS is not an option :) One possibility is not to use PyPgSQL
variable substitution and create every query "by hand" - not very nice
solution, since variable substitution and quoting is quite convenient.

Second (and better) possibility is to ask PyPgSQL develeopers to take care
of PostgreSQL oddities.

Any other suggestions?

--
Ivar