Thread: Re: extra info - curious delay on view/where

Hi Folks,

It´s just for curiosity I ask:

Could an INDEX speed up that SELECT?

Thanks in advance, and sorry for my newbies questions.

André
(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 = true AND visible = 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 = 'Leeds';
> > or
> > select * from stock_available where branch = '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
>
__________________________________________________________________________
Acabe com aquelas janelinhas que pulam na sua tela.
AntiPop-up UOL - É grátis!
http://antipopup.uol.com.br/

Hi, Andre,

On Thu, 28 Oct 2004 11:53:25 -0300
"andre.toscano" <andre.toscano@uol.com.br> wrote:

> Could an INDEX speed up that SELECT?

> > > CREATE VIEW "stock_available" as
> > >   SELECT * FROM stock_details
> > >   WHERE available = true AND visible = true AND
> > >   location not in (SELECT descr FROM ignored);

Yes, I'm shure.

I would try to create (on the underlying table) a conditional index on
the column "location" with the condition "available = true AND visible =
true".

As often, the acutal effect depends on the count of rows the query
returns compared to the total rows in the table. And it would be helpful
to know the typical queries (especially the rows in the WHERE clauses)
to give additional hints on creating indices. A matching index also
potentially speeds up ORDER BY queries.

HTH,
Markus



--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com