[Books] Good Author List
Permalink
|
Comment on this post
Previous Entries
For comments, see relevant pages (permalinks).
[Computing] Efficient queries with grouping in Postgres
From:
andrew cooke <andrew@...>
Date:
Fri, 29 Sep 2023 12:54:40 -0300
I have finally understood a problem that has been worrying me for a long
time. The key information is here -
https://dba.stackexchange.com/questions/170860
In my case I have tables that look like:
publisher | rowid | other data
------------------------------
puba | 1 | ...
puba | 2 | ...
puba | 3 | ...
pubb | 1 | ...
pubb | 2 | ...
where:
- there is a composite primary key (publisher, rowid)
- the number of distinct publishers is small
- the total number of entries is large
And the particular problem I had was in the query:
select publisher, max(timestamp) from table group by publisher;
where timestamp is part of "other data".
This query was slow and NOT using an index on (timestamp, publisher)
(and the problem persists if the order in the index is swapped).
This was a big surprise, because the index seems perfect for the job -
it has ordered timestamps "grouped by" publisher (in a sense).
The problem is that Postgres does not exploit the knowledge that there
are only a few publishers. So it decides to do a full scan to find
all publishers (simplifying a little).
Since my publishers were actually listed in the publisher table the
following query was much (100x) quicker:
select p.publisher,
(select max(t.timestamp) from table as t
where t.publisher = p.publisher)
from publisher as p;
because it forces Postgres to look at each publisher in turn (instead
of scanning many many duplicates).
An extra detail is that I had to pull a more complex calculation based
on the timestamp into an outer query so that the "max" was clear
enough for the index to be used). The final query was
with x as
(select p.publisher as publisher,
(select max(t.timestamp) as timestamp from table as t
where t.publisher = p.publisher)
from publisher as p)
select publisher, extract (epoch from (now() - timestamp)) from x;
to give the number of seconds since the latest timestamp.
Andrew
Permalink
[Computing] Automatic Wake (Linux)
From:
andrew cooke <andrew@...>
Date:
Wed, 6 Sep 2023 08:04:55 -0300
I want my computer to hibernate (or even to power off), but also to
wake automatically to check for email. To do this I:
1 - configure Gnome to hibernate after some period (eg 1 hour)
2 - add the following cronjob:
30 * * * * sudo rtcwake -u -m no -t `date -d 'tomorrow 00:00' '+\%s'` >& /dev/null
which at half-past every hour sets an alarm to wake the computer
at midnight.
This means that at midnight the computer starts and runs for an hour.
During that time, the alarm is set for the next day, then the computer
hibernates.
In this way the computer cycles every 24 hours (this starts even from
soft power off).
Obviously you could do something similar with smaller intervals if you
wanted, as long as you remember that only one alarm can be set at any
one time.
Andrew
Permalink
[Computing] AWS CDK Aspects in Go
From:
andrew cooke <andrew@...>
Date:
Tue, 22 Aug 2023 11:02:26 -0400
In theory go (golang) has first class support in AWS CDK. In practice
there are often missing examples. Support for Aspects was particularly
poor, so here's a simple example.
This adds a permission boundary to all roles, so you don't have to do
it explicitly (and includes implicit roles). The ARN for the boundary
includes the account name which is read from the environment.
In examples for other languages there's an issue with "instanceof"
operators (or similar) and multiple javascript packages. The same
problem may exist here, so treat with care.
The code is very simple, once it's there...
Andrew
package main
import (
"os"
"github.com/aws/aws-cdk-go/awscdk/v2"
iam "github.com/aws/aws-cdk-go/awscdk/v2/awsiam"
"github.com/aws/aws-sdk-go-v2/aws"
"github.com/aws/constructs-go/constructs/v10"
)
func main() {
app = ...
env = ...
...
awscdk.Aspects_Of(app).Add(&AddPermissionsBoundary{Account: env.Account})
...
app.Synth(nil)
}
type AddPermissionsBoundary struct {
Account *string
}
func (this AddPermissionsBoundary) Visit(node constructs.IConstruct) {
role, ok := node.(iam.CfnRole)
if ok {
role.SetPermissionsBoundary(aws.String("arn:aws:iam::" + *this.Account + ":policy/my-boundary"))
} else {
// not a role - the aspect is applied to all nodes in the tree (visitor pattern)
}
}
Permalink
[Bike] Adidas Gravel Shoes
From:
andrew cooke <andrew@...>
Date:
Fri, 18 Aug 2023 10:53:37 -0400
My old Shimano CX shoes started to separate from the sole (making
unclipping difficult). The Adidas gravel shoes were on offer from
199.990 to 139.990 (60% off) and when I added an offer code I found
via google I got them down to around 103.000 (CLP), which seemed like
a reasonable price, so I bought a pair.
Since they are only available online I neded to guess the size. I
have two pairs of adidas trainers - size 10.5 and 10 US. Since I
thought a cycling shoe should be a fairy snug fit I ordered size 10
and it seems fine. My feet are perhaps slightly wider than normal,
and they are still comfortable (but don't want to be any smaller!).
They are black with white stripes and they look awfully like football
boots. The white stripe is a bit much for a cycling shoe IMHO, but
that was the only choice in stock.
I've just come back from a ride and they seem fine. The elastic strip
to trap the laces could be higher, but works. They're the most
comfortable to walk in cycling shoes that I've owned and the soles are
deep enough to avoid "clicking" on ceramic floors. The sole seems
very rigid (no hot spot, but I have never had problems with that).
They're easy to clip in, but if you don't the "arch" area is very hard
and slippy, so you can easily slide off the pedal.
Some other reviews mentioned that they are not well ventilated. I was
hoping that might mean that they were warm, but they didn't seem any
warmer than my previous shoes. The "sock" is really just a collar
round the ankle and doesn't cause any problems, but I wonder how
stretchy it will stay after a few years of use.
So far they seem fine, but I wouldn't have wanted to pay full price...
Andrew
Permalink
[Covid] Extended SIR Models
From:
andrew cooke <andrew@...>
Date:
Thu, 1 Jun 2023 22:09:34 -0400
I just went to see a talk on mathematical modelling of pandemics. It
was largely a rehash of the SIR model along with simulations (although
I got lost near the end, so may have missed what was new).
Anyway, I got to thinking, and really it seems like the SIR approach
really misses the heart of the poblem. I mean, we know that if
everyone isolates then infection rates will drop. It's fucking
obvious. The reason why it's controversial to actually put that into
practice is because there's a large collective cost.
When you see the word "collective" you start to look at the SIR model
and think how "free market" it is. Lots of independent little people
all doing their own thing.
So I started wondering how you could extend it to include social cost.
The simplest solution I can see is to have a global state that
represents "common good" that is incremented on every interaction,
but that decays over time.
In addition, it seems that you need to add some kind of consequence
for this good becoming lower. The simplest I could think of was that
you randomly kill individuals (so this would need to be SIR + births
and deaths I guess) at a rate connected to how low that gets.
I imagine that if you ran this then for most reasonable death
probability functions you get little happeninn until some threshold,
and then collapse.
So maybe it wouldn't be very interesting.
One extension might be to have two groups (in the same overall
population) with different functions / probabilities (eg rich and
poor). Maybe that would show something more interesting.
Has anything like this been done?
Andrew
Permalink
[Art] York-based Printmaker
From:
andrew cooke <andrew@...>
Date:
Sat, 29 Apr 2023 08:28:49 -0400
https://thecuriousprintmaker.co.uk/
Near home - must visit next time I am back.
Lots of practical (and beautiful) advice in the blog.
Andrew
Permalink