pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed From: jgimenez@sipec_quitaesto_.es
To: pgsql-performance@postgresql.org
Subject: Diferent execution plan for similar query
Date: Mon, 28 Apr 2003 12:26:03 +0200
Message-ID: <003401c30d70$92c07310$8001010a@sipec.es> (raw )
Somebody could explain me why this query...
SELECT *
FROM articulos,eans
WHERE articulos.id_iinterno=eans.id_iinterno
AND eans.id_iean=345
is slower than this one? (the difference is the quotes around the
number....)
SELECT *
FROM articulos,eans
WHERE articulos.id_iinterno=eans.id_iinterno
AND eans.id_iean='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=0.00..8026.85 rows=1 width=133)
-> Seq Scan on eans (cost=0.00..8023.74 rows=1 width=16)
-> Index Scan using articulos_pk on articulos (cost=0.00..3.10 rows=1
width=117)
And this is the second:
Nested Loop (cost=0.00..9.12 rows=1 width=133)
-> Index Scan using eans_pk on eans (cost=0.00..6.01 rows=1 width=16)
-> Index Scan using articulos_pk on articulos (cost=0.00..3.10 rows=1
width=117)
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énez .
Analista Software Departamento Calypso.
Soluciones Informáticas Para El Comercio, S.L.
jgimenez(arroba)sipec.es
view thread (6+ messages) latest in thread
Message-ID: <003401c30d70$92c07310$8001010a@sipec.es>
Permalink: ../003401c30d70$92c07310$8001010a@sipec.es/
Also on: postgresql.org/message-id/003401c30d70$92c07310$8001010a@sipec.es
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: jgimenez@sipec_quitaesto_.es
Subject: Re: Diferent execution plan for similar query
In-Reply-To: <003401c30d70$92c07310$8001010a@sipec.es>
References: <003401c30d70$92c07310$8001010a@sipec.es>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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