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
I'll add some to the list
1.
can be thousands of times faster than 2.This one is just weird, but I've seen (and was never able to figure out why):
be a lot slower than 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