Re: Update with last known location?

James David Smith <james.david.smith@gmail.com> wrote:
> On 30 January 2014 16:45, Kevin Grittner <kgrittn@ymail.com> wrote:

>> 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
>>         )
>> ;

> I can't run it on my actual data at the moment, or rather I don't want
> too, as I set the below query going an hour or so ago and thought I
> should let it finish really.
>
> If it hasn't finished when I come into work tomorrow (I'm leaving the
> office shortly) then I'll cancel it and give yours a crack instead. To
> my novice eye, your code looks like it'll be quicker than the below
> anyway.

> SELECT
>   data.ppid,
>   data.point_time,
>   CASE
>     WHEN data.the_geom IS NULL
>     THEN (
>       --Get all locations with an earlier time stamp for that ppid
>       SELECT geom.the_geom
>       FROM hybrid_location geom
>       WHERE data.ppid = geom.ppid
>       AND geom.point_time < data.point_time
>       AND geom.the_geom IS NOT NULL
>       AND NOT EXISTS (
>         -- Cull all but the most recent one
>         SELECT *
>         FROM hybrid_location cull
>         WHERE cull.ppid = geom.ppid
>         AND geom.the_geom IS NOT NULL
>         AND cull.point_time < data.point_time
>         AND cull.point_time > geom.point_time
>         AND cull.the_geom IS NOT NULL
>         )
>     )
>   ELSE data.the_geom
>   end
> FROM hybrid_location data;

Yeah, that's basically the same approach, but it uses a subquery
which I don't think can get pulled up -- so I think it will need to
do a lot of the work once for each row with a NULL the_geom column
where my version can do it once, period.

If you haven't tuned your configuration, you can probably speed up
any of these versions with a few tweaks to memory allocation and
cost factors.  The most significant for this query would probably
be to set work_mem to something around 25% of machine RAM divided
by the number of active connections you can have.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company