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 C9852D1B4FC for ; Sat, 11 Oct 2003 19:37:16 +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 14966-05 for ; Sat, 11 Oct 2003 16:36:37 -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 C904ED1B531 for ; Sat, 11 Oct 2003 16:36:26 -0300 (ADT) Received: by BOATS_EXCH01 with Internet Mail Service (5.5.2653.19) id <4S92JT9T>; Sat, 11 Oct 2003 12:34:30 -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 4S92JT9R; Sat, 11 Oct 2003 12:34:23 -0700 From: David Griffiths To: pgsql-performance@postgresql.org Message-ID: <052001c39030$1ccc8020$6501a8c0@griffiths2> Subject: Another weird one with an UPDATE Date: Sat, 11 Oct 2003 12:44:36 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0505_01C38FF5.6D8CCF50" 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/394 X-Sequence-Number: 4142 This is a multi-part message in MIME format. ------=_NextPart_000_0505_01C38FF5.6D8CCF50 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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_0505_01C38FF5.6D8CCF50 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I am running an update-query to benchmark various databases; the= =20 postgres version is,
 
UPDATE user_account SET last_name =3D 'abc'
WHERE user_account_id&n= bsp;IN=20 (SELECT user_account_id FROM commercial_entity, commercial_service WHERE=20 yw_account_id IS NULL
AND commercial_entity.commercial_entity_id =3D=20 commercial_service.commercial_entity_id);
 
 
The inner query (the select), run by itsel= f, takes=20 about a second. Add the outer query (the update-portion), and the query die= s.=20 The machine has been vacuum-analzyed. Here is the=20 explain-analyze:
 
benchtest=3D# EXPLAIN ANALYZE UPDATE user_= account SET=20 last_name =3D 'abc'
benchtest-# WHERE user_account_id IN (SELECT=20 user_account_id FROM commercial_entity, commercial_service WHERE yw_account= _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) (actu= al=20 time=3D0.06..106.40 rows=3D84831=20 loops=3D72483)
         &nb= sp;=20 ->  Merge Join  (cost=3D0.00..11224.77 rows=3D86952 width=3D36= ) (actual=20 time=3D0.21..1845.13 rows=3D85158=20 loops=3D1)
          &= nbsp;     =20 Merge Cond: ("outer".commercial_entity_id =3D=20 "inner".commercial_entity_id)
       =          =20 ->  Index Scan using commercial_entity_pkey on commercial_entity&nb= sp;=20 (cost=3D0.00..6787.27 rows=3D77862 width=3D24) (actual time=3D0.06..469.56 = rows=3D78132=20 loops=3D1)
          &= nbsp;           =20 Filter: (yw_account_id IS=20 NULL)
           =      =20 ->  Index Scan using comm_serv_comm_ent_id_i on commercial_service&= nbsp;=20 (cost=3D0.00..2952.42 rows=3D88038 width=3D12) (actual time=3D0.03..44= 4.80=20 rows=3D88038 loops=3D1)
 Total runtime: 16332976.21 msec
(10=20 rows)
 
Here are the relevant parts of the=20 schema:
 
 
 
USER_ACCOUNT
 
           = =20 Column           &nb= sp;=20 |           =20 Type            = ;=20 |         =20 Modifiers
-------------------------------+-----------------------------+= -----------------------------
 user_account_id   &nb= sp;          =20 |=20 numeric(10,0)          &n= bsp;   =20 | not=20 null
 first_name        &nb= sp;          =20 | character varying(100)     =20 |
 last_name         &= nbsp;          =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 FOREI= GN=20 KEY (lang_id) REFERENCES lang(lang_id) ON UPDATE NO ACTION ON DELETE NO=20 ACTION,
          &nbs= p;            &= nbsp;=20 $2 FOREIGN KEY (source_id) REFERENCES source(source_id) ON UPDATE NO ACTION= ON=20 DELETE NO=20 ACTION,
          &nbs= p;            &= nbsp;=20 $3 FOREIGN KEY (user_role_id) REFERENCES user_role(user_role_id) ON UPDATE = NO=20 ACTION ON DELETE NO ACTION
 
 
 
COMMERCIAL_ENTITY
 
         =20 Column          =20 |           =20 Type            = ;=20 |            &n= bsp;            = ;=20 Modifiers
---------------------------+-----------------------------+----= ---------------------------------------------------------
 commerci= al_entity_id     =20 |=20 numeric(10,0)          &n= bsp;   =20 | not=20 null
 yw_account_id        =     =20 |=20 numeric(10,0)          &n= bsp;   =20 |
Indexes: commercial_entity_pkey primary key btree=20 (commercial_entity_id),
        = =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,
          &nbs= p;            &= nbsp;=20 $2 FOREIGN KEY (user_account_id) REFERENCES user_account(user_account_id) O= N=20 UPDATE NO ACTION ON DELETE NO ACTION
 
 
 
COMMERCIAL_SERVICE
 
       = =20 Column        |    = =20 Type      |=20 Modifiers
----------------------+---------------+-----------
 co= mmercial_entity_id=20 | numeric(10,0) | not=20 null
 service_type_id      | numeric(10,0)= |=20 not=20 null
 source_id        &nbs= p;  =20 | numeric(10,0) | not null
Indexes: commercial_service_pkey primary key = btree=20 (commercial_entity_id,=20 service_type_id),
        =20 comm_serv_comm_ent_id_i btree=20 (commercial_entity_id),
        = =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 FORE= IGN=20 KEY (commercial_entity_id) REFERENCES commercial_entity(commercial_entity_i= d) ON=20 UPDATE NO ACTION ON DELETE NO=20 ACTION,
          &nbs= p;            &= nbsp;=20 $2 FOREIGN KEY (source_id) REFERENCES source(source_id) ON UPDATE NO ACTION= ON=20 DELETE NO=20 ACTION,
          &nbs= p;            &= nbsp;=20 $3 FOREIGN KEY (service_type_id) REFERENCES service_type(service_type_id) O= N=20 UPDATE NO ACTION ON DELETE NO ACTION
 
 
Here is the postgres.conf (or the variable= s 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            =   =20 # min 4, typically 8KB each
sort_mem =3D=20 4096            = ;    =20 # min 64, size in KB
effective_cache_= size =3D=20 50000    # typically 8KB each
 
Is it a problem with "IN"?
 
David
------=_NextPart_000_0505_01C38FF5.6D8CCF50--