From: | <jgimenez(at)sipec_quitaesto_(dot)es> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Diferent execution plan for similar query |
Date: | 2003-04-28 10:26:03 |
Message-ID: | 003401c30d70$92c07310$8001010a@sipec.es |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Shridhar Daithankar | 2003-04-28 10:41:33 | Re: Diferent execution plan for similar query |
Previous Message | Gavin Sherry | 2003-04-28 09:37:57 | INSERT/UPDATE ... RETURNING |
From | Date | Subject | |
---|---|---|---|
Next Message | Shridhar Daithankar | 2003-04-28 10:41:33 | Re: Diferent execution plan for similar query |
Previous Message | Christopher Kings-Lynne | 2003-04-28 08:19:50 | Re: pgsql BLOB issues |