From: Albert Cervera Areny <albert@sedifa.com>
Organization: Sedifa, S.L.
To: pgsql-performance@postgresql.org
Subject: Re: Posible planner improvement?
Date: Wed, 21 May 2008 13:37:49 +0200
User-Agent: KMail/1.9.9
References: <200805211228.48248.albert@sedifa.com>
	<48340687.9010501@mark.mielke.cc>
	<200805211330.16604.albert@sedifa.com>
In-Reply-To: <200805211330.16604.albert@sedifa.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200805211337.49472.albert@sedifa.com>

A Dimecres 21 Maig 2008, Albert Cervera Areny va escriure:
> A Dimecres 21 Maig 2008, Mark Mielke va escriure:
> > A Dimecres 21 Maig 2008, Richard Huxton va escriure:
> > >> Albert Cervera Areny wrote:
> > >>> I've got a query similar to this:
> > >>>
> > >>> select * from t1, t2 where t1.id > 158507 and t1.id =3D t2.id;
> > >>>
> > >>> That took > 84 minutes (the query was a bit longer but this is the
> > >>> part that made the difference) after a little change the query took
> > >>> ~1 second:
> > >>>
> > >>> select * from t1, t2 where t1.id > 158507 and t2.id > 158507 and
> > >>> t1.id =3D t2.id;
> > >>
> > >> Try posting EXPLAIN ANALYSE SELECT ... for both of those queries and
> > >> we'll see why it's better at the second one.
> >
> > Even if the estimates were off (they look a bit off for the first
> > table), the above two queries are logically identical, and I would
> > expect the planner to make the same decision for both.
> >
> > I am curious - what is the result of:
> >
> >     select * from t1, t2 where t2.id > 158507 and t1.id =3D t2.id;
> >
> > Is it the same speed as the first or second, or is a third speed
> > entirely?
>
> Attached the same file with the third result at the end. The result is
> worst than the other two cases. Note that I've analyzed both tables but
> results are the same. One order of magnitude between the two first querie=
s.

Sorry, it's not worse than the other two cases as shown in the file. Howeve=
r,=20
after repetition it seems the other two seem to decrease more than the thir=
d=20
one whose times vary a bit more and some times take up to 5 seconds.

Other queries are running in the same machine, so take times with a grain o=
f=20
salt. What's clear is that always  there's a big difference between first a=
nd=20
second queries.

>
> > If t1.id =3D t2.id, I would expect the planner to substitute them freely
> > in terms of identities?
> >
> > Cheers,
> > mark


=2D-=20
Albert Cervera Areny
Dept. Inform=E0tica Sedifa, S.L.

Av. Can Bordoll, 149
08202 - Sabadell (Barcelona)
Tel. 93 715 51 11
=46ax. 93 715 51 12

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=2E.......................  AVISO LEGAL  ............................
La   presente  comunicaci=F3n  y sus anexos tiene como destinatario la
persona a  la  que  va  dirigida, por  lo  que  si  usted lo  recibe
por error  debe  notificarlo  al  remitente  y   eliminarlo   de  su
sistema,  no  pudiendo  utilizarlo,  total  o   parcialmente,   para
ning=FAn  fin.  Su  contenido  puede  tener informaci=F3n confidencial o
protegida legalmente   y   =FAnicamente   expresa  la  opini=F3n     del
remitente.  El   uso   del   correo   electr=F3nico   v=EDa Internet  no
permite   asegurar    ni  la   confidencialidad   de   los  mensajes
ni    su    correcta     recepci=F3n.   En    el  caso   de   que   el
destinatario no consintiera la utilizaci=F3n  del correo  electr=F3nico,
deber=E1 ponerlo en nuestro conocimiento inmediatamente.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=2E.......................... DISCLAIMER .............................
This message and its  attachments are  intended  exclusively for the
named addressee. If you  receive  this  message  in   error,  please
immediately delete it from  your  system  and notify the sender. You
may  not  use  this message  or  any  part  of it  for any  purpose.
The   message   may  contain  information  that  is  confidential or
protected  by  law,  and  any  opinions  expressed  are those of the
individual    sender.  Internet  e-mail   guarantees   neither   the
confidentiality   nor  the  proper  receipt  of  the  message  sent.
If  the  addressee  of  this  message  does  not  consent to the use
of   internet    e-mail,    please    inform     us    inmmediately.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D


=20