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


Mon, 28 Apr 2003 12:26:03 +0200
<jgimenez@sipec_quitaesto_.es>
Diferent execution plan for similar query
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


Mon, 28 Apr 2003 16:11:33 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Re: Diferent execution plan for similar query

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


Mon, 28 Apr 2003 12:59:07 +0200 (CEST)
"Magnus Naeslund(w)" <mag@fbab.net>
Re: Diferent execution plan for similar query
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


Mon, 28 Apr 2003 16:30:55 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Re: [PERFORM] Diferent execution plan for similar query