Thread: 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=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' 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
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
On Mon, Apr 28, 2003 at 01:00:03PM +0200, Magnus Naeslund(w) wrote:
> Is this an nontrivial change?
> Because if it's trivial it should be done, imho.
It's not trivial. If it were, it would have been done already.
See the TODO entries about this, and the many discussions about it on
-hackers, for why it's not trivial.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110