Re: how to avoid repeating expensive computation in select
От | Nicklas Avén |
---|---|
Тема | Re: how to avoid repeating expensive computation in select |
Дата | |
Msg-id | 1296769585.1926.140.camel@ubuntu64 обсуждение исходный текст |
Ответ на | Re: how to avoid repeating expensive computation in select (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: how to avoid repeating expensive computation in select
|
Список | pgsql-general |
That's interesting Tom. This is a discussion coming up sometimes at PostGIS lists since PostGIS often handles "expensive" calculations. Regina wrote a blog post about it: http://postgresonline.com/journal/archives/113-How-to-force-PostgreSQL-to-use-a-pre-calculated-value.html I thought the "offset 0" trick was just a dirty hack, but coming from you, Tom, I assume it is a robust way of doing it. I also tried some of the queries we tried then, almost 2 years ago, and I think it seems like PostgreSQL handles this much better in 9.0. Is that possible? What was strange then was that PostGIS functions marked immutable also was recalculated between Select and where-clause and also if used multiple times in the Select part. But I think (from very few tests) that the result of the function was reused in a better way now. Can that be the case? Thanks Nicklas Avén On Thu, 2011-02-03 at 13:16 -0500, Tom Lane wrote: > Bob Price <rjp_email@yahoo.com> writes: > > I would like to know if there is a way in PostgreSQL to avoid repeating an expensive computation in a SELECT where theresult 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. > > regards, tom lane >
В списке pgsql-general по дате отправления: