I see one more issue here, and that is that PG isn't rechecking quals for pushed aggregations:

sgr@localhost:splitgraph> SELECT count(*) FROM es.account WHERE firstname ~~ 'Su_an%'
+-------+
| count |
|-------|
| 4     |
+-------+
SELECT 1
Time: 0.019s
sgr@localhost:splitgraph> SELECT count(*) FROM es.account WHERE firstname !~~ 'Su_an%'
+-------+
| count |
|-------|
| 1000  |
+-------+
SELECT 1
Time: 0.013s
sgr@localhost:splitgraph> select count(1) FROM es.account WHERE firstname !~~ 'Su_an%'
+-------+
| count |
|-------|
| 996   |
+-------+
SELECT 1
Time: 0.032s

Ok, I think it's clear to me now after skimming PG source - quals are not meant to be rechecked in case of pushed down aggregations, because if FDW says the pushdown is safe, there's no way to disentangle the returned aggregate result and discard the contributing rows which do not match quals.

Instead, what needs to be done is to reject aggregation pushdown in the plan phase if the qual operator is not supported,here is an example for SQLite FDW.