curious delay on view/where

From: Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
To: pgsql-sql(at)postgresql(dot)org
Subject: curious delay on view/where
Date: 2004-10-28 10:16:44
Message-ID: 200410281116.44972.gary.stainburn@ringways.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Gary Stainburn 2004-10-28 10:42:46 Re: extra info - curious delay on view/where
Previous Message Markus Schaber 2004-10-28 10:03:20 Select Instead on a table