Diferent execution plan for similar query

Поиск
Список
Период
Сортировка
От
Тема Diferent execution plan for similar query
Дата
Msg-id 003401c30d70$92c07310$8001010a@sipec.es
обсуждение исходный текст
Ответы Re: Diferent execution plan for similar query  (Shridhar Daithankar <shridhar_daithankar@persistent.co.in>)
Список 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


В списке pgsql-performance по дате отправления:

Предыдущее
От: "Christopher Kings-Lynne"
Дата:
Сообщение: Re: pgsql BLOB issues
Следующее
От: Shridhar Daithankar
Дата:
Сообщение: Re: Diferent execution plan for similar query