pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDiferent execution plan for similar query
6+ messages / 4 participants
[nested] [flat]
* Diferent execution plan for similar query
@ 2003-04-28 10:26 jgimenez@sipec_quitaesto_.es
0 siblings, 1 reply; 6+ messages in thread
From: jgimenez@sipec_quitaesto_.es @ 2003-04-28 10:26 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Diferent execution plan for similar query
@ 2003-04-28 10:41 Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
parent: jgimenez@sipec_quitaesto_.es
0 siblings, 1 reply; 6+ messages in thread
From: Shridhar Daithankar @ 2003-04-28 10:41 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Diferent execution plan for similar query
@ 2003-04-28 10:59 Magnus Naeslund(w) <mag@fbab.net>
parent: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
0 siblings, 1 reply; 6+ messages in thread
From: Magnus Naeslund(w) @ 2003-04-28 10:59 UTC (permalink / raw)
To: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Diferent execution plan for similar query
@ 2003-04-28 11:00 Magnus Naeslund(w) <mag@fbab.net>
0 siblings, 1 reply; 6+ messages in thread
From: Magnus Naeslund(w) @ 2003-04-28 11:00 UTC (permalink / raw)
To: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [PERFORM] Diferent execution plan for similar query
@ 2003-04-28 11:00 Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
parent: Magnus Naeslund(w) <mag@fbab.net>
0 siblings, 0 replies; 6+ messages in thread
From: Shridhar Daithankar @ 2003-04-28 11:00 UTC (permalink / raw)
To: pgsql-performance; +Cc: pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Diferent execution plan for similar query
@ 2003-04-28 11:12 Andrew Sullivan <andrew@libertyrms.info>
parent: Magnus Naeslund(w) <mag@fbab.net>
0 siblings, 0 replies; 6+ messages in thread
From: Andrew Sullivan @ 2003-04-28 11:12 UTC (permalink / raw)
To: pgsql-performance
On Mon, Apr 28, 2003 at 01:00:03PM +0200, Magnus Naeslund(w) wrote:
> Is this an nontrivial change?
> Because if it's trivial it should be done, imho.
It's not trivial. If it were, it would have been done already.
See the TODO entries about this, and the many discussions about it on
-hackers, for why it's not trivial.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-04-28 11:12 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-28 10:26 Diferent execution plan for similar query jgimenez@sipec_quitaesto_.es
2003-04-28 10:41 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-04-28 10:59 ` Magnus Naeslund(w) <mag@fbab.net>
2003-04-28 11:00 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-04-28 11:00 Re: Diferent execution plan for similar query Magnus Naeslund(w) <mag@fbab.net>
2003-04-28 11:12 ` Andrew Sullivan <andrew@libertyrms.info>
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