pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedVery slow updates when using IN syntax subselect
9+ messages / 3 participants
[nested] [flat]
* Very slow updates when using IN syntax subselect
@ 2006-02-10 21:00 Bryce Nesbitt <bryce1@obviously.com>
2006-02-10 21:32 ` Re: Very slow updates when using IN syntax subselect Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Bryce Nesbitt @ 2006-02-10 21:00 UTC (permalink / raw)
To: pgsql-sql
If I do:
select event_id from event join token using
(token_number) where token_status=50 and
reconciled=false limit 1;
Then:
update event set reconciled=true where event_id={XXX};
It returns in about a second, or less. But If I do the same thing with
the IN syntax:
update event set reconciled=true where event_id in
(select event_id from event join token using (token_number)
where token_status=50 and reconciled=false LIMIT 1);
On a 4 CPU machine, 2 CPU's peg at 100%, and the request just eats CPU
forever. Any clues what might be going on? Help would be much
appreciated. I'm not seeing this on all my DB's... just the important ones.
PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-20)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow updates when using IN syntax subselect
2006-02-10 21:00 Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
@ 2006-02-10 21:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-11 05:12 ` Re: Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2006-02-10 21:32 UTC (permalink / raw)
To: Bryce Nesbitt <bryce1@obviously.com>; +Cc: pgsql-sql
Bryce Nesbitt <bryce1@obviously.com> writes:
> update event set reconciled=true where event_id in
> (select event_id from event join token using (token_number)
> where token_status=50 and reconciled=false LIMIT 1);
> On a 4 CPU machine, 2 CPU's peg at 100%, and the request just eats CPU
> forever.
What does EXPLAIN show for this and for the base query?
> PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
This may be your problem right here. You are urgently in need of an
update in any case --- there are a lot of nasty bugs fixed since 7.4.1:
http://developer.postgresql.org/docs/postgres/release.html
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow updates when using IN syntax subselect
2006-02-10 21:00 Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
2006-02-10 21:32 ` Re: Very slow updates when using IN syntax subselect Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-02-11 05:12 ` Bryce Nesbitt <bryce1@obviously.com>
2006-02-11 15:47 ` Re: Very slow updates when using IN syntax subselect Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Bryce Nesbitt @ 2006-02-11 05:12 UTC (permalink / raw)
To: pgsql-sql; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
Tom Lane wrote:
> Bryce Nesbitt <bryce1@obviously.com> writes:
>
>> update event set reconciled=true where event_id in
>> (select event_id from event join token using (token_number)
>> where token_status=50 and reconciled=false LIMIT 1);
>>
>> On a 4 CPU machine, 2 CPU's peg at 100%, and the request just eats CPU
>> forever.
>>
> What does EXPLAIN show for this and for the base query?
QUERY PLAN
-------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..3.04 rows=1 width=8)
-> Seq Scan on event (cost=0.00..0.00 rows=1 width=408)
Filter: (reconciled = false)
-> Index Scan using token_token_number_key on token
(cost=0.00..3.03 rows=1 width=11)
Index Cond: (("outer".token_number)::text =
(token.token_number)::text)
Filter: (token_status = 50)
(6 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------
Nested Loop IN Join (cost=0.00..3.06 rows=1 width=616)
Join Filter: ("outer".event_id = "inner".event_id)
-> Seq Scan on event (cost=0.00..0.00 rows=1 width=616)
-> Nested Loop (cost=0.00..3.04 rows=1 width=8)
-> Seq Scan on event (cost=0.00..0.00 rows=1 width=408)
Filter: (reconciled = false)
-> Index Scan using token_token_number_key on token
(cost=0.00..3.03 rows=1 width=11)
Index Cond: (("outer".token_number)::text =
(token.token_number)::text)
Filter: (token_status = 50)
(9 rows)
select count(*) from event;
-----------
116226
stage=# select count(*) from token;
-------
8948
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow updates when using IN syntax subselect
2006-02-10 21:00 Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
2006-02-10 21:32 ` Re: Very slow updates when using IN syntax subselect Tom Lane <tgl@sss.pgh.pa.us>
2006-02-11 05:12 ` Re: Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
@ 2006-02-11 15:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-11 19:48 ` Re: Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2006-02-11 15:47 UTC (permalink / raw)
To: Bryce Nesbitt <bryce1@obviously.com>; +Cc: pgsql-sql
Bryce Nesbitt <bryce1@obviously.com> writes:
> Tom Lane wrote:
>> What does EXPLAIN show for this and for the base query?
> -> Seq Scan on event (cost=0.00..0.00 rows=1 width=408)
> Filter: (reconciled = false)
> select count(*) from event;
> -----------
> 116226
It seems pretty clear that you've never vacuumed nor analyzed these
tables ... else the planner would have some clue about their sizes.
Do that and then see what you get.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow updates when using IN syntax subselect
2006-02-10 21:00 Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
2006-02-10 21:32 ` Re: Very slow updates when using IN syntax subselect Tom Lane <tgl@sss.pgh.pa.us>
2006-02-11 05:12 ` Re: Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
2006-02-11 15:47 ` Re: Very slow updates when using IN syntax subselect Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-02-11 19:48 ` Bryce Nesbitt <bryce1@obviously.com>
2006-02-11 20:34 ` Re: Very slow updates when using IN syntax subselect Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 9+ messages in thread
From: Bryce Nesbitt @ 2006-02-11 19:48 UTC (permalink / raw)
To: pgsql-sql
Tom Lane wrote:
> Bryce Nesbitt <bryce1@obviously.com> writes:
>
>> Tom Lane wrote:
>>
>>> What does EXPLAIN show for this and for the base query?
>>>
>
>
>> -> Seq Scan on event (cost=0.00..0.00 rows=1 width=408)
>> Filter: (reconciled = false)
>>
>
>
>> select count(*) from event;
>> -----------
>> 116226
>>
>
> It seems pretty clear that you've never vacuumed nor analyzed these
> tables ... else the planner would have some clue about their sizes.
> Do that and then see what you get.
>
They occur in fine time. That's good, thanks. But jeeze, can't
postgres figure this out for itself?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow updates when using IN syntax subselect
2006-02-10 21:00 Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
2006-02-10 21:32 ` Re: Very slow updates when using IN syntax subselect Tom Lane <tgl@sss.pgh.pa.us>
2006-02-11 05:12 ` Re: Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
2006-02-11 15:47 ` Re: Very slow updates when using IN syntax subselect Tom Lane <tgl@sss.pgh.pa.us>
2006-02-11 19:48 ` Re: Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
@ 2006-02-11 20:34 ` Peter Eisentraut <peter_e@gmx.net>
2006-02-11 22:43 ` Re: Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
2006-02-12 00:37 ` Re: Very slow updates when using IN syntax subselect Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 9+ messages in thread
From: Peter Eisentraut @ 2006-02-11 20:34 UTC (permalink / raw)
To: pgsql-sql; +Cc: Bryce Nesbitt <bryce1@obviously.com>
Bryce Nesbitt wrote:
> > It seems pretty clear that you've never vacuumed nor analyzed these
> > tables ... else the planner would have some clue about their sizes.
> > Do that and then see what you get.
>
> They occur in fine time. That's good, thanks. But jeeze, can't
> postgres figure this out for itself?
I'm sure you wouldn't appreciate it if PostgreSQL did a full table scan
before each query to figure out the total size of the involved tables.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow updates when using IN syntax subselect
2006-02-10 21:00 Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
2006-02-10 21:32 ` Re: Very slow updates when using IN syntax subselect Tom Lane <tgl@sss.pgh.pa.us>
2006-02-11 05:12 ` Re: Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
2006-02-11 15:47 ` Re: Very slow updates when using IN syntax subselect Tom Lane <tgl@sss.pgh.pa.us>
2006-02-11 19:48 ` Re: Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
2006-02-11 20:34 ` Re: Very slow updates when using IN syntax subselect Peter Eisentraut <peter_e@gmx.net>
@ 2006-02-11 22:43 ` Bryce Nesbitt <bryce1@obviously.com>
1 sibling, 0 replies; 9+ messages in thread
From: Bryce Nesbitt @ 2006-02-11 22:43 UTC (permalink / raw)
To: pgsql-sql; +Cc: Peter Eisentraut <peter_e@gmx.net>
Peter Eisentraut wrote:
> Bryce Nesbitt wrote:
>
>>> It seems pretty clear that you've never vacuumed nor analyzed these
>>> tables ... else the planner would have some clue about their sizes.
>>> Do that and then see what you get.
>>>
>> They occur in fine time. That's good, thanks. But jeeze, can't
>> postgres figure this out for itself?
>>
>
> I'm sure you wouldn't appreciate it if PostgreSQL did a full table scan
> before each query to figure out the total size of the involved tables.
>
Nope.
But let's say the query optimizer thought the table had one row.
The the query starts, and 111,000 rows later...
It seems that a mismatch between the static table size, and the actual
one counted as you go, would be a quick
check. That could set a flag for later background processing....
-Bryce
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow updates when using IN syntax subselect
2006-02-10 21:00 Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
2006-02-10 21:32 ` Re: Very slow updates when using IN syntax subselect Tom Lane <tgl@sss.pgh.pa.us>
2006-02-11 05:12 ` Re: Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
2006-02-11 15:47 ` Re: Very slow updates when using IN syntax subselect Tom Lane <tgl@sss.pgh.pa.us>
2006-02-11 19:48 ` Re: Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
2006-02-11 20:34 ` Re: Very slow updates when using IN syntax subselect Peter Eisentraut <peter_e@gmx.net>
@ 2006-02-12 00:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-13 17:27 ` Re: Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2006-02-12 00:37 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-sql@postgresql.org, Bryce Nesbitt <bryce1@obviously.com>
Peter Eisentraut <peter_e@gmx.net> writes:
> Bryce Nesbitt wrote:
>> They occur in fine time. That's good, thanks. But jeeze, can't
>> postgres figure this out for itself?
> I'm sure you wouldn't appreciate it if PostgreSQL did a full table scan
> before each query to figure out the total size of the involved tables.
It's also less than polite to complain about the behavior of
two-year-old releases, without making any effort to ascertain
whether more-current versions are smarter.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow updates when using IN syntax subselect
2006-02-10 21:00 Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
2006-02-10 21:32 ` Re: Very slow updates when using IN syntax subselect Tom Lane <tgl@sss.pgh.pa.us>
2006-02-11 05:12 ` Re: Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
2006-02-11 15:47 ` Re: Very slow updates when using IN syntax subselect Tom Lane <tgl@sss.pgh.pa.us>
2006-02-11 19:48 ` Re: Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
2006-02-11 20:34 ` Re: Very slow updates when using IN syntax subselect Peter Eisentraut <peter_e@gmx.net>
2006-02-12 00:37 ` Re: Very slow updates when using IN syntax subselect Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-02-13 17:27 ` Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 0 replies; 9+ messages in thread
From: Bryce Nesbitt @ 2006-02-13 17:27 UTC (permalink / raw)
To: pgsql-sql
Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
>
>> Bryce Nesbitt wrote:
>>
>>> They occur in finite time. That's good, thanks. But jeeze, can't
>>> postgres figure this out for itself?
>>>
>> I'm sure you wouldn't appreciate it if PostgreSQL did a full table scan
>> before each query to figure out the total size of the involved tables.
>>
>
> It's also less than polite to complain about the behavior of
> two-year-old releases, without making any effort to ascertain
> whether more-current versions are smarter.
>
Sorry to offend. Are current versions smarter?
The DB I was working with still had row counts of 1 after tens of thousands
of records had been added.
With new versions, must the DB still be VACUUMED, or is that a bit
more automatic?
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2006-02-13 17:27 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-10 21:00 Very slow updates when using IN syntax subselect Bryce Nesbitt <bryce1@obviously.com>
2006-02-10 21:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-11 05:12 ` Bryce Nesbitt <bryce1@obviously.com>
2006-02-11 15:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-11 19:48 ` Bryce Nesbitt <bryce1@obviously.com>
2006-02-11 20:34 ` Peter Eisentraut <peter_e@gmx.net>
2006-02-11 22:43 ` Bryce Nesbitt <bryce1@obviously.com>
2006-02-12 00:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-13 17:27 ` Bryce Nesbitt <bryce1@obviously.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