X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 583F44758C9 for ; Mon, 28 Apr 2003 06:39:14 -0400 (EDT) Received: from dos.sipec.es (unknown [62.36.228.122]) by postgresql.org (Postfix) with ESMTP id 24866474E42 for ; Mon, 28 Apr 2003 06:39:08 -0400 (EDT) Received: from sistemests2 ([10.1.1.128]) by dos.sipec.es with Microsoft SMTPSVC(5.0.2195.5329); Mon, 28 Apr 2003 12:26:04 +0200 From: To: Subject: Diferent execution plan for similar query Date: Mon, 28 Apr 2003 12:26:03 +0200 Message-ID: <003401c30d70$92c07310$8001010a@sipec.es> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 Importance: Normal X-OriginalArrivalTime: 28 Apr 2003 10:26:04.0765 (UTC) FILETIME=[92D840D0:01C30D70] Content-Transfer-Encoding: quoted-printable X-Spam-Status: No, hits=-0.8 required=5.0 tests=BAYES_20,NO_DNS_FOR_FROM,NO_REAL_NAME version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/225 X-Sequence-Number: 1731 Somebody could explain me why this query... SELECT * FROM articulos,eans WHERE articulos.id_iinterno=3Deans.id_iinterno AND eans.id_iean=3D345 is slower than this one? (the difference is the quotes around the number....) SELECT * FROM articulos,eans WHERE articulos.id_iinterno=3Deans.id_iinterno AND eans.id_iean=3D'345' I really now why, but I don't undestand the reason. The execution plan for the first query uses Sequential scans, and the second one uses the index, as you can see here: Execution plan for the first query: Nested Loop (cost=3D0.00..8026.85 rows=3D1 width=3D133) -> Seq Scan on eans (cost=3D0.00..8023.74 rows=3D1 width=3D16) -> Index Scan using articulos_pk on articulos (cost=3D0.00..3.10 rows= =3D1 width=3D117) And this is the second: Nested Loop (cost=3D0.00..9.12 rows=3D1 width=3D133) -> Index Scan using eans_pk on eans (cost=3D0.00..6.01 rows=3D1 width= =3D16) -> Index Scan using articulos_pk on articulos (cost=3D0.00..3.10 rows= =3D1 width=3D117) The field id_iean is an 8 bytes integer. Also the same for the field id_iinterno in both tables. The definition of the 2 tables is this: CREATE TABLE "eans" ( "id_iean" int8 NOT NULL, "id_iinterno" int8, CONSTRAINT "eans_pk" PRIMARY KEY ("id_iean") ) WITH OIDS; CREATE TABLE "articulos" ( "id_iinterno" int8 NOT NULL, "vsdesc_calypso" varchar(20), "id_iseccion" int4, "iprecio" int4, "ifamilia" int8, "icod_proveedor" int4, "vsmarca" varchar(10), "vsdesc_larga" varchar(22), "bnulo" bool, "bcontrol_devolucion" bool, "itipo_pedido" int2, "isurtido" int2, "ifuera_lineal" int2, "idias_caducidad" int2, "iuni_x_caja" int2, "suni_medida" varchar(2), "suni_pedido" varchar(3), CONSTRAINT "articulos_pk" PRIMARY KEY ("id_iinterno") ) WITH OIDS; What I don't understand is why the quotes in the number result in a diferent query execution. Somebody could help me? Thank you for your help. Jordi Gim=E9nez . Analista Software Departamento Calypso. Soluciones Inform=E1ticas Para El Comercio, S.L. jgimenez(arroba)sipec.es