Diferent execution plan for similar query
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
On Monday 28 April 2003 15:56, jgimenez@sipec_quitaesto_.es wrote:
Somebody could explain me why this query...
SELECT *
FROM articulos,eans
WHERE articulos.id_iinterno=eans.id_iinterno
AND eans.id_iean=345is 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'
In second case, postgresql typecasted it correctly. Even
eans.id_iean=345::int8 would have worked the same way. By default postgresql
treats a number as int4 while comparing and integer and float8 for a real
numbe. I discovered that yesterday.
Until the planner/parser gets smarter, this is going to be an FAQ..
Shridhar
Shridhar Daithankar said:
In second case, postgresql typecasted it correctly. Even
eans.id_iean=345::int8 would have worked the same way. By default
postgresql
treats a number as int4 while comparing and integer and float8 for a real
numbe. I discovered that yesterday.Until the planner/parser gets smarter, this is going to be an FAQ..
Shridhar
Is this an nontrivial change?
Because if it's trivial it should be done, imho.
I've been bitten indirectly of this, and it's not too easy to find out
always. I think that this is one of the most unobvious performance hickups
there are with postgresql.
Magnus
On Monday 28 April 2003 16:29, Magnus Naeslund(w) wrote:
Shridhar Daithankar said:
In second case, postgresql typecasted it correctly. Even
eans.id_iean=345::int8 would have worked the same way. By default
postgresql
treats a number as int4 while comparing and integer and float8 for a real
numbe. I discovered that yesterday.Until the planner/parser gets smarter, this is going to be an FAQ..
Shridhar
Is this an nontrivial change?
Because if it's trivial it should be done, imho.
I've been bitten indirectly of this, and it's not too easy to find out
always. I think that this is one of the most unobvious performance hickups
there are with postgresql.
I would say dig into hackers archives for the consensus(??) reached.. I don't
remember..
Shridhar