I'm trying to work out if the queries below will always produce
identical results. The second is a lot faster. It seems logical to me
that a correlated subquery involving an aggregate as below could in
most cases be converted to a join (assuming the aggregate is stable).
Put it another way, the correlated subquery always produces the same
result for any particular set of values "passed" to it, so it may be
advantagous to calculate in the FROM section and use a join.
Could PostgreSQL transform these automatically easily? Do we need to
assume that the id field can never be NULL (such as in this case).
There seem to be references to papers suggesting something like this
online, but I can't find a good description.Aany ideas?
Query #1:
update transact set billed = true
where transdate <=
(select max(transdate)
from transact t
where t.type = 'bill'
and t.id = transact.id);
Query #2:
update transact set billed = true
from (select id, max(transdate)
from transact t
where t.type = 'bill'
group by t.id) as sub
where transact.id = sub.id
and transact.transdate <= sub.max;
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.