From: Tom Lane <tgl@sss.pgh.pa.us>
To: mario.splivalo@mobart.hr
Cc: Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: SELECT FOR UPDATE performance is bad
Date: Tue, 18 Apr 2006 10:30:58 -0400
Message-ID: <13037.1145370658@sss.pgh.pa.us> (raw)
In-Reply-To: <1145365720.18469.35.camel@localhost.localdomain>
References: <1145365720.18469.35.camel@localhost.localdomain>
Mario Splivalo <mario.splivalo@mobart.hr> writes:
> For the purpose of the application I need to establish some form of
> serialization, therefore I use FOR UPDATE. The query, inside the
> function, is like this:
> pulitzer2=# explain analyze select id FROM messages JOIN
> ticketing_codes_played ON id = message_id WHERE service_id = 1102 AND
> receiving_time BETWEEN '2006-03-01' AND '2006-06-30' FOR UPDATE;
> Hash Join (cost=32131.04..34281.86 rows=627 width=16) (actual
> time=742.806..1491.864 rows=58005 loops=1)
^^^^^
> Now, this query takes between 8 and 30 seconds, wich is a lot, since
> during the day we have almost 20 requests per minute.
Acquiring a row lock separately for each of 58000 rows is not going to
be a cheap operation. Especially not if anyone else is locking any of
the same rows and thereby blocking you. If there is concurrent locking,
you're also running a big risk of deadlock because two processes might
try to lock the same rows in different orders.
Are you really intending to update all 58000 rows? If not, what is
the serialization requirement exactly (ie, what are you trying to
accomplish)? Seems like something about this app needs to be
redesigned.
regards, tom lane
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: tgl@sss.pgh.pa.us, mario.splivalo@mobart.hr
Subject: Re: SELECT FOR UPDATE performance is bad
In-Reply-To: <13037.1145370658@sss.pgh.pa.us>
* 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