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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: extra info - curious delay on view/where

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

Re: curious delay on view/where

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

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

Re: curious delay on view/where

On Thursday 28 October 2004 3:25 pm, Tom Lane wrote:
> Gary Stainburn 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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings