Diferent execution plan for similar query

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

Responses

Browse pgsql-hackers by date

  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

Browse pgsql-performance by date

  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