pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: extra info - curious delay on view/where
3+ messages / 3 participants
[nested] [flat]
* Re: extra info - curious delay on view/where
@ 2004-10-28 10:42 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 3+ messages in thread
From: Gary Stainburn @ 2004-10-28 10:42 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: extra info - curious delay on view/where
@ 2004-10-28 14:53 andre.toscano <andre.toscano@uol.com.br>
2004-10-29 17:46 ` Re: extra info - curious delay on view/where Markus Schaber <schabios@logi-track.com>
0 siblings, 1 reply; 3+ messages in thread
From: andre.toscano @ 2004-10-28 14:53 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: extra info - curious delay on view/where
2004-10-28 14:53 Re: extra info - curious delay on view/where andre.toscano <andre.toscano@uol.com.br>
@ 2004-10-29 17:46 ` Markus Schaber <schabios@logi-track.com>
0 siblings, 0 replies; 3+ messages in thread
From: Markus Schaber @ 2004-10-29 17:46 UTC (permalink / raw)
To: andre.toscano <andre.toscano@uol.com.br>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-10-29 17:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-28 10:42 Re: extra info - curious delay on view/where Gary Stainburn <gary.stainburn@ringways.co.uk>
2004-10-28 14:53 Re: extra info - curious delay on view/where andre.toscano <andre.toscano@uol.com.br>
2004-10-29 17:46 ` Markus Schaber <schabios@logi-track.com>
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