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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend