On 10/4/21 11:09 AM, Israel Brewster wrote:
On Oct 4, 2021, at 8:46 AM, Rob Sargent <robjsarg...@gmail.com
<mailto:robjsarg...@gmail.com>> wrote:
On Oct 4, 2021, at 10:22 AM, Israel Brewster <ijbrews...@alaska.edu
<mailto:ijbrews...@alaska.edu>> wrote:
Guessing the “sd” is "standard deviation”? Any chance those stddevs
are easily calculable from base data? Could cut your table size in
half (and put those 20 cores to work on the reporting).
Possible - I’d have to dig into that with the script author. I was
just handed an R script (I don’t work with R…) and told here’s the
data it needs, here’s the output we need stored in the DB. I then
spent just enough time with the script to figure out how to hook up
the I/O. The schema is pretty much just a raw dump of the output - I
haven’t really spent any resources figuring out what, exactly, the
data is. Maybe I should :-)
And I wonder if the last three indices are strictly necessary? They
take disc space too.
Not sure. Here’s the output from pg_stat_all_indexes:
volcano_seismology=# select * from pg_stat_all_indexes where
relname='data';
relid | indexrelid | schemaname | relname | indexrelname |
idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+---------+---------------------------+----------+--------------+---------------
19847 | 19869 | public | data | data_pkey |
0 | 0 | 0
19847 | 19873 | public | data |
date_station_channel_idx | 811884 | 12031143199 | 1192412952
19847 | 19875 | public | data | station_channel_epoch_idx
| 8 | 318506 | 318044
19847 | 19876 | public | data | station_data_idx
| 9072 | 9734 | 1235
19847 | 19877 | public | data | station_date_idx
| 721616 | 10927533403 | 10908912092
19847 | 20479 | public | data |
data_station_channel_idx | 47293 | 194422257262 | 6338753379
(6 rows)
so they *have* been used (although not the station_data_idx so much),
but this doesn’t tell me when it was last used, so some of those may
be queries I was experimenting with to see what was fastest, but are
no longer in use. Maybe I should keep an eye on this for a while, see
which values are increasing.
But my bet is you’re headed for partitioning on datetime or perhaps
station.
While datetime partitioning seems to be the most common, I’m not clear
on how that would help here, as the most intensive queries need *all*
the datetimes for a given station, and even the smaller queries would
be getting an arbitrary time range potentially spanning several, if
not all, partitions. Now portioning on station seems to make sense -
there are over 100 of those, and pretty much any query will only deal
with a single station at a time. Perhaps if more partitioning would be
better, portion by both station and channel? The queries that need to
be fastest will only be looking at a single channel of a single station.
I’ll look into this a bit more, maybe try some experimenting while I
still have *relatively* little data. My main hesitation here is that
in the brief look I’ve given partitioning so far, it looks to be a
royal pain to get set up. Any tips for making that easier?
If no queries address multiple stations you could do a table per
station. Doesn't smell good but you have a lot of data and well, speed
kills.
I think the date-station-channel could "take over" for the
station-date. Naturally the latter is chosen if you give just the two
fields, but I would be curious to see how well the former performs given
just its first two fields(when station-date doesn't exist).
