From: Ivar Zarans <iff@alcaron.ee>
To: pgsql-performance@postgresql.org
Subject: Re: Slow UPADTE, compared to INSERT
Date: Fri, 5 Dec 2003 14:38:43 +0200
Message-ID: <20031205123843.GA27170@alcaron.ee> (raw)
In-Reply-To: <200312051008.20317.dev@archonet.com>
References: <20031204185751.GA15893@alcaron.ee>
<20031205014514.GA21079@alcaron.ee>
<20031205020728.GA21440@alcaron.ee>
<200312051008.20317.dev@archonet.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: iff@alcaron.ee
Subject: Re: Slow UPADTE, compared to INSERT
In-Reply-To: <20031205123843.GA27170@alcaron.ee>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox