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@sss.pgh.pa.us]
> Sent: Friday, August 02, 2002 21:40
> To: Cédric Dufour
> Cc: pgsql-general@postgresql.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.dufour@freesurf.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
>