pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: jgimenez@sipec_quitaesto_.es
To: pgsql-performance@postgresql.org
Subject: Diferent execution plan for similar query
Date: Mon, 28 Apr 2003 12:26:03 +0200
Message-ID: <003401c30d70$92c07310$8001010a@sipec.es> (raw)

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




view thread (6+ messages)  latest in thread

Message-ID: <003401c30d70$92c07310$8001010a@sipec.es>
Permalink:  ../003401c30d70$92c07310$8001010a@sipec.es/
Also on:    postgresql.org/message-id/003401c30d70$92c07310$8001010a@sipec.es

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: jgimenez@sipec_quitaesto_.es
  Subject: Re: Diferent execution plan for similar query
  In-Reply-To: <003401c30d70$92c07310$8001010a@sipec.es>
  References: <003401c30d70$92c07310$8001010a@sipec.es>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox