pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAnother weird one with an UPDATE
12+ messages / 4 participants
[nested] [flat]
* Another weird one with an UPDATE
@ 2003-10-11 19:44 David Griffiths <dgriffiths@boats.com>
2003-10-11 21:35 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
0 siblings, 1 reply; 12+ messages in thread
From: David Griffiths @ 2003-10-11 19:44 UTC (permalink / raw)
To: pgsql-performance
I am running an update-query to benchmark various databases; the
postgres version is,
UPDATE user_account SET last_name = 'abc'
WHERE user_account_id IN (SELECT user_account_id FROM commercial_entity,
commercial_service WHERE yw_account_id IS NULL
AND commercial_entity.commercial_entity_id =
commercial_service.commercial_entity_id);
The inner query (the select), run by itself, takes about a second. Add
the outer query (the update-portion), and the query dies. The machine
has been vacuum-analzyed. Here is the explain-analyze:
benchtest=# EXPLAIN ANALYZE UPDATE user_account SET last_name = 'abc'
benchtest-# WHERE user_account_id IN (SELECT user_account_id FROM
commercial_entity, commercial_service WHERE yw_account_id IS NULL
benchtest(# AND commercial_entity.commercial_entity_id =
commercial_service.commercial_entity_id);
Seq Scan on user_account (cost=0.00..813608944.88 rows=36242
width=718) (actual time=15696258.98..16311130.29 rows=3075 loops=1)
Filter: (subplan)
SubPlan
-> Materialize (cost=11224.77..11224.77 rows=86952 width=36)
(actual time=0.06..106.40 rows=84831 loops=72483)
-> Merge Join (cost=0.00..11224.77 rows=86952 width=36)
(actual time=0.21..1845.13 rows=85158 loops=1)
Merge Cond: ("outer".commercial_entity_id =
"inner".commercial_entity_id)
-> Index Scan using commercial_entity_pkey on
commercial_entity (cost=0.00..6787.27 rows=77862 width=24) (actual
time=0.06..469.56 rows=78132 loops=1)
Filter: (yw_account_id IS NULL)
-> Index Scan using comm_serv_comm_ent_id_i on
commercial_service (cost=0.00..2952.42 rows=88038 width=12) (actual
time=0.03..444.80 rows=88038 loops=1)
Total runtime: 16332976.21 msec
(10 rows)
Here are the relevant parts of the schema:
USER_ACCOUNT
Column | Type |
Modifiers
-------------------------------+-----------------------------+----------
-------------------
user_account_id | numeric(10,0) | not null
first_name | character varying(100) |
last_name | character varying(100) |
Indexes: user_account_pkey primary key btree (user_account_id),
usr_acc_last_name_i btree (last_name),
Foreign Key constraints: $1 FOREIGN KEY (lang_id) REFERENCES
lang(lang_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
$2 FOREIGN KEY (source_id) REFERENCES
source(source_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
$3 FOREIGN KEY (user_role_id) REFERENCES
user_role(user_role_id) ON UPDATE NO ACTION ON DELETE NO ACTION
COMMERCIAL_ENTITY
Column | Type |
Modifiers
---------------------------+-----------------------------+--------------
-----------------------------------------------
commercial_entity_id | numeric(10,0) | not null
yw_account_id | numeric(10,0) |
Indexes: commercial_entity_pkey primary key btree
(commercial_entity_id),
comm_ent_yw_acc_id_i btree (yw_account_id)
Foreign Key constraints: $1 FOREIGN KEY (source_id) REFERENCES
source(source_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
$2 FOREIGN KEY (user_account_id) REFERENCES
user_account(user_account_id) ON UPDATE NO ACTION ON DELETE NO ACTION
COMMERCIAL_SERVICE
Column | Type | Modifiers
----------------------+---------------+-----------
commercial_entity_id | numeric(10,0) | not null
service_type_id | numeric(10,0) | not null
source_id | numeric(10,0) | not null
Indexes: commercial_service_pkey primary key btree
(commercial_entity_id, service_type_id),
comm_serv_comm_ent_id_i btree (commercial_entity_id),
comm_serv_serv_type_id_i btree (service_type_id),
comm_serv_source_id_i btree (source_id)
Foreign Key constraints: $1 FOREIGN KEY (commercial_entity_id)
REFERENCES commercial_entity(commercial_entity_id) ON UPDATE NO ACTION
ON DELETE NO ACTION,
$2 FOREIGN KEY (source_id) REFERENCES
source(source_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
$3 FOREIGN KEY (service_type_id) REFERENCES
service_type(service_type_id) ON UPDATE NO ACTION ON DELETE NO ACTION
Here is the postgres.conf (or the variables that are not commented out):
tcpip_socket = true
max_connections = 500
shared_buffers = 32768 # min max_connections*2 or 16, 8KB each
wal_buffers = 128 # min 4, typically 8KB each
sort_mem = 4096 # min 64, size in KB
effective_cache_size = 50000 # typically 8KB each
Is it a problem with "IN"?
David
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Another weird one with an UPDATE
2003-10-11 19:44 Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
@ 2003-10-11 21:35 ` David Griffiths <dgriffiths@boats.com>
2003-10-11 22:34 ` Re: Another weird one with an UPDATE Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 12+ messages in thread
From: David Griffiths @ 2003-10-11 21:35 UTC (permalink / raw)
To: pgsql-performance
Sorry - just found the FAQ (
http://jamesthornton.com/postgres/FAQ/faq-english.html#4.22
<http://jamesthornton.com/postgres/FAQ/faq-english.html#4.22; ) on how
IN is very slow.
So I rewrote the query:
\o ./data/temp.txt
SELECT current_timestamp;
UPDATE user_account SET last_name = 'abc'
WHERE EXISTS (SELECT ua.user_account_id FROM user_account ua,
commercial_entity ce, commercial_service cs
WHERE ua.user_account_id = ce.user_account_id AND
ce.commercial_entity_id = cs.commercial_entity_id);
SELECT current_timestamp;
\o
EXISTS is kind of a weird statement, and it doesn't appear to be
identical (the number of rows updated was 72,000 rather than 3500). It
also took 4 minutes to execute.
Is there any way around this other than breaking the query into two? As
in:
pstmt1 = conn.preprareStatement("SELECT ua.user_account_id FROM
user_account ua, commercial_entity ce, commercial_service cs
WHERE ua.user_account_id = ce.user_account_id AND
ce.commercial_entity_id = cs.commercial_entity_id");
rset = pstmt1.executeQuery();
while (rset.next())
{
pstmt2 = conn.prepareStatement("UPDATE user_account SET last_name =
'abc' WHERE user_account_id = ?");
pstmt2.setLong(1, rset.getLong(1));
...
}
Unfort, that will be alot of data moved from Postgres->middle-tier
(Weblogic/Resin), which is inefficient.
Anyone see another solution?
David.
----- Original Message -----
From: David <mailto:dgriffiths@boats.com> Griffiths
To: pgsql-performance@postgresql.org
<mailto:pgsql-performance@postgresql.org>
Sent: Saturday, October 11, 2003 12:44 PM
Subject: [PERFORM] Another weird one with an UPDATE
I am running an update-query to benchmark various databases; the
postgres version is,
UPDATE user_account SET last_name = 'abc'
WHERE user_account_id IN (SELECT user_account_id FROM commercial_entity,
commercial_service WHERE yw_account_id IS NULL
AND commercial_entity.commercial_entity_id =
commercial_service.commercial_entity_id);
The inner query (the select), run by itself, takes about a second. Add
the outer query (the update-portion), and the query dies. The machine
has been vacuum-analzyed. Here is the explain-analyze:
benchtest=# EXPLAIN ANALYZE UPDATE user_account SET last_name = 'abc'
benchtest-# WHERE user_account_id IN (SELECT user_account_id FROM
commercial_entity, commercial_service WHERE yw_account_id IS NULL
benchtest(# AND commercial_entity.commercial_entity_id =
commercial_service.commercial_entity_id);
Seq Scan on user_account (cost=0.00..813608944.88 rows=36242
width=718) (actual time=15696258.98..16311130.29 rows=3075 loops=1)
Filter: (subplan)
SubPlan
-> Materialize (cost=11224.77..11224.77 rows=86952 width=36)
(actual time=0.06..106.40 rows=84831 loops=72483)
-> Merge Join (cost=0.00..11224.77 rows=86952 width=36)
(actual time=0.21..1845.13 rows=85158 loops=1)
Merge Cond: ("outer".commercial_entity_id =
"inner".commercial_entity_id)
-> Index Scan using commercial_entity_pkey on
commercial_entity (cost=0.00..6787.27 rows=77862 width=24) (actual
time=0.06..469.56 rows=78132 loops=1)
Filter: (yw_account_id IS NULL)
-> Index Scan using comm_serv_comm_ent_id_i on
commercial_service (cost=0.00..2952.42 rows=88038 width=12) (actual
time=0.03..444.80 rows=88038 loops=1)
Total runtime: 16332976.21 msec
(10 rows)
Here are the relevant parts of the schema:
USER_ACCOUNT
Column | Type |
Modifiers
-------------------------------+-----------------------------+----------
-------------------
user_account_id | numeric(10,0) | not null
first_name | character varying(100) |
last_name | character varying(100) |
Indexes: user_account_pkey primary key btree (user_account_id),
usr_acc_last_name_i btree (last_name),
Foreign Key constraints: $1 FOREIGN KEY (lang_id) REFERENCES
lang(lang_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
$2 FOREIGN KEY (source_id) REFERENCES
source(source_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
$3 FOREIGN KEY (user_role_id) REFERENCES
user_role(user_role_id) ON UPDATE NO ACTION ON DELETE NO ACTION
COMMERCIAL_ENTITY
Column | Type |
Modifiers
---------------------------+-----------------------------+--------------
-----------------------------------------------
commercial_entity_id | numeric(10,0) | not null
yw_account_id | numeric(10,0) |
Indexes: commercial_entity_pkey primary key btree
(commercial_entity_id),
comm_ent_yw_acc_id_i btree (yw_account_id)
Foreign Key constraints: $1 FOREIGN KEY (source_id) REFERENCES
source(source_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
$2 FOREIGN KEY (user_account_id) REFERENCES
user_account(user_account_id) ON UPDATE NO ACTION ON DELETE NO ACTION
COMMERCIAL_SERVICE
Column | Type | Modifiers
----------------------+---------------+-----------
commercial_entity_id | numeric(10,0) | not null
service_type_id | numeric(10,0) | not null
source_id | numeric(10,0) | not null
Indexes: commercial_service_pkey primary key btree
(commercial_entity_id, service_type_id),
comm_serv_comm_ent_id_i btree (commercial_entity_id),
comm_serv_serv_type_id_i btree (service_type_id),
comm_serv_source_id_i btree (source_id)
Foreign Key constraints: $1 FOREIGN KEY (commercial_entity_id)
REFERENCES commercial_entity(commercial_entity_id) ON UPDATE NO ACTION
ON DELETE NO ACTION,
$2 FOREIGN KEY (source_id) REFERENCES
source(source_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
$3 FOREIGN KEY (service_type_id) REFERENCES
service_type(service_type_id) ON UPDATE NO ACTION ON DELETE NO ACTION
Here is the postgres.conf (or the variables that are not commented out):
tcpip_socket = true
max_connections = 500
shared_buffers = 32768 # min max_connections*2 or 16, 8KB each
wal_buffers = 128 # min 4, typically 8KB each
sort_mem = 4096 # min 64, size in KB
effective_cache_size = 50000 # typically 8KB each
Is it a problem with "IN"?
David
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Another weird one with an UPDATE
2003-10-11 19:44 Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-11 21:35 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
@ 2003-10-11 22:34 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-10-12 05:35 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-13 01:33 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
0 siblings, 2 replies; 12+ messages in thread
From: Stephan Szabo @ 2003-10-11 22:34 UTC (permalink / raw)
To: David Griffiths <dgriffiths@boats.com>; +Cc: pgsql-performance
On Sat, 11 Oct 2003, David Griffiths wrote:
> Sorry - just found the FAQ (
> http://jamesthornton.com/postgres/FAQ/faq-english.html#4.22
> <http://jamesthornton.com/postgres/FAQ/faq-english.html#4.22; ) on how
> IN is very slow.
>
> So I rewrote the query:
>
> \o ./data/temp.txt
> SELECT current_timestamp;
> UPDATE user_account SET last_name = 'abc'
> WHERE EXISTS (SELECT ua.user_account_id FROM user_account ua,
> commercial_entity ce, commercial_service cs
> WHERE ua.user_account_id = ce.user_account_id AND
> ce.commercial_entity_id = cs.commercial_entity_id);
> SELECT current_timestamp;
I don't think that's the query you want. You're not binding the subselect
to the outer values of user_account.
I think you want something like:
UPDATE user_account SET last_name = 'abc'
WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service cs
WHERE user_account.user_account_id = ce.user_account_id AND
ce.commercial_entity_id = cs.commercial_entity_id);
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Another weird one with an UPDATE
2003-10-11 19:44 Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-11 21:35 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-11 22:34 ` Re: Another weird one with an UPDATE Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2003-10-12 05:35 ` David Griffiths <dgriffiths@boats.com>
1 sibling, 0 replies; 12+ messages in thread
From: David Griffiths @ 2003-10-12 05:35 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-performance
Thanks - that worked.
David
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: "David Griffiths" <dgriffiths@boats.com>
Cc: <pgsql-performance@postgresql.org>
Sent: Saturday, October 11, 2003 3:34 PM
Subject: Re: [PERFORM] Another weird one with an UPDATE
> On Sat, 11 Oct 2003, David Griffiths wrote:
>
> > Sorry - just found the FAQ (
> > http://jamesthornton.com/postgres/FAQ/faq-english.html#4.22
> > <http://jamesthornton.com/postgres/FAQ/faq-english.html#4.22; ) on how
> > IN is very slow.
> >
> > So I rewrote the query:
> >
> > \o ./data/temp.txt
> > SELECT current_timestamp;
> > UPDATE user_account SET last_name = 'abc'
> > WHERE EXISTS (SELECT ua.user_account_id FROM user_account ua,
> > commercial_entity ce, commercial_service cs
> > WHERE ua.user_account_id = ce.user_account_id AND
> > ce.commercial_entity_id = cs.commercial_entity_id);
> > SELECT current_timestamp;
>
> I don't think that's the query you want. You're not binding the subselect
> to the outer values of user_account.
>
> I think you want something like:
> UPDATE user_account SET last_name = 'abc'
> WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service cs
> WHERE user_account.user_account_id = ce.user_account_id AND
> ce.commercial_entity_id = cs.commercial_entity_id);
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Another weird one with an UPDATE
2003-10-11 19:44 Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-11 21:35 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-11 22:34 ` Re: Another weird one with an UPDATE Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2003-10-13 01:33 ` David Griffiths <dgriffiths@boats.com>
2003-10-13 01:37 ` Re: Another weird one with an UPDATE Joe Conway <mail@joeconway.com>
2003-10-13 01:48 ` Re: Another weird one with an UPDATE Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 2 replies; 12+ messages in thread
From: David Griffiths @ 2003-10-13 01:33 UTC (permalink / raw)
To: pgsql-performance
[snip]
> I think you want something like:
> UPDATE user_account SET last_name = 'abc'
> WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service cs
> WHERE user_account.user_account_id = ce.user_account_id AND
> ce.commercial_entity_id = cs.commercial_entity_id);
Unfort, this is still taking a long time.
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------
Seq Scan on user_account (cost=0.00..748990.51 rows=36242 width=716)
(actual time=10262.50..26568.03 rows=3771 loops=1)
Filter: (subplan)
SubPlan
-> Nested Loop (cost=0.00..11.47 rows=1 width=24) (actual
time=0.24..0.24 rows=0 loops=72483)
-> Index Scan using comm_ent_usr_acc_id_i on commercial_entity
ce (cost=0.00..4.12 rows=1 width=12) (actual time=0.05..0.05 rows=0
loops=72483)
Index Cond: ($0 = user_account_id)
-> Index Scan using comm_serv_comm_ent_id_i on
commercial_service cs (cost=0.00..7.32 rows=3 width=12) (actual
time=1.72..1.72 rows=0 loops=7990)
Index Cond: ("outer".commercial_entity_id =
cs.commercial_entity_id)
Total runtime: 239585.09 msec
(9 rows)
Anyone have any thoughts?
David
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Another weird one with an UPDATE
2003-10-11 19:44 Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-11 21:35 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-11 22:34 ` Re: Another weird one with an UPDATE Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-10-13 01:33 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
@ 2003-10-13 01:37 ` Joe Conway <mail@joeconway.com>
2003-10-13 06:21 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
1 sibling, 1 reply; 12+ messages in thread
From: Joe Conway @ 2003-10-13 01:37 UTC (permalink / raw)
To: David Griffiths <dgriffiths@boats.com>; +Cc: pgsql-performance
David Griffiths wrote:
>>I think you want something like:
>>UPDATE user_account SET last_name = 'abc'
>> WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service cs
>> WHERE user_account.user_account_id = ce.user_account_id AND
>> ce.commercial_entity_id = cs.commercial_entity_id);
>
> Unfort, this is still taking a long time.
> -------
> Seq Scan on user_account (cost=0.00..748990.51 rows=36242 width=716)
Do you have an index on user_account.user_account_id?
Joe
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Another weird one with an UPDATE
2003-10-11 19:44 Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-11 21:35 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-11 22:34 ` Re: Another weird one with an UPDATE Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-10-13 01:33 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-13 01:37 ` Re: Another weird one with an UPDATE Joe Conway <mail@joeconway.com>
@ 2003-10-13 06:21 ` David Griffiths <dgriffiths@boats.com>
2003-10-13 15:04 ` Re: Another weird one with an UPDATE Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 12+ messages in thread
From: David Griffiths @ 2003-10-13 06:21 UTC (permalink / raw)
To: pgsql-performance
Yes, the query operates only on indexed columns (all numeric(10)'s).
Column | Type |
Modifiers
-------------------------------+-----------------------------+--------------
---------------
user_account_id | numeric(10,0) | not null
[snip]
Indexes: user_account_pkey primary key btree (user_account_id),
Foreign Key constraints: $1 FOREIGN KEY (lang_id) REFERENCES lang(lang_id)
ON UPDATE NO ACTION ON DELETE NO ACTION,
$2 FOREIGN KEY (source_id) REFERENCES
source(source_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
$3 FOREIGN KEY (user_role_id) REFERENCES
user_role(user_role_id) ON UPDATE NO ACTION ON DELETE NO ACTION
David
----- Original Message -----
From: "Joe Conway" <mail@joeconway.com>
To: "David Griffiths" <dgriffiths@boats.com>
Cc: <pgsql-performance@postgresql.org>
Sent: Sunday, October 12, 2003 6:37 PM
Subject: Re: [PERFORM] Another weird one with an UPDATE
> David Griffiths wrote:
> >>I think you want something like:
> >>UPDATE user_account SET last_name = 'abc'
> >> WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service cs
> >> WHERE user_account.user_account_id = ce.user_account_id AND
> >> ce.commercial_entity_id = cs.commercial_entity_id);
> >
> > Unfort, this is still taking a long time.
> > -------
> > Seq Scan on user_account (cost=0.00..748990.51 rows=36242 width=716)
>
> Do you have an index on user_account.user_account_id?
>
> Joe
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Another weird one with an UPDATE
2003-10-11 19:44 Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-11 21:35 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-11 22:34 ` Re: Another weird one with an UPDATE Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-10-13 01:33 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-13 01:37 ` Re: Another weird one with an UPDATE Joe Conway <mail@joeconway.com>
2003-10-13 06:21 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
@ 2003-10-13 15:04 ` Joe Conway <mail@joeconway.com>
2003-10-13 19:04 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
0 siblings, 1 reply; 12+ messages in thread
From: Joe Conway @ 2003-10-13 15:04 UTC (permalink / raw)
To: David Griffiths <dgriffiths@boats.com>; +Cc: pgsql-performance
David Griffiths wrote:
> Yes, the query operates only on indexed columns (all numeric(10)'s).
>
> Column | Type |
> Modifiers
> -------------------------------+-----------------------------+--------------
> ---------------
> user_account_id | numeric(10,0) | not null
> [snip]
> Indexes: user_account_pkey primary key btree (user_account_id),
> Foreign Key constraints: $1 FOREIGN KEY (lang_id) REFERENCES lang(lang_id)
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> $2 FOREIGN KEY (source_id) REFERENCES
> source(source_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
> $3 FOREIGN KEY (user_role_id) REFERENCES
> user_role(user_role_id) ON UPDATE NO ACTION ON DELETE NO ACTION
And what about commercial_entity.user_account_id. Is it indexed and what
is its data type (i.e. does it match numeric(10,0))?
Also, have you run VACUUM ANALYZE lately?
Joe
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Another weird one with an UPDATE
2003-10-11 19:44 Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-11 21:35 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-11 22:34 ` Re: Another weird one with an UPDATE Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-10-13 01:33 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-13 01:37 ` Re: Another weird one with an UPDATE Joe Conway <mail@joeconway.com>
2003-10-13 06:21 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-13 15:04 ` Re: Another weird one with an UPDATE Joe Conway <mail@joeconway.com>
@ 2003-10-13 19:04 ` David Griffiths <dgriffiths@boats.com>
0 siblings, 0 replies; 12+ messages in thread
From: David Griffiths @ 2003-10-13 19:04 UTC (permalink / raw)
To: pgsql-performance
> And what about commercial_entity.user_account_id. Is it indexed and what
> is its data type (i.e. does it match numeric(10,0))?
Yup - all columns in the statement are indexed, and they are all
numeric(10,0).
> Also, have you run VACUUM ANALYZE lately?
Yup - just before the last run.
Will get together my tuning data now.
David
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Another weird one with an UPDATE
2003-10-11 19:44 Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-11 21:35 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-11 22:34 ` Re: Another weird one with an UPDATE Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-10-13 01:33 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
@ 2003-10-13 01:48 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-10-13 06:23 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
1 sibling, 1 reply; 12+ messages in thread
From: Stephan Szabo @ 2003-10-13 01:48 UTC (permalink / raw)
To: David Griffiths <dgriffiths@boats.com>; +Cc: pgsql-performance
On Sun, 12 Oct 2003, David Griffiths wrote:
> [snip]
>
> > I think you want something like:
> > UPDATE user_account SET last_name = 'abc'
> > WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service cs
> > WHERE user_account.user_account_id = ce.user_account_id AND
> > ce.commercial_entity_id = cs.commercial_entity_id);
>
> Unfort, this is still taking a long time.
Hmm, does
UPDATE user_account SET last_name='abc'
FROM commercial_entity ce, commercial_service cs
WHERE user_account.user_account_id = ce.user_account_id AND
ce.commercial_entity_id=cs.commercial_entity_id;
give the right results... That might end up being faster.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Another weird one with an UPDATE
2003-10-11 19:44 Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-11 21:35 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-11 22:34 ` Re: Another weird one with an UPDATE Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-10-13 01:33 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-13 01:48 ` Re: Another weird one with an UPDATE Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2003-10-13 06:23 ` David Griffiths <dgriffiths@boats.com>
2003-10-13 10:27 ` Re: Another weird one with an UPDATE Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
0 siblings, 1 reply; 12+ messages in thread
From: David Griffiths @ 2003-10-13 06:23 UTC (permalink / raw)
To: pgsql-performance
It's a slight improvement, but that could be other things as well.
I'd read that how you tune Postgres will determine how the optimizer works
on a query (sequential scan vs index scan). I am going to post all I've done
with tuning tommorow, and see if I've done anything dumb. I've found some
contradictory advice, and I'm still a bit hazy on how/why Postgres trusts
the OS to do caching. I'll post it all tommorow.
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------
Merge Join (cost=11819.21..15258.55 rows=12007 width=752) (actual
time=4107.64..5587.81 rows=20880 loops=1)
Merge Cond: ("outer".commercial_entity_id = "inner".commercial_entity_id)
-> Index Scan using comm_serv_comm_ent_id_i on commercial_service cs
(cost=0.00..3015.53 rows=88038 width=12) (actual time=0.05..487.23
rows=88038 loops=1)
-> Sort (cost=11819.21..11846.08 rows=10752 width=740) (actual
time=3509.07..3955.15 rows=25098 loops=1)
Sort Key: ce.commercial_entity_id
-> Merge Join (cost=0.00..9065.23 rows=10752 width=740) (actual
time=0.18..2762.13 rows=7990 loops=1)
Merge Cond: ("outer".user_account_id =
"inner".user_account_id)
-> Index Scan using user_account_pkey on user_account
(cost=0.00..8010.39 rows=72483 width=716) (actual time=0.05..2220.86
rows=72483 loops=1)
-> Index Scan using comm_ent_usr_acc_id_i on
commercial_entity ce (cost=0.00..4787.69 rows=78834 width=24) (actual
time=0.02..55.64 rows=7991 loops=1)
Total runtime: 226239.77 msec
(10 rows)
David
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: "David Griffiths" <dgriffiths@boats.com>
Cc: <pgsql-performance@postgresql.org>
Sent: Sunday, October 12, 2003 6:48 PM
Subject: Re: [PERFORM] Another weird one with an UPDATE
> On Sun, 12 Oct 2003, David Griffiths wrote:
>
> > [snip]
> >
> > > I think you want something like:
> > > UPDATE user_account SET last_name = 'abc'
> > > WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service
cs
> > > WHERE user_account.user_account_id = ce.user_account_id AND
> > > ce.commercial_entity_id = cs.commercial_entity_id);
> >
> > Unfort, this is still taking a long time.
>
> Hmm, does
> UPDATE user_account SET last_name='abc'
> FROM commercial_entity ce, commercial_service cs
> WHERE user_account.user_account_id = ce.user_account_id AND
> ce.commercial_entity_id=cs.commercial_entity_id;
> give the right results... That might end up being faster.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Another weird one with an UPDATE
2003-10-11 19:44 Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-11 21:35 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-11 22:34 ` Re: Another weird one with an UPDATE Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-10-13 01:33 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-13 01:48 ` Re: Another weird one with an UPDATE Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-10-13 06:23 ` Re: Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
@ 2003-10-13 10:27 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
0 siblings, 0 replies; 12+ messages in thread
From: Shridhar Daithankar @ 2003-10-13 10:27 UTC (permalink / raw)
To: David Griffiths <dgriffiths@boats.com>; +Cc: pgsql-performance
David Griffiths wrote:
> It's a slight improvement, but that could be other things as well.
>
> I'd read that how you tune Postgres will determine how the optimizer works
> on a query (sequential scan vs index scan). I am going to post all I've done
> with tuning tommorow, and see if I've done anything dumb. I've found some
> contradictory advice, and I'm still a bit hazy on how/why Postgres trusts
> the OS to do caching. I'll post it all tommorow.
>
> ----------------------------------------------------------------------------
> ----------------------------------------------------------------------------
> ----------------
> Merge Join (cost=11819.21..15258.55 rows=12007 width=752) (actual
> time=4107.64..5587.81 rows=20880 loops=1)
> Merge Cond: ("outer".commercial_entity_id = "inner".commercial_entity_id)
> -> Index Scan using comm_serv_comm_ent_id_i on commercial_service cs
> (cost=0.00..3015.53 rows=88038 width=12) (actual time=0.05..487.23
> rows=88038 loops=1)
> -> Sort (cost=11819.21..11846.08 rows=10752 width=740) (actual
> time=3509.07..3955.15 rows=25098 loops=1)
> Sort Key: ce.commercial_entity_id
I think this is the problem. Is there an index on ce.commercial_entity_id?
> -> Merge Join (cost=0.00..9065.23 rows=10752 width=740) (actual
> time=0.18..2762.13 rows=7990 loops=1)
> Merge Cond: ("outer".user_account_id =
> "inner".user_account_id)
> -> Index Scan using user_account_pkey on user_account
> (cost=0.00..8010.39 rows=72483 width=716) (actual time=0.05..2220.86
> rows=72483 loops=1)
> -> Index Scan using comm_ent_usr_acc_id_i on
> commercial_entity ce (cost=0.00..4787.69 rows=78834 width=24) (actual
> time=0.02..55.64 rows=7991 loops=1)
In this case of comparing account ids, its using two index scans. In the entity
field though, it chooses a sort. I think there is an index missing. The costs
are also shot up as well.
> Total runtime: 226239.77 msec
Standard performance question. What was the last time these tables/database were
vacuumed. Have you tuned postgresql.conf correctly?
HTH
Shridhar
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2003-10-13 19:04 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-11 19:44 Another weird one with an UPDATE David Griffiths <dgriffiths@boats.com>
2003-10-11 21:35 ` David Griffiths <dgriffiths@boats.com>
2003-10-11 22:34 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-10-12 05:35 ` David Griffiths <dgriffiths@boats.com>
2003-10-13 01:33 ` David Griffiths <dgriffiths@boats.com>
2003-10-13 01:37 ` Joe Conway <mail@joeconway.com>
2003-10-13 06:21 ` David Griffiths <dgriffiths@boats.com>
2003-10-13 15:04 ` Joe Conway <mail@joeconway.com>
2003-10-13 19:04 ` David Griffiths <dgriffiths@boats.com>
2003-10-13 01:48 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-10-13 06:23 ` David Griffiths <dgriffiths@boats.com>
2003-10-13 10:27 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
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