> On Sep 23, 2021, at 8:33 AM, Rob Sargent <robjsarg...@gmail.com> wrote: > > On 9/23/21 10:16 AM, Israel Brewster wrote: >>> On Sep 23, 2021, at 4:34 AM, Ryan Booz <r...@timescale.com >>> <mailto:r...@timescale.com>> wrote: >>> >>> Heh, I honestly forgot about the recursive CTE. Certainly worth a try and >>> wouldn't require installing other extensions. >>> >>> This is what depesz is referring to: >>> https://wiki.postgresql.org/wiki/Loose_indexscan >>> <https://wiki.postgresql.org/wiki/Loose_indexscan> >> Thanks for the pointer. Will definitely have to spend some time wrapping my >> brain around that one - I’ve done some CTE’s before, but not recursive that >> I can recall. Should be fun! >> >> If it helps matters any, my structure is currently the following: >> >> table “stations” listing station details (name, latitude, longitude, etc) >> with a smallint primary key “id" >> table “data” with many (many!) data columns (mostly doubles), a station >> column that is a smallint referencing the stations table, and a channel >> column which is a varchar containing the *name* of the channel the data came >> in on. >> >> I will readily accept that this may not be the best structure for the DB. >> For example, perhaps the channel column should be normalized out as has been >> mentioned a couple of times as an option. This would make sense, and would >> certainly simplify this portion of the project. >> >> If I do go with a lookup table updated by a trigger, what would be the best >> option for the query the trigger runs - an upset (ON CONFLICT DO NOTHING)? >> Or a query followed by an insert if needed? The normal case would be that >> the entry already exists (millions of hits vs only the occasional insert >> needed). >> >> > I would look into pre-loading the lookup table (and pre-emptive maintenance). > Add the foreign key, but not the trigger.
That makes sense. Thanks! --- Israel Brewster Software Engineer Alaska Volcano Observatory Geophysical Institute - UAF 2156 Koyukuk Drive Fairbanks AK 99775-7320 Work: 907-474-5172 cell: 907-328-9145
