5 ms·

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

by latch 5y ago

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 https://www.postgresql.org/docs/14/indexes-expressional.html (2) - https://www.postgresql.org/docs/14/pgstatstatements.html https://www.postgresql.org/docs/14/pgstatstatements.html (3) - https://www.postgresql.org/docs/14/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT https://www.postgresql.org/docs/14/runtime-config-wal.html#G... (4) - https://www.postgresql.org/docs/14/wal-async-commit.html https://www.postgresql.org/docs/14/wal-async-commit.html

OJFord 5y ago
> Isn't a more common and general solution to the first problem to index the expression lower(email) (1). OP mentions & dismisses it in passing before the proposed solutions: > A query searching by a function cannot use a standard index. So you’d need to add a custom index for it to be efficient. But, adding custom indexes on a per-query basis is not a very scalable approach. You might find yourself with multiple redundant indexes that significantly slow down the write operations.
jdreaver 5y ago
> RDS is slow. I've seen select statements take 20 minutes on RDS which take a few seconds on _much_ cheaper baremetal. I'm sure you observed this, but concluding that RDS is slow as a blanket statement is totally wrong. You had to have had different database settings between the two postgres instances to see a difference like that. 3 orders of magnitude performance difference indicates something wrong with the comparison.
- stillicidious 5y ago
  You could easily observe this with a cache-cold query performing lots of random IO. EBS latency is on the order of milliseconds, even cheap baremetal nowadays is microseconds
  - singron 5y ago
    Also rds caps out around 20k IOPS. You can hit 1 million IOPS on a large machine with a bunch of SSDs. Imagine running 50 rds databases instead of 1. It's a huge bummer that EBS is the only durable block storage in aws since the performance is so bad. Has anyone had luck using instance storage? The aws white papers make it seem like you could lose data there for any number of reasons, but the performance is so much better. Maybe a synchronous replica in a different AZ?
    - jdreaver 5y ago
      I've used Aurora and the IO is much better there than on vanilla RDS. Postgres Aurora is basically a fork of postgres with a totally different storage system. Their are some neat re:Invent talks on it if you are interested.
      - singron 5y ago
        We use aurora actually. It's a lot more scalable, but also pretty expensive. The IO layer is multi-tenent, and unfortunately when it goes wrong, you have no idea why and no recourse. I think I've never had a positive experience with AWS support about it either. We've had IO latency go from <2ms to >10ms and completely destroy throughput. Support tells us to try optimizing our queries like we are idiots.
Izkata 5y ago
> This one is just weird, but I've seen (and was never able to figure out why): The LIMIT has me suspicious it has to do with the "correlation" statistic - I know it applies when ORDER BY is involved, but dunno about the IN. This statistic exists for every column in a table, and measures the correlation between the order of the column's data and the table's order on disk. If the correlation is "bad" and you're getting most/all of the table's data, then the query planner will do a full table scan and sort, to avoid lots of random access on disk. If instead the correlation is "good", it'll do an index scan because it won't have to do much jumping around to different parts of the disk. CLUSTER can change the table data order on disk to match one of the indexes on the table. It would have to be run regularly though, since there's no way to insert new rows in the middle, and it locks the table for its whole runtime.

by latch 5y ago

OJFord 5y ago

> Isn't a more common and general solution to the first problem to index the expression lower(email) (1). OP mentions & dismisses it in passing before the proposed solutions: > A query searching by a function cannot use a standard index. So you’d need to add a custom index for it to be efficient. But, adding custom indexes on a per-query basis is not a very scalable approach. You might find yourself with multiple redundant indexes that significantly slow down the write operations.

jdreaver 5y ago

> RDS is slow. I've seen select statements take 20 minutes on RDS which take a few seconds on _much_ cheaper baremetal. I'm sure you observed this, but concluding that RDS is slow as a blanket statement is totally wrong. You had to have had different database settings between the two postgres instances to see a difference like that. 3 orders of magnitude performance difference indicates something wrong with the comparison.

stillicidious 5y ago
You could easily observe this with a cache-cold query performing lots of random IO. EBS latency is on the order of milliseconds, even cheap baremetal nowadays is microseconds
- singron 5y ago
  Also rds caps out around 20k IOPS. You can hit 1 million IOPS on a large machine with a bunch of SSDs. Imagine running 50 rds databases instead of 1. It's a huge bummer that EBS is the only durable block storage in aws since the performance is so bad. Has anyone had luck using instance storage? The aws white papers make it seem like you could lose data there for any number of reasons, but the performance is so much better. Maybe a synchronous replica in a different AZ?
  - jdreaver 5y ago
    I've used Aurora and the IO is much better there than on vanilla RDS. Postgres Aurora is basically a fork of postgres with a totally different storage system. Their are some neat re:Invent talks on it if you are interested.
    - singron 5y ago
      We use aurora actually. It's a lot more scalable, but also pretty expensive. The IO layer is multi-tenent, and unfortunately when it goes wrong, you have no idea why and no recourse. I think I've never had a positive experience with AWS support about it either. We've had IO latency go from <2ms to >10ms and completely destroy throughput. Support tells us to try optimizing our queries like we are idiots.

Izkata 5y ago

> This one is just weird, but I've seen (and was never able to figure out why): The LIMIT has me suspicious it has to do with the "correlation" statistic - I know it applies when ORDER BY is involved, but dunno about the IN. This statistic exists for every column in a table, and measures the correlation between the order of the column's data and the table's order on disk. If the correlation is "bad" and you're getting most/all of the table's data, then the query planner will do a full table scan and sort, to avoid lots of random access on disk. If instead the correlation is "good", it'll do an index scan because it won't have to do much jumping around to different parts of the disk. CLUSTER can change the table data order on disk to match one of the indexes on the table. It would have to be run regularly though, since there's no way to insert new rows in the middle, and it locks the table for its whole runtime.