Re: b1 OR b2 <-> ( CASE WHEN b1 THE true ELSE b2 END ): performance bottleneck on logical OR

Regarding the trigger problem, it is exactly as I have described it in the
first place:

IF ( ( TG_OP = 'INSERT' ) OR ( ( TG_OP = 'UPDATE' ) AND ( old.X !=
new.X ) ) ) THEN
-- Do actions depending on field X when inserted or when **changed** (thus
avoiding useless action if field X didn't change)
END IF;

--> Error on **insert**: 'record old is unassigned yet'. Am I wrong assuming
that even though the ( TG_OP = 'INSERT' ) is true and ( TG_OP = 'UPDATE' )
is false, ( old.X != new.X ) seems to be evaluated ? ( which causes the
error )

As for what I actually did, it looks like (it is a query that simulate
permissions management, much the same as on a file system):

*****
* 1 *
*****
SELECT
( CASE WHEN owner.admin_bool THEN true ELSE COALESCE(
item_token.permit_bool, folder_token.permit_bool, owner.permit_bool ) END )
AS permit_bool
FROM
owner
INNER JOIN
folder
ON ( folder.PK = folder.FK_owner )
LEFT JOIN
folder_token
ON ( folder.PK = folder_token.PK )
INNER JOIN
item
ON ( folder.PK = item.FK_folder )
LEFT JOIN
item_token
ON ( item.PK = item_token.PK )
WHERE
( CASE WHEN owner.admin_bool THEN true
ELSE (
folder.enabled_bool
AND ( ( folder.enable_date IS NULL ) OR ( folder.enable_date <=
CURRENT_TIMESTAMP ) )
AND ( ( folder.Disable_date IS NULL ) OR ( folder.disable_date >
CURRENT_TIMESTAMP ) )
item.enabled_bool
AND ( ( item.enable_date IS NULL ) OR ( item.enable_date <=
CURRENT_TIMESTAMP ) )
AND ( ( item.disable_date IS NULL ) OR ( item.disable_date >
CURRENT_TIMESTAMP ) )
END )

*****
* 2 *
*****
SELECT
( owner.admin_bool OR COALESCE( item_token.permit_bool,
folder_token.permit_bool, owner.permit_bool ) ) AS permit_bool
FROM
owner
INNER JOIN
folder
ON ( folder.PK = folder.FK_owner )
LEFT JOIN
folder_token
ON ( folder.PK = folder_token.PK )
INNER JOIN
item
ON ( folder.PK = item.FK_folder )
LEFT JOIN
item_token
ON ( item.PK = item_token.PK )
WHERE
owner.admin_bool OR
(
folder.enabled_bool
AND ( ( folder.enable_date IS NULL ) OR ( folder.enable_date <=
CURRENT_TIMESTAMP ) )
AND ( ( folder.Disable_date IS NULL ) OR ( folder.disable_date >
CURRENT_TIMESTAMP ) )
item.enabled_bool
AND ( ( item.enable_date IS NULL ) OR ( item.enable_date <=
CURRENT_TIMESTAMP ) )
AND ( ( item.disable_date IS NULL ) OR ( item.disable_date >
CURRENT_TIMESTAMP ) )
)

owner is 100 rows,
folder is 10'000 rows,
item is 1'000'000 rows
no indexes on the columns involved in the boolean expressions

In the case where 'owner.admin_bool' is always true, *1* executed 2 to 3
times faster as *2* (after launching a new connection for each scenario - in
order to have a "clean" backend process - and running the query several
times for each scenario - no changes on the data - and taking the average
runtime value, once it is stable ). Am I missing something ?

Regards,

Cedric Dufour

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Friday, August 02, 2002 21:40
> To: Cédric Dufour
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] b1 OR b2 <-> ( CASE WHEN b1 THE true ELSE b2 END
> ): performance bottleneck on logical OR
>
>
> =?iso-8859-1?Q?C=E9dric_Dufour?= <cedric(dot)dufour(at)freesurf(dot)ch> writes:
> > This tends to prove that the normal OR expression evaluates
> both left and
> > right expression, though evaluating the right expression is
> useless provided
> > the left expression is true.
>
> It proves no such thing. How about showing us what you actually did,
> rather than jumping to (incorrect) conclusions?
>
> regards, tom lane
>