From: | "David Johnston" <polobo(at)yahoo(dot)com> |
---|---|
To: | "'Bob Price'" <rjp_email(at)yahoo(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: how to avoid repeating expensive computation in select |
Date: | 2011-02-03 18:07:07 |
Message-ID: | 061401cbc3cd$2bf17eb0$83d47c10$@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Is using a pl/pgsql function a viable option. Within or without the use of
a function you can create a temporary table to hold the needed intermediate
results. You can even use a permanent working table and write functions to
perform the needed queries against it.
Especially for expensive calculation you want to consider whether it is
safe/reasonable to pre-calculate and store values instead of running the
calculation during each query.
If you need procedural language capabilities (variables, multiple uses of
the same data) trying to work out a solution in pure transactional SQL can
be difficult or outright impossible; you really need to use the procedural
facilities built into the server OR your application environment. In other
words put down the hammer and go find yourself a chainsaw :)
David J
-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Bob Price
Sent: Thursday, February 03, 2011 12:18 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] how to avoid repeating expensive computation in select
I have been searching through the docs and mailing list and haven't found a
way to do this, so I thought I would ask the community.
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.
As a simple example, consider the following query on a table with 'id' and
'value' columns, and an expensive computation represented as a function:
SELECT id, expensivefunc(value) AS score FROM mytable
WHERE id LIKE '%z%' AND expensivefunc(value) > 0.5;
It would be great if I could find a way to only compute expensivefunc(value)
at most once per row, and not at all if the other WHERE constraints are not
satisfied.
For this simple case I know that I could rewrite the SELECT as something
like the following:
WITH other_where AS (
SELECT id, value FROM mytable WHERE id LIKE '%z%'
), calc_scores AS (
SELECT id, expensivefunc(value) AS score FROM other_where
)
SELECT id, score from calc_scores WHERE score > 0.5;
This works in this simple case, but my guess is that it probably adds a lot
of overhead (is this true?), and I also have to deal with much more
complicated scenarios with multiple expensive calculations that may not fit
into this kind of rewrite.
Does anyone know of a simpler way to accomplish this?
For example, it would be great if there were a function that could reference
the Nth select list item so it is only computed once, like:
SELECT id, expensivefunc(value) AS score FROM mytable
WHERE id LIKE '%z%' AND sel_list_item(2) > 0.5;
or if there were temporary variables in the WHERE expressions like:
SELECT id, tmp1 AS score FROM mytable
WHERE id LIKE '%z%' AND (tmp1 = expensivefunc(value)) > 0.5;
Any ideas anyone!
Thanks in advance!
Bob
--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-02-03 18:16:44 | Re: how to avoid repeating expensive computation in select |
Previous Message | Wappler, Robert | 2011-02-03 17:41:54 | Re: set theory question |