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
|
Список | 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 по дате отправления: