Hello 2011/2/3 Tom Lane <tgl@xxxxxxxxxxxxx>: > Bob Price <rjp_email@xxxxxxxxx> writes: >> I would like to know if there is a way in PostgreSQL to avoid repeating an expensive computation in a SELECT where the result is needed both as a returned value and as an expression in the WHERE clause. > > Use a subselect. ÂYou might need OFFSET 0 to prevent the planner from > "flattening" the subselect, eg > > Â ÂSELECT whatever FROM > Â Â Â(SELECT *, expensivefunc(value) AS score FROM mytable OFFSET 0) ss > Â ÂWHERE id LIKE '%z%' AND score > 0.5; > > Keep in mind that in the above formulation, expensivefunc will be > evaluated at rows that don't pass the LIKE test. ÂSo you probably want > to push down as much as you can into the sub-select's WHERE clause. > The planner will not help you with that if you put in the OFFSET 0 > optimization-fence. ÂIt's a good idea to use EXPLAIN (or even better > EXPLAIN VERBOSE, if you're using >= 8.4) to confirm that you're getting > the plan you want. What about to increase a COST value? Can it help? Regards Pavel > > Â Â Â Â Â Â Â Â Â Â Â Âregards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
