James David Smith <james.david.smith@gmail.com> wrote:
> I've made a self-contained example of my problem below.
It is always easier to provide advice on this sort of thing with a
self-contained test case. Looking at that, I think I would
approach it this way, at least as a first attempt, to see if
performance is good enough:
update test_data x
set the_geom = y.the_geom
from test_data y
where x.the_geom is null
and y.ppid = x.ppid
and y.the_geom is not null
and y.point_time < x.point_time
and not exists
(
select * from test_data z
where z.ppid = y.ppid
and z.the_geom is not null
and z.point_time > y.point_time
and z.point_time < x.point_time
)
;
To my eye, that is simple and straightforward. On my machine, it
runs in less than 1 ms with the provided test data; the question is
whether it scales OK. If it does not, we will need a description
of your hardware, OS, and your configuration to figure out why not.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company