Re: b1 OR b2 <-> ( CASE WHEN b1 THE true ELSE b2 END ): performance bottleneck on logical OR - Mailing list pgsql-general
From | Cédric Dufour |
---|---|
Subject | Re: b1 OR b2 <-> ( CASE WHEN b1 THE true ELSE b2 END ): performance bottleneck on logical OR |
Date | |
Msg-id | NDBBIFNBODNADCAOFDOAAEJKCDAA.cedric.dufour@freesurf.ch Whole thread Raw |
In response to | Re: b1 OR b2 <-> ( CASE WHEN b1 THE true ELSE b2 END ): performance bottleneck on logical OR (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-general |
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 >
pgsql-general by date: