Обсуждение: curious delay on view/where

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     

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     

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

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