Re: Duplicate deletion optimizations

Hi Pierre!

On 7 January 2012 12:20, Pierre C <lists(at)peufeu(dot)com> wrote:
> I'm stuck home with flu, so I'm happy to help ;)
[...]
> I'll build an example setup to make it clearer...
[...]

That's almost identical to my tables. :-)

> Note that the "distance" field represents the distance (in time) between the
> interpolated value and the farthest real data point that was used to
> calculate it. Therefore, it can be used as a measure of the quality of the
> interpolated point ; if the distance is greater than some threshold, the
> value might not be that precise.

Nice idea!

> Although this query is huge, it's very fast, since it doesn't hit the big
> tables with any seq scans (hence the max() and min() tricks to use the
> indexes instead).

And it can easily be tamed by putting parts of it into stored pgpsql functions.

> I love how postgres can blast that huge pile of SQL in, like, 50 ms...

Yes, indeed. It's incredible fast. Brilliant!

> If there is some overlap between packet data and data already in the log,
> you might get some division by zero errors, in this case you'll need to
> apply a DISTINCT somewhere (or simply replace the UNION ALL with an UNION,
> which might be wiser anyway...)

I do have a unique constraint on the actual table to prevent duplicate
data in case of retransmission after a failed connect. It's easy
enough to delete the rows from packet that already exist in the main
table with a short one line SQL delete statement before the
interpolation and merge.

> Tada.

:-))))

> Enjoy !

I certainly will. Many thanks for those great lines of SQL!

Hope you recover from your flu quickly!

All the best,
Marc