From: | Israel Brewster <ijbrewster(at)alaska(dot)edu> |
---|---|
To: | Rob Sargent <robjsargent(at)gmail(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Faster distinct query? |
Date: | 2021-09-23 16:45:09 |
Message-ID: | D24DD0D6-B126-4433-85E9-3082A12286BF@alaska.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> On Sep 23, 2021, at 8:33 AM, Rob Sargent <robjsargent(at)gmail(dot)com> wrote:
>
> On 9/23/21 10:16 AM, Israel Brewster wrote:
>>> On Sep 23, 2021, at 4:34 AM, Ryan Booz <ryan(at)timescale(dot)com <mailto:ryan(at)timescale(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | rihad | 2021-09-23 16:51:49 | Re: Currently running queries with actual arguments? |
Previous Message | David G. Johnston | 2021-09-23 16:44:38 | Re: Get COUNT results from two different columns |