Tom Lane wrote:
Chris writes:
I can see it's doing the extra filter step at the start (4th line) which
is not present without the coalesce/case statement. I just don't
understand why it's being done at that stage.
It's not that hard to understand. With the original view formulation
(or the
On Sun, Jan 18, 2009 at 7:30 PM, Chris wrote:
> I have a view that looks like this:
>
> SELECT
>CASE
>WHEN r.assetid IS NULL THEN p.assetid
>ELSE r.assetid
>END AS assetid,
>CASE
>WHEN r.userid IS NULL THEN p.userid
>ELSE r.
Chris writes:
> I can see it's doing the extra filter step at the start (4th line) which
> is not present without the coalesce/case statement. I just don't
> understand why it's being done at that stage.
It's not that hard to understand. With the original view formulation
(or the COALESCE vers
I thought the where condition would cut down on the rows returned, then the
case statement would take effect to do the null check. It seems to be doing
it in reverse ??
# explain analyze SELECT * from sq_vw_ast_perm where assetid='30748';
It aperas to me that both of your statements have whe
> My guess is you can exclude rows with WHERE if the the
> column used is an run-time computation involving an ad-hoc CASE.
* that you can't
--
Evan Carroll
System Lord of the Internets
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subs
The reason why the CASE is affecting your query planning is because
you are using a query that compares assetid to a constant:
SELECT * from sq_vw_ast_perm where assetid='30748';
When PostgreSQL evaluates this statement, assetid gets expanded either
into a case statement (with your first view
On Sun, Jan 18, 2009 at 9:30 PM, Chris wrote:
> Hi all,
>
> I have a view that looks like this:
>
> SELECT
>CASE
>WHEN r.assetid IS NULL THEN p.assetid
>ELSE r.assetid
>END AS assetid,
>CASE
>WHEN r.userid IS NULL THEN p.userid
>
On Sun, Jan 18, 2009 at 10:30 PM, Chris wrote:
> Hi all,
>
> I have a view that looks like this:
>
> SELECT
>CASE
>WHEN r.assetid IS NULL THEN p.assetid
>ELSE r.assetid
>END AS assetid,
>CASE
>WHEN r.userid IS NULL THEN p.userid
>
Hi all,
I have a view that looks like this:
SELECT
CASE
WHEN r.assetid IS NULL THEN p.assetid
ELSE r.assetid
END AS assetid,
CASE
WHEN r.userid IS NULL THEN p.userid
ELSE r.userid
END AS userid, p.permission, p."gra
