supersandro2000/supersandro20005: you’re welcome, it also allows me to ping you for review :)
2020-05-08 12955, 2020
shivam-kapila
ruaok: What problem is MsB causing?
2020-05-08 12955, 2020
ruaok
on function was not being found, which was a problem with running MsB on a recent version of PG. that is fixed, but there is still one other strange thing I need to look at. `LOG: incomplete startup packet` appears in the PG logs
2020-05-08 12912, 2020
ruaok
zas: ping
2020-05-08 12920, 2020
zas
pooong
2020-05-08 12950, 2020
ruaok
I'm trying to debug a strange problem with the timescale (postgres) setup.
2020-05-08 12910, 2020
ruaok
how often does a service check try to check to see if the service is alive?
run this `explain SELECT listened_at, recording_msid, data FROM listen WHERE user_name = 'rob' and listened_at < 1580085314 order by listened_at desc limit 50;`
2020-05-08 12918, 2020
shivam-kapila
I ran that for my username. Lemme try for yours
2020-05-08 12933, 2020
ruaok
gonna be different.
2020-05-08 12931, 2020
ruaok
oh, you don't have the full DB. never mind.
2020-05-08 12939, 2020
alastairp
shivam-kapila: cost isn't seconds. it's a "cost", unitless, but can be compared between different explain statements
2020-05-08 12958, 2020
alastairp
you can use `explain analyze` and it will print the query plan _and_ perform the query, and show the runtime at the end
2020-05-08 12926, 2020
shivam-kapila
ruaok: Can I steal your last.fm data :)
2020-05-08 12938, 2020
alastairp
the `cost=0.15..14.83` shows that it could potentially cost anywhere from 0.15 to 14.83 to perform this query. but 14 is an upper estimate
2020-05-08 12940, 2020
shivam-kapila
alastairp: Oh sorry. I didn't know that
2020-05-08 12942, 2020
ruaok
for me that query gives an explain that is (2197 rows) long.
2020-05-08 12954, 2020
alastairp
no apology needed, that's why I was explaining it
2020-05-08 12908, 2020
ruaok
thanks for the alastairp. :)
2020-05-08 12911, 2020
ruaok
that
2020-05-08 12953, 2020
ruaok
the gist of the story is: we need to never make queries that have an open ended interval. -- they must always be closed.
2020-05-08 12943, 2020
alastairp
listened_at < and listened_at > ?
2020-05-08 12950, 2020
ruaok
yes.
2020-05-08 12901, 2020
ruaok
which is a... a bit tricky
2020-05-08 12903, 2020
alastairp
even if it covers the whole time period?
2020-05-08 12936, 2020
ruaok
so, the way timescale differs from PG is how it deals with hypertables.
2020-05-08 12944, 2020
alastairp
way back on the original storage engine didn't we have a max age which was the lastfm launch date?
2020-05-08 12951, 2020
ruaok
hypertables are a new construct in timescale
2020-05-08 12920, 2020
ruaok
and as data gets added, the table is automatically partitioned on time, in this case listened_at.
2020-05-08 12949, 2020
ruaok
and if you run a query near the latest data and don't lower bound your query, it must do an index lookup on ALL table segments.
2020-05-08 12957, 2020
alastairp
yeah, for sure
2020-05-08 12912, 2020
alastairp
just out of interest, do you know how much a hypertable differs from a regular postgres partitioned table?
2020-05-08 12915, 2020
ruaok
but, then how do we pick a lower bound that is safe?
2020-05-08 12935, 2020
shivam-kapila
It get it no. For < it scans down every hypertable chunk. Am I correct?
2020-05-08 12940, 2020
shivam-kapila
now*
2020-05-08 12950, 2020
shivam-kapila
Because there is no lower bound
2020-05-08 12952, 2020
ruaok
not that much, I think. other than that the partitioning is automatic and that you have continuous aggregates.
2020-05-08 12907, 2020
ruaok
shivam-kapila: exactly.
2020-05-08 12925, 2020
alastairp
and the issue with finding the lower bound is that if you want to return 100 items, you have no idea if you have to go back a month or 2 years to get that?
2020-05-08 12934, 2020
ruaok
now, mind you, I am not convinced that this is our only problem why pages load slowly.
2020-05-08 12940, 2020
ruaok
alastairp: that!
2020-05-08 12944, 2020
alastairp
what's the size of a partition?
2020-05-08 12953, 2020
ruaok
user defined.
2020-05-08 12903, 2020
ruaok
for us I think its a few days, maybe a week.
2020-05-08 12906, 2020
alastairp
what's the duration that we've defined for a parition
2020-05-08 12906, 2020
alastairp
right
2020-05-08 12920, 2020
alastairp
and a partition is on the date, not (user, date)
2020-05-08 12934, 2020
ruaok
I believe that is correct.
2020-05-08 12950, 2020
alastairp
we could always do multiple queries
2020-05-08 12958, 2020
ruaok
so, if we query and get less than our LIMIT returned, we can remove or extend the lower bound.
