Date: Thu, 28 Oct 2004 11:53:25 -0300
Message-Id: <I6AUP1$D20A31AD6BA04587AF2C81E6DEC05C48@uol.com.br>
Subject: Re: extra info - curious delay on view/where
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
From: "andre.toscano" <andre.toscano@uol.com.br>
To: "pgsql-sql" <pgsql-sql@postgresql.org>

Hi Folks,

It=B4s just for curiosity I ask:

Could an INDEX speed up that SELECT?

Thanks in advance, and sorry for my newbies questions.

Andr=E9
(Brazilian User)

> On Thursday 28 October 2004 11:16 am, Gary Stainburn wrote:
> > Hi folks.
> >
> > I have the following view:
> >
> > CREATE VIEW "stock_available" as
> >   SELECT * FROM stock_details
> >   WHERE available =3D true AND visible =3D true AND
> >   location not in (SELECT descr FROM ignored);
> >
> > Stock_details is itself a view pulling in a number of 
tables.
> > Everything works fine until I try to pull in only the 
details for a
> > specific branch, using the following.
> >
> > select * from stock_available where branch =3D 'Leeds';
> > or
> > select * from stock_available where branch =3D 'Doncaster';
> >
> > At this point, the query takes 11 seconds. Any other 
quiery,
> > including
> [snip]
> 
> Once thing I forgot to mention.  If I run the above on the 
base view 
> stock_details, it returns in < 1 second too.
> -- 
> Gary Stainburn
>  
> This email does not contain private or confidential 
material as it
> may be snooped on by interested government parties for 
unknown
> and undisclosed purposes - Regulation of Investigatory 
Powers Act, 2000     
> 
> 
> ---------------------------(end of broadcast)---------------
------------
> TIP 9: the planner will ignore your desire to choose an 
index scan if your
>       joining column's datatypes do not match
> =0A =0A________________________________________________________________=
__________=0AAcabe com aquelas janelinhas que pulam na sua tela.=0AAntiPo=
p-up UOL - =C9 gr=E1tis!=0Ahttp://antipopup.uol.com.br/=0A