Re: 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:
> 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 )

It wouldn't get evaluated if TG_OP = 'INSERT' ... but plpgsql has to
insert all the parameters of the IF expression before it passes the IF
expression off to the main executor. So you're bombing out at the
parameter-interpretation stage. I think you'll have to divide this into
two plpgsql IF statements.

> FROM
> owner
> INNER JOIN
> folder
> ON ( folder.PK = folder.FK_owner )

Surely that join condition is wrong.

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

I'm having a hard time making sense of this, since both your examples
contain the same typo --- I imagine there's an AND or OR before
item.enabled_bool, but it's hard to guess which.

However, I suspect the issue is that the planner tries to flatten the
above WHERE into conjunctive normal form, which is normally a good
optimization strategy but perhaps doesn't work real well on this case.
Still that could only affect the boolean-expression evaluation time,
and it's hard to believe that that's a large fraction of the total
join time.

What does EXPLAIN ANALYZE say about the plans for these queries?
And could we see them in typo-free form?

regards, tom lane