pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Diferent 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