Range types
in [PgSql]
|
Prev: Winflex
Next: [HACKERS] Fast or immediate shutdown
From: Andrew Gierth on 17 Dec 2009 10:55 >>>>> "Tom" == Tom Lane <tgl(a)sss.pgh.pa.us> writes: Someone mentioned LATERAL? >> Tom Lane <tgl(a)sss.pgh.pa.us> writes: >>> Hm, how would you do it with LATERAL? The problem is not so much >>> composition as the need for a variable number of rounds of >>> composition. >> Let's have a try at it: >> select p2_member, array_accum(p1) >> from unnest(p2) as p2_member >> lateral (select period_except(p1_member, p2_member) >> from unnest(p1) p1_member) as x(p1); Tom> I don't think that does it. Maybe I misunderstand LATERAL, but Tom> what that looks like to me is that each p1 will be separately Tom> filtered by each p2, giving rise to a distinct element in the Tom> output. What we need is for each p1 to be filtered by *all* Tom> p2's, successively (though in any order). Right, that's not a job for LATERAL, though it could be done easily enough in one statement with a recursive CTE, I think. -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Scott Bailey on 17 Dec 2009 11:38 Tom Lane wrote: > Dimitri Fontaine <dfontaine(a)hi-media.com> writes: >> Tom Lane <tgl(a)sss.pgh.pa.us> writes: >>> Hm, how would you do it with LATERAL? The problem is not so much >>> composition as the need for a variable number of rounds of >>> composition. > >> Let's have a try at it: > >> select p2_member, array_accum(p1) >> from unnest(p2) as p2_member >> lateral (select period_except(p1_member, p2_member) >> from unnest(p1) p1_member) as x(p1); > > I don't think that does it. Maybe I misunderstand LATERAL, but what > that looks like to me is that each p1 will be separately filtered by > each p2, giving rise to a distinct element in the output. What we > need is for each p1 to be filtered by *all* p2's, successively > (though in any order). > > regards, tom lane That approach will only work if you coalesce your inputs into non-contiguous sets (NCS) first. Overlapping ranges would break it in a hurry. In addition to two coalesce operations, period_except would be calculated 1000x for a pair of 100 element arrays. Original solution, while not short was probably a little more elegant than Tom gave credit for. In a single pass it pulls out only the data points needed to build the resultant NCS without making assumptions that the inputs were coalesced. I think I'll still be able to do a single pass solution for continuous ranges. I just wont be able to do the coalesce operations inline with the set operations. Scott -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: decibel on 22 Dec 2009 18:17
On Dec 15, 2009, at 6:29 PM, Jeff Davis wrote: > On Tue, 2009-12-15 at 18:06 -0600, decibel wrote: >> Now that varlena's don't have an enormous fixed overhead, perhaps it's >> worth looking at using them. Obviously some operations would be >> slower, but for your stated examples of auditing and history, I >> suspect that you're not going to notice the overhead that much. > > For most varvarlena types, you only get stuck with the full alignment > burden if you get unlucky. In this case, we're moving from 16 bytes to > 17, which really means 24 bytes with alignment. Try creating two tables: My thought was that many timestamps don't actually need 16 bytes. Jan 1, 2000 certainly doesn't. So if your dates are close to the PG epoch, you can get away with far fewer than 8 bytes, which means varlena would be a win. *does some math* Actually, we're kinda screwed with microsecond time. Neglecting leap years and what-not, I come up with 8 years as the most you can represent in 6 bytes. The good news is that 7 bytes gets you all the way to 2284 (with uS precision), so we're not actually hurting ourselves on storage until 4284 or so. Not everyone needs uS precision, so it might be worth looking at a varlena-based timestamp. I was actually thinking about storing something like an absolute time and then an interval. That might have been able to compact a lot more if you used some kind of modified varlena (you'd want to store how long both the absolute time and the interval were). But again, we're rather screwed if you use uS precision. 1 byte header + 7 bytes for absolute gets us +/- 2284 years from epoch, but 4 bytes for interval only gives us 4294 seconds at uS precision. Maybe still worth it for those hour-long meetings. But if you switch to second precision, things get a lot more interesting: 1 byte overhead + 3 bytes interval gives you 194 days. 4 bytes of 1 second absolute time gets you epoch +/- 136 years. That means you could represent an entire period in 8 bytes. -- Jim C. Nasby, Database Architect jim(a)nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |