From: | Kevin Grittner <kgrittn(at)ymail(dot)com> |
---|---|
To: | James David Smith <james(dot)david(dot)smith(at)gmail(dot)com>, Erik Darling <edarling80(at)gmail(dot)com> |
Cc: | PGSQL-Novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Update with last known location? |
Date: | 2014-01-30 16:45:19 |
Message-ID: | 1391100319.50253.YahooMailNeo@web122306.mail.ne1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
James David Smith <james(dot)david(dot)smith(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | James David Smith | 2014-01-30 16:52:29 | Re: Update with last known location? |
Previous Message | David Johnston | 2014-01-30 15:46:41 | Re: Update with last known location? |