pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT is immediate but the UPDATE takes forever
10+ messages / 5 participants
[nested] [flat]
* SELECT is immediate but the UPDATE takes forever
@ 2010-12-07 11:11 Raimon Fernandez <coder@montx.com>
1 sibling, 1 reply; 10+ messages in thread
From: Raimon Fernandez @ 2010-12-07 11:11 UTC (permalink / raw)
To: pgsql-general List <pgsql-general@postgresql.org>
Hi,
I want to understand why one of my postgresql functions takes an eternity to finish.
Here's an example:
UPDATE comptes SET belongs_to_compte_id=42009 WHERE (codi_compte LIKE '10000%' AND empresa_id=2 AND nivell=11); // takes forever to finish
QUERY PLAN
--------------------------------------------------------------------------------------------
Seq Scan on comptes (cost=0.00..6559.28 rows=18 width=81)
Filter: (((codi_compte)::text ~~ '10000%'::text) AND (empresa_id = 2) AND (nivell = 11))
(2 rows)
but the same SELECT count, it's immediate:
SELECT count(id) FROM comptes WHERE codi_compte LIKE '10000%' AND empresa_id=2 AND nivell=11;
what I'm doing wrong ?
thanks,
regards,
r.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SELECT is immediate but the UPDATE takes forever
@ 2010-12-07 14:45 Michał Roszka <mike@if-then-else.pl>
parent: Raimon Fernandez <coder@montx.com>
0 siblings, 2 replies; 10+ messages in thread
From: Michał Roszka @ 2010-12-07 14:45 UTC (permalink / raw)
To: Raimon Fernandez <coder@montx.com>; +Cc: pgsql-general List <pgsql-general@postgresql.org>
Quoting Raimon Fernandez <coder@montx.com>:
> I want to understand why one of my postgresql functions takes an
> eternity to finish.
>
> Here's an example:
>
> UPDATE comptes SET belongs_to_compte_id=42009 WHERE (codi_compte LIKE
> '10000%' AND empresa_id=2 AND nivell=11); // takes forever to finish
[...]
> but the same SELECT count, it's immediate:
>
> SELECT count(id) FROM comptes WHERE codi_compte LIKE '10000%' AND
> empresa_id=2 AND nivell=11;
Maybe there is any check or constraint on belongs_to_compte_id.comptes that
might take longer?
Cheers,
-Mike
--
Michał Roszka
mike@if-then-else.pl
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SELECT is immediate but the UPDATE takes forever
@ 2010-12-07 15:14 Raimon Fernandez <coder@montx.com>
parent: Michał Roszka <mike@if-then-else.pl>
1 sibling, 1 reply; 10+ messages in thread
From: Raimon Fernandez @ 2010-12-07 15:14 UTC (permalink / raw)
To: Michał Roszka <mike@if-then-else.pl>; +Cc: pgsql-general List <pgsql-general@postgresql.org>
On 7dic, 2010, at 15:45 , Michał Roszka wrote:
> Quoting Raimon Fernandez <coder@montx.com>:
>
>> I want to understand why one of my postgresql functions takes an
>> eternity to finish.
>>
>> Here's an example:
>>
>> UPDATE comptes SET belongs_to_compte_id=42009 WHERE (codi_compte LIKE
>> '10000%' AND empresa_id=2 AND nivell=11); // takes forever to finish
>
> [...]
>
>> but the same SELECT count, it's immediate:
>>
>> SELECT count(id) FROM comptes WHERE codi_compte LIKE '10000%' AND
>> empresa_id=2 AND nivell=11;
>
> Maybe there is any check or constraint on belongs_to_compte_id.comptes that
> might take longer?
no, there's no check or constraint (no foreign key, ...) on this field.
I'm using now another database with same structure and data and the delay doesn't exist there, there must be something wrong in my current development database.
I'm checking this now ...
thanks,
r.
>
> Cheers,
>
> -Mike
>
> --
> Michał Roszka
> mike@if-then-else.pl
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SELECT is immediate but the UPDATE takes forever
@ 2010-12-07 15:37 Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2010-12-07 15:37 UTC (permalink / raw)
To: Michał Roszka <mike@if-then-else.pl>; +Cc: Raimon Fernandez <coder@montx.com>; pgsql-general List <pgsql-general@postgresql.org>
=?utf-8?b?TWljaGHFgg==?= Roszka <mike@if-then-else.pl> writes:
> Quoting Raimon Fernandez <coder@montx.com>:
>> I want to understand why one of my postgresql functions takes an
>> eternity to finish.
> Maybe there is any check or constraint on belongs_to_compte_id.comptes that
> might take longer?
Or maybe the UPDATE is blocked on a lock ... did you look into
pg_stat_activity or pg_locks to check?
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SELECT is immediate but the UPDATE takes forever
@ 2010-12-07 18:20 Alban Hertroys <dalroi@solfertje.student.utwente.nl>
parent: Michał Roszka <mike@if-then-else.pl>
1 sibling, 0 replies; 10+ messages in thread
From: Alban Hertroys @ 2010-12-07 18:20 UTC (permalink / raw)
To: Michał Roszka <mike@if-then-else.pl>; +Cc: Raimon Fernandez <coder@montx.com>; pgsql-general List <pgsql-general@postgresql.org>
On 7 Dec 2010, at 15:45, Michał Roszka wrote:
>> but the same SELECT count, it's immediate:
>>
>> SELECT count(id) FROM comptes WHERE codi_compte LIKE '10000%' AND
>> empresa_id=2 AND nivell=11;
>
> Maybe there is any check or constraint on belongs_to_compte_id.comptes that
> might take longer?
Or a foreign key constraint or an update trigger, to name a few other possibilities.
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
!DSPAM:737,4cfe7af5802659106873227!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SELECT is immediate but the UPDATE takes forever
@ 2010-12-08 17:18 Vick Khera <vivek@khera.org>
parent: Raimon Fernandez <coder@montx.com>
0 siblings, 1 reply; 10+ messages in thread
From: Vick Khera @ 2010-12-08 17:18 UTC (permalink / raw)
To: pgsql-general <pgsql-general@postgresql.org>
2010/12/7 Raimon Fernandez <coder@montx.com>:
> I'm using now another database with same structure and data and the delay doesn't exist there, there must be something wrong in my current development database.
>
does autovacuum run on it? is the table massively bloated? is your
disk system really, really slow to allocate new space?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SELECT is immediate but the UPDATE takes forever
@ 2010-12-09 03:41 Raimon Fernandez <coder@montx.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Raimon Fernandez @ 2010-12-09 03:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Michał Roszka <mike@if-then-else.pl>; pgsql-general List <pgsql-general@postgresql.org>
On 7dic, 2010, at 16:37 , Tom Lane wrote:
>> Quoting Raimon Fernandez <coder@montx.com>:
>>> I want to understand why one of my postgresql functions takes an
>>> eternity to finish.
>
>> Maybe there is any check or constraint on belongs_to_compte_id.comptes that
>> might take longer?
>
> Or maybe the UPDATE is blocked on a lock ... did you look into
> pg_stat_activity or pg_locks to check?
no, there's no lock, blocked, ... I'm the only user connected with my developer test database and I'm sure there are no locks, and more sure after looking at pg_locks :-)
thanks,
r.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SELECT is immediate but the UPDATE takes forever
@ 2010-12-09 03:58 Raimon Fernandez <coder@montx.com>
parent: Vick Khera <vivek@khera.org>
0 siblings, 1 reply; 10+ messages in thread
From: Raimon Fernandez @ 2010-12-09 03:58 UTC (permalink / raw)
To: Vick Khera <vivek@khera.org>; +Cc: pgsql-general <pgsql-general@postgresql.org>
On 8dic, 2010, at 18:18 , Vick Khera wrote:
> 2010/12/7 Raimon Fernandez <coder@montx.com>:
>> I'm using now another database with same structure and data and the delay doesn't exist there, there must be something wrong in my current development database.
>>
>
> does autovacuum run on it?
no
> is the table massively bloated?
no
> is your disk system really, really slow to allocate new space?
no
now:
well, after a VACUUM things are going faster ... I'm still trying to analyze the function as it seems there are other bottlechecnk, but at least the first update now is faster as before ...
thanks,
r.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SELECT is immediate but the UPDATE takes forever
@ 2010-12-09 13:32 Vick Khera <vivek@khera.org>
parent: Raimon Fernandez <coder@montx.com>
0 siblings, 1 reply; 10+ messages in thread
From: Vick Khera @ 2010-12-09 13:32 UTC (permalink / raw)
To: pgsql-general <pgsql-general@postgresql.org>
On Wed, Dec 8, 2010 at 10:58 PM, Raimon Fernandez <coder@montx.com> wrote:
> well, after a VACUUM things are going faster ... I'm still trying to analyze the function as it seems there are other bottlechecnk, but at least the first update now is faster as before ...
>
If that's the case then your 'no' answer to "is the table bloated" was
probably incorrect, and your answer to "is your I/O slow to grow a
file" is also probably incorrect.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SELECT is immediate but the UPDATE takes forever
@ 2010-12-09 16:08 Raimon Fernandez <coder@montx.com>
parent: Vick Khera <vivek@khera.org>
0 siblings, 0 replies; 10+ messages in thread
From: Raimon Fernandez @ 2010-12-09 16:08 UTC (permalink / raw)
To: Vick Khera <vivek@khera.org>; +Cc: pgsql-general <pgsql-general@postgresql.org>
On 9dic, 2010, at 14:32 , Vick Khera wrote:
>> well, after a VACUUM things are going faster ... I'm still trying to analyze the function as it seems there are other bottlechecnk, but at least the first update now is faster as before ...
>>
>
> If that's the case then your 'no' answer to "is the table bloated" was probably incorrect,
here you maybe are right
> and your answer to "is your I/O slow to grow a file" is also probably incorrect.
not sure as I'm not experiencing any slownes on the same machine with other postgresql databases that are also more or less the same size, I'm still a real newbie ...
thanks!
regards,
raimon
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2010-12-09 16:08 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-07 11:11 ` Raimon Fernandez <coder@montx.com>
2010-12-07 14:45 ` Michał Roszka <mike@if-then-else.pl>
2010-12-07 15:14 ` Raimon Fernandez <coder@montx.com>
2010-12-08 17:18 ` Vick Khera <vivek@khera.org>
2010-12-09 03:58 ` Raimon Fernandez <coder@montx.com>
2010-12-09 13:32 ` Vick Khera <vivek@khera.org>
2010-12-09 16:08 ` Raimon Fernandez <coder@montx.com>
2010-12-07 18:20 ` Alban Hertroys <dalroi@solfertje.student.utwente.nl>
2010-12-07 15:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-12-09 03:41 ` Raimon Fernandez <coder@montx.com>
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