Isn't a more common and general solution to the first problem to index the expression lower(email) (1).

I'll add some to the list

1.

    select count(*) from x where not exists (select 1 from y where id = http://x.id)

can be thousands of times faster than

   select count(*) from x where id not in (select id from y)

2.

This one is just weird, but I've seen (and was never able to figure out why):

    select x from table where id in (select id from cte) and date > $1

be a lot slower than

    select x from table where id in (select id from cte limit (select count(*) from cte)) and date > $1

3.

RDS is slow. I've seen select statements take 20 minutes on RDS which take a few seconds on _much_ cheaper baremetal.

4.

pg_stat_statements (2) is probably the single most useful thing you can enable/use

5.

If you're ok with potentially losing data on failure, consider setting synchronous_commit = off (3). You'll still be protected from data corruption and (4).

(1) - https://www.postgresql.org/docs/14/indexes-expressional.html

(2) - https://www.postgresql.org/docs/14/pgstatstatements.html

(3) - https://www.postgresql.org/docs/14/runtime-config-wal.html#G...

(4) - https://www.postgresql.org/docs/14/wal-async-commit.html