Thread: curious delay on view/where

curious delay on view/where

From
Gary Stainburn
Date:
Hi folks.

I have the following view:

CREATE VIEW "stock_available" as  SELECT * FROM stock_details  WHERE available = true AND visible = true AND location
notin (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

select * from stock_available where branch != 'Doncaster'
and
select * from stock_available where branch != 'Leeds'

which only return the equivelent of the top two (we only have Leeds and 
Doncaster) are les than  1 second.

Anyone got any ideas of the cause, or thoughts on how I can trace the 
problem?

-- 
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     



Re: extra info - curious delay on view/where

From
Gary Stainburn
Date:
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     



Re: curious delay on view/where

From
Tom Lane
Date:
Gary Stainburn <gary.stainburn@ringways.co.uk> writes:
> Anyone got any ideas of the cause, or thoughts on how I can trace the 
> problem?

EXPLAIN ANALYZE results for the fast and slow cases would be
interesting.  Also, have you ANALYZEd the underlying tables lately?
And what PG version is this?
        regards, tom lane


Re: curious delay on view/where

From
Gary Stainburn
Date:
On Thursday 28 October 2004 3:25 pm, Tom Lane wrote:
> Gary Stainburn <gary.stainburn@ringways.co.uk> writes:
> > Anyone got any ideas of the cause, or thoughts on how I can trace
> > the problem?
>
> EXPLAIN ANALYZE results for the fast and slow cases would be
> interesting.  Also, have you ANALYZEd the underlying tables lately?
> And what PG version is this?
>
>             regards, tom lane

Hi Tom.

I've the analyze but don't understand what it's telling me.  I've made 
it available at http://www.stainburn.com/analyze.txt

Gary
-- 
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