X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 73648D1B4F2 for ; Sat, 11 Oct 2003 21:28:00 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 32405-04 for ; Sat, 11 Oct 2003 18:27:20 -0300 (ADT) Received: from boats_exch01.boats.com (h209-17-182-106.gtconnect.net [209.17.182.106]) by svr1.postgresql.org (Postfix) with ESMTP id 36F16D1B4ED for ; Sat, 11 Oct 2003 18:27:09 -0300 (ADT) Received: by BOATS_EXCH01 with Internet Mail Service (5.5.2653.19) id <4S92JVHC>; Sat, 11 Oct 2003 14:25:42 -0700 Received: from GRIFFITHS2 ([192.168.12.192]) by boats_exch01.boats.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id 4S92JVHA; Sat, 11 Oct 2003 14:25:39 -0700 From: David Griffiths To: pgsql-performance@postgresql.org Message-ID: <05a401c3903f$a8524850$6501a8c0@griffiths2> References: <052001c39030$1ccc8020$6501a8c0@griffiths2> Subject: Re: Another weird one with an UPDATE Date: Sat, 11 Oct 2003 14:35:52 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_05A1_01C39004.F8FFABC0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4922.1500 X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4925.2800 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/395 X-Sequence-Number: 4143 This is a multi-part message in MIME format. ------=_NextPart_000_05A1_01C39004.F8FFABC0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Sorry - just found the FAQ ( http://jamesthornton.com/postgres/FAQ/faq-english.html#4.22 ) on how IN is very slow. =20 So I rewrote the query: =20 \o ./data/temp.txt SELECT current_timestamp; UPDATE user_account SET last_name =3D 'abc' WHERE EXISTS (SELECT ua.user_account_id FROM user_account ua, commercial_entity ce, commercial_service cs WHERE ua.user_account_id =3D ce.user_account_id AND ce.commercial_entity_id =3D cs.commercial_entity_id); SELECT current_timestamp; \o =20 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. =20 Is there any way around this other than breaking the query into two? As in: =20 pstmt1 =3D conn.preprareStatement("SELECT ua.user_account_id FROM user_account ua, commercial_entity ce, commercial_service cs WHERE ua.user_account_id =3D ce.user_account_id AND ce.commercial_entity_id =3D cs.commercial_entity_id"); rset =3D pstmt1.executeQuery(); while (rset.next()) { pstmt2 =3D conn.prepareStatement("UPDATE user_account SET last_name =3D 'abc' WHERE user_account_id =3D ?"); pstmt2.setLong(1, rset.getLong(1)); ... } =20 Unfort, that will be alot of data moved from Postgres->middle-tier (Weblogic/Resin), which is inefficient. =20 Anyone see another solution? =20 David. ----- Original Message -----=20 From: David Griffiths=20 To: pgsql-performance@postgresql.org =20=20 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, =20 UPDATE user_account SET last_name =3D '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 =3D commercial_service.commercial_entity_id); =20 =20 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: =20 benchtest=3D# EXPLAIN ANALYZE UPDATE user_account SET last_name =3D '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 =3D commercial_service.commercial_entity_id); Seq Scan on user_account (cost=3D0.00..813608944.88 rows=3D36242 width=3D718) (actual time=3D15696258.98..16311130.29 rows=3D3075 loops=3D1) Filter: (subplan) SubPlan -> Materialize (cost=3D11224.77..11224.77 rows=3D86952 width=3D36) (actual time=3D0.06..106.40 rows=3D84831 loops=3D72483) -> Merge Join (cost=3D0.00..11224.77 rows=3D86952 width=3D36) (actual time=3D0.21..1845.13 rows=3D85158 loops=3D1) Merge Cond: ("outer".commercial_entity_id =3D "inner".commercial_entity_id) -> Index Scan using commercial_entity_pkey on commercial_entity (cost=3D0.00..6787.27 rows=3D77862 width=3D24) (actual time=3D0.06..469.56 rows=3D78132 loops=3D1) Filter: (yw_account_id IS NULL) -> Index Scan using comm_serv_comm_ent_id_i on commercial_service (cost=3D0.00..2952.42 rows=3D88038 width=3D12) (actual time=3D0.03..444.80 rows=3D88038 loops=3D1) Total runtime: 16332976.21 msec (10 rows) =20 Here are the relevant parts of the schema: =20 =20 =20 USER_ACCOUNT =20 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 =20 =20 =20 COMMERCIAL_ENTITY =20 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 =20 =20 =20 COMMERCIAL_SERVICE=20 =20 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 =20 =20 Here is the postgres.conf (or the variables that are not commented out): =20 tcpip_socket =3D true max_connections =3D 500 shared_buffers =3D 32768 # min max_connections*2 or 16, 8KB each wal_buffers =3D 128 # min 4, typically 8KB each sort_mem =3D 4096 # min 64, size in KB effective_cache_size =3D 50000 # typically 8KB each =20 Is it a problem with "IN"? =20 David ------=_NextPart_000_05A1_01C39004.F8FFABC0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Sorry - just found the FAQ (http:/= /jamesthornton.com/postgres/FAQ/faq-english.html#4.22)=20 on how IN is very slow.
 
So I rewrote the query:
 
\o ./data/temp.txt
SELECT current_timestamp;
UPDATE user_account SET last_name =3D 'abc= '
WHERE=20 EXISTS (SELECT ua.user_account_id FROM user_account ua, commercial_entity c= e,=20 commercial_service cs
WHERE ua.user_account_id =3D ce.user_account_id AN= D=20 ce.commercial_entity_id =3D cs.commercial_entity_id);
SELECT current_timestamp;
\o
 
EXISTS is kind of a weird statement, and i= t doesn't=20 appear to be identical (the number of rows updated was 72,000 rather than 3= 500).=20 It also took 4 minutes to execute.
 
Is there any way around this other than br= eaking=20 the query into two? As in:
 
pstmt1 =3D conn.preprareStatement("SELECT= =20 ua.user_account_id FROM user_account ua, commercial_entity ce,=20 commercial_service cs
WHERE ua.user_account_id =3D ce.user_account_id AN= D=20 ce.commercial_entity_id =3D cs.commercial_entity_id");
rset =3D pstmt1.executeQuery();
while (rset.next())
{
    pstmt2 =3D=20 conn.prepareStatement("UPDATE user_account SET last_name =3D 'abc' WHERE=20 user_account_id =3D ?");
    pstmt2.setLong(1,=20 rset.getLong(1));
    ...
}
 
Unfort, that will be alot of data moved fr= om=20 Postgres->middle-tier (Weblogic/Resin), which is inefficient.
 
Anyone see another solution?
 
David.
----- Original Message -----
Fro= m:=20 Davi= d=20 Griffiths
To: pgsql-performance@postgresql.org= =20
Sent: Saturday, October 11, 2003 1= 2:44=20 PM
Subject: [PERFORM] Another weird o= ne with=20 an UPDATE

I am running an update-query to benchmark various databases; th= e=20 postgres version is,
 
UPDATE user_account SET last_name =3D 'abc'
WHERE=20 user_account_id IN (SELECT user_account_id FROM commercial_entity,= =20 commercial_service WHERE yw_account_id IS NULL
AND=20 commercial_entity.commercial_entity_id =3D=20 commercial_service.commercial_entity_id);
 
 
The inner query (the select), run by its= elf,=20 takes about a second. Add the outer query (the update-portion), and the q= uery=20 dies. The machine has been vacuum-analzyed. Here is the=20 explain-analyze:
 
benchtest=3D# EXPLAIN ANALYZE UPDATE use= r_account=20 SET last_name =3D 'abc'
benchtest-# WHERE user_account_id IN (SELECT= =20 user_account_id FROM commercial_entity, commercial_service WHERE yw_accou= nt_id=20 IS NULL
benchtest(# AND commercial_entity.commercial_entity_id =3D=20 commercial_service.commercial_entity_id);
 Seq Scan on user_account =20 (cost=3D0.00..813608944.88 rows=3D36242 width=3D718) (actual=20 time=3D15696258.98..16311130.29 rows=3D3075 loops=3D1)   Filter= :=20 (subplan)
   SubPlan
     -> = =20 Materialize  (cost=3D11224.77..11224.77 rows=3D86952 width=3D36) (ac= tual=20 time=3D0.06..106.40 rows=3D84831=20 loops=3D72483)
         &= nbsp;=20 ->  Merge Join  (cost=3D0.00..11224.77 rows=3D86952 width=3D= 36) (actual=20 time=3D0.21..1845.13 rows=3D85158=20 loops=3D1)
          = ;      =20 Merge Cond: ("outer".commercial_entity_id =3D=20 "inner".commercial_entity_id)
      &nbs= p;         =20 ->  Index Scan using commercial_entity_pkey on commercial_entity&= nbsp;=20 (cost=3D0.00..6787.27 rows=3D77862 width=3D24) (actual time=3D0.06..469.5= 6 rows=3D78132=20 loops=3D1)
          = ;            = =20 Filter: (yw_account_id IS=20 NULL)
          &nbs= p;     =20 ->  Index Scan using comm_serv_comm_ent_id_i on=20 commercial_service  (cost=3D0.00..2952.42 rows=3D88038 width=3D= 12) (actual=20 time=3D0.03..444.80 rows=3D88038 loops=3D1)
 Total runtime: 16332= 976.21=20 msec
(10 rows)
 
Here are the relevant parts of the=20 schema:
 
 
 
USER_ACCOUNT
 
          &nbs= p;=20 Column           &= nbsp;=20 |           =20 Type           &nb= sp;=20 |         =20 Modifiers
-------------------------------+----------------------------= -+-----------------------------
 user_account_id   &= nbsp;          =20 |=20 numeric(10,0)          =     =20 | not=20 null
 first_name        &= nbsp;          =20 | character varying(100)     =20 |
 last_name         = ;           =20 | character varying(100)      |
Indexes:=20 user_account_pkey primary key btree=20 (user_account_id),
        =20 usr_acc_last_name_i btree (last_name),
Foreign Key constraints: $1 FOR= EIGN=20 KEY (lang_id) REFERENCES lang(lang_id) ON UPDATE NO ACTION ON DELETE NO= =20 ACTION,
          &n= bsp;            = ; =20 $2 FOREIGN KEY (source_id) REFERENCES source(source_id) ON UPDATE NO ACTI= ON ON=20 DELETE NO=20 ACTION,
          &n= bsp;            = ; =20 $3 FOREIGN KEY (user_role_id) REFERENCES user_role(user_role_id) ON UPDAT= E NO=20 ACTION ON DELETE NO ACTION
 
 
 
COMMERCIAL_ENTITY
 
         =20 Column          =20 |           =20 Type           &nb= sp;=20 |            =             &nb= sp;=20 Modifiers
---------------------------+-----------------------------+--= -----------------------------------------------------------
 commer= cial_entity_id     =20 |=20 numeric(10,0)          =     =20 | not=20 null
 yw_account_id       &nbs= p;    =20 |=20 numeric(10,0)          =     =20 |
Indexes: commercial_entity_pkey primary key btree=20 (commercial_entity_id),
       &nbs= p;=20 comm_ent_yw_acc_id_i btree (yw_account_id)
Foreign Key constraints: $1= =20 FOREIGN KEY (source_id) REFERENCES source(source_id) ON UPDATE NO ACTION = ON=20 DELETE NO=20 ACTION,
          &n= bsp;            = ; =20 $2 FOREIGN KEY (user_account_id) REFERENCES user_account(user_account_id)= ON=20 UPDATE NO ACTION ON DELETE NO ACTION
 
 
 
COMMERCIAL_SERVICE
 
      &nbs= p;=20 Column        |    = ;=20 Type      |=20 Modifiers
----------------------+---------------+-----------
 = commercial_entity_id=20 | numeric(10,0) | not=20 null
 service_type_id      | numeric(10,= 0) |=20 not=20 null
 source_id        &n= bsp;  =20 | numeric(10,0) | not null
Indexes: commercial_service_pkey primary ke= y=20 btree (commercial_entity_id,=20 service_type_id),
        =20 comm_serv_comm_ent_id_i btree=20 (commercial_entity_id),
       &nbs= p;=20 comm_serv_serv_type_id_i btree=20 (service_type_id),
        =20 comm_serv_source_id_i btree (source_id)
Foreign Key constraints: $1 FO= REIGN=20 KEY (commercial_entity_id) REFERENCES commercial_entity(commercial_entity= _id)=20 ON UPDATE NO ACTION ON DELETE NO=20 ACTION,
          &n= bsp;            = ; =20 $2 FOREIGN KEY (source_id) REFERENCES source(source_id) ON UPDATE NO ACTI= ON ON=20 DELETE NO=20 ACTION,
          &n= bsp;            = ; =20 $3 FOREIGN KEY (service_type_id) REFERENCES service_type(service_type_id)= ON=20 UPDATE NO ACTION ON DELETE NO ACTION
 
 
Here is the postgres.conf (or the variab= les that=20 are not commented out):
 
tcpip_socket =3D true
max_connections= =3D=20 500
shared_buffers =3D=20 32768          # min=20 max_connections*2 or 16, 8KB each
wal_buffers =3D=20 128           &nbs= p;  =20 # min 4, typically 8KB each
sort_mem =3D=20 4096           &nb= sp;    =20 # min 64, size in KB
effective_cach= e_size =3D=20 50000    # typically 8KB each
 
Is it a problem with "IN"?
 
David
------=_NextPart_000_05A1_01C39004.F8FFABC0--