5 ms·

PostgreSQL query performance bottlenecks

249 points by pawurb 5y ago

wongarsu 5y ago
Considering how easy the workaround for Ordering by NULLS LAST is (asking for ordered non-null values, then for null values) I wonder why postgres doesn't do that optimization internally. At first glance it seems like you could trivially rewrite any ORDER BY x NULLS LAST query that can't use an index into two queries that can use the index, and then UNION them together. Maybe one of these things that are so easy to work around that nobody has prioritized fixing them.
- asah 5y ago
  I had the same thought! Is this almost-always faster? (If not, would there be a workaround if a user didn't want this new behavior? Postgres core team hates new settings...) Would it be hard it would be to add to the optimizer as a query rewrite?
- barrkel 5y ago
  I suspect the latter, but also the limited win. In practice indexes are rarely used for sorting. It's almost always more profitable to use an index for filtering on the first table, and some mix of predicate pushdown (for hash and nested loop joins) and key lookups (for nested loop joins) for joined tables. And if a merge join is applicable, it's probably on PK/FKs and not on your presumably semantic sort order. And most of the time, the set of rows responsive to a query is small enough that not using an index for a final sort isn't a big deal. Where an index is profitable for sorting is when you're doing pagination over a mostly unbounded set of rows, potentially with a predicate that matches on a prefix of the index you're planning to use for the sort. The plans for these queries tend to be fragile, you need to take care when adding extra joins and predicates that they don't cause the optimizer to abandon the index for the sort.
itsthecourier 5y ago
If your data allows so, use BRIN indexes. I have one case with append only data, around 1TB, 250GB date field btree that was converted to a BRIN of around 25MB
laurent123456 5y ago
Why was the title changed? It now seems like a general article about query performance, while it's really just about 5 specific cases.
- OJFord 5y ago
  It's BuzzFeedy, who cares how many are discussed? From the guidelines [0]: > If the title contains a gratuitous number or number + adjective, we'd appreciate it if you'd crop it. E.g. translate "10 Ways To Do X" to "How To Do X," and "14 Amazing Ys" to "Ys." Exception: when the number is meaningful, e.g. "The 5 Platonic Solids." (and I think it makes some attempt to do this automatically) [0] - https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html
throwaway858 5y ago
Another postgresql performance gotcha: Find all the coupons that are expired (90 day expiration): SELECT * FROM coupon WHERE created_at + INTERVAL '90 DAY' < now() This will not use the index on the "created_at" column and will be slow. You should rewrite the inequality to: SELECT * FROM coupon WHERE created_at < now() - INTERVAL '90 DAY' and now the query will be much faster. There are a lot of cases in postgres where simple equivalent algebraic manipulations can completely change the query plan
- mahkoh 5y ago
  They are not equivalent since `created_at + INTERVAL '90 DAY'` can overflow for every single row whereas `now() - INTERVAL '90 DAY'` is a constant for the purpose of the query execution.
  - smt88 5y ago
    Yeah this seems very logical to me. I wouldn't call it a "gotcha".
  - CWuestefeld 5y ago
    Yes - this is a common restriction in any DB I've used, certainly in MS SQL Server. The idea is that your queries need to be "SARGable": https://en.wikipedia.org/wiki/Sargable https://en.wikipedia.org/wiki/Sargable
  - OJFord 5y ago
    But that would never be desirable, so it's just another reason to do the other?
  - magicalhippo 5y ago
    The DB we use (SQLAnywhere) doesn't consider now() a constant either, so no indexes considered just due to that alone #thingsilearnedinproduction
    - Hjfrf 5y ago
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.
EGreg 5y ago
Is anyone here an expert with MySQL, optimization and replication? Please contact me at greg at qbix,com and we’d engage you to help solve a couple issues. Our site is experiencing slowdowns and SHOW PROCESSLIST may hold some clues. We have had 10,000 downloads of our apps worldwide and collect ping statistics in MySQL so that may have something to do with it
bob1029 5y ago
> Execution Time: 0.128 ms This is fantastic. Wouldn't it be nice if that was the end of it and your business logic was running with the data after that exact delay? It doesn't really detract from the value of query optimization (i.e. system load reduction), but I do find it a little contradictory in principle to push for sub-millisecond execution and then decide to send those results to a completely different computer across a network. The biggest bottleneck we ever identified with any database vendor was the network stack sitting between database server and application server. I've got some SQLite-backed business services that can transact an entire user request (which involves many database interactions) and then already be returning a final HTTP response in the amount of time it takes PostgreSQL to complete this one example query.
- i_have_an_idea 5y ago
  And I got queries that dig through 2TB of data and return aggregations in 30 secs. Good luck doing that with SQLite.
  - kijin 5y ago
    Off topic, but never underestimate the read performance of an SQLite database with all of its indexes warm in the page cache. :)
- adwn 5y ago
  > I've got some SQLite-backed business services that can transact an entire user request (which involves many database interactions) and then already be returning a final HTTP response in the amount of time it takes PostgreSQL to complete this one example query. So? My in-memory hashmap can do all that in a fraction of the time it takes SQLite to do its thing. Does that mean that hashmaps are superior to SQLite? SQLite is a different database with a different concept, different goals, different guarantees, different capabilities, and different tradeoffs than PostgreSQL. Choose the best tool for the job. Sometimes it's a hashmap, sometimes it's SQLite, and sometimes it's PostgreSQL.
  - bob1029 5y ago
aarondf 5y ago
I'm not sure if it's as much a bottleneck in Postgres as it is in MySQL, but I've just written a tome[1] on more effective offset/limit pagination by using something called a deferred join. The main problem with offset / limit pagination is that the database is forced to fetch and discard more and more rows as you get deeper into the pages. The trick to getting offset / limit to be faster is to reduce the amount of data that the database has to inspect. Using a "deferred join" defers the expensive data access until after the pagination has been calculated. So instead of select * from contacts order by updated_at desc limit 15 offset 10000 you'd do select * from contacts inner join ( select id from contacts order by updated_at desc limit 15 offset 10000 ) as tmp using(id) order by updated_at desc That way the inner query can use a covering index to pull everything it needs (in some cases) and the expensive selecting is done on fewer rows. This works especially well when you intend to select a lot of data and when you're filtering / sorting on more than just the primary key. [1] https://aaronfrancis.com/2022/efficient-pagination-using-deferred-joins https://aaronfrancis.com/2022/efficient-pagination-using-def...
- tomnipotent 5y ago
  I'm guessing this is due to MySQL using clustered indexes, which require a separate b-tree lookup for each matching row in the secondary index since all you have is the key. A Postgres secondary index actually contains the offset of the slotted page containing the row.

249 points by pawurb 5y ago

Considering how easy the workaround for Ordering by NULLS LAST is (asking for ordered non-null values, then for null values) I wonder why postgres doesn't do that optimization internally. At first glance it seems like you could trivially rewrite any ORDER BY x NULLS LAST query that can't use an index into two queries that can use the index, and then UNION them together. Maybe one of these things that are so easy to work around that nobody has prioritized fixing them.

asah 5y ago
I had the same thought! Is this almost-always faster? (If not, would there be a workaround if a user didn't want this new behavior? Postgres core team hates new settings...) Would it be hard it would be to add to the optimizer as a query rewrite?
barrkel 5y ago
I suspect the latter, but also the limited win. In practice indexes are rarely used for sorting. It's almost always more profitable to use an index for filtering on the first table, and some mix of predicate pushdown (for hash and nested loop joins) and key lookups (for nested loop joins) for joined tables. And if a merge join is applicable, it's probably on PK/FKs and not on your presumably semantic sort order. And most of the time, the set of rows responsive to a query is small enough that not using an index for a final sort isn't a big deal. Where an index is profitable for sorting is when you're doing pagination over a mostly unbounded set of rows, potentially with a predicate that matches on a prefix of the index you're planning to use for the sort. The plans for these queries tend to be fragile, you need to take care when adding extra joins and predicates that they don't cause the optimizer to abandon the index for the sort.

itsthecourier 5y ago

If your data allows so, use BRIN indexes. I have one case with append only data, around 1TB, 250GB date field btree that was converted to a BRIN of around 25MB

laurent123456 5y ago

Why was the title changed? It now seems like a general article about query performance, while it's really just about 5 specific cases.

OJFord 5y ago
It's BuzzFeedy, who cares how many are discussed? From the guidelines [0]: > If the title contains a gratuitous number or number + adjective, we'd appreciate it if you'd crop it. E.g. translate "10 Ways To Do X" to "How To Do X," and "14 Amazing Ys" to "Ys." Exception: when the number is meaningful, e.g. "The 5 Platonic Solids." (and I think it makes some attempt to do this automatically) [0] - https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html

throwaway858 5y ago

Another postgresql performance gotcha: Find all the coupons that are expired (90 day expiration): SELECT * FROM coupon WHERE created_at + INTERVAL '90 DAY' < now() This will not use the index on the "created_at" column and will be slow. You should rewrite the inequality to: SELECT * FROM coupon WHERE created_at < now() - INTERVAL '90 DAY' and now the query will be much faster. There are a lot of cases in postgres where simple equivalent algebraic manipulations can completely change the query plan

mahkoh 5y ago
They are not equivalent since `created_at + INTERVAL '90 DAY'` can overflow for every single row whereas `now() - INTERVAL '90 DAY'` is a constant for the purpose of the query execution.
- smt88 5y ago
  Yeah this seems very logical to me. I wouldn't call it a "gotcha".
- CWuestefeld 5y ago
  Yes - this is a common restriction in any DB I've used, certainly in MS SQL Server. The idea is that your queries need to be "SARGable": https://en.wikipedia.org/wiki/Sargable https://en.wikipedia.org/wiki/Sargable
- OJFord 5y ago
  But that would never be desirable, so it's just another reason to do the other?
- magicalhippo 5y ago
  The DB we use (SQLAnywhere) doesn't consider now() a constant either, so no indexes considered just due to that alone #thingsilearnedinproduction
  - Hjfrf 5y ago

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.

EGreg 5y ago

Is anyone here an expert with MySQL, optimization and replication? Please contact me at greg at qbix,com and we’d engage you to help solve a couple issues. Our site is experiencing slowdowns and SHOW PROCESSLIST may hold some clues. We have had 10,000 downloads of our apps worldwide and collect ping statistics in MySQL so that may have something to do with it

bob1029 5y ago

> Execution Time: 0.128 ms This is fantastic. Wouldn't it be nice if that was the end of it and your business logic was running with the data after that exact delay? It doesn't really detract from the value of query optimization (i.e. system load reduction), but I do find it a little contradictory in principle to push for sub-millisecond execution and then decide to send those results to a completely different computer across a network. The biggest bottleneck we ever identified with any database vendor was the network stack sitting between database server and application server. I've got some SQLite-backed business services that can transact an entire user request (which involves many database interactions) and then already be returning a final HTTP response in the amount of time it takes PostgreSQL to complete this one example query.

i_have_an_idea 5y ago
And I got queries that dig through 2TB of data and return aggregations in 30 secs. Good luck doing that with SQLite.
- kijin 5y ago
  Off topic, but never underestimate the read performance of an SQLite database with all of its indexes warm in the page cache. :)
adwn 5y ago
> I've got some SQLite-backed business services that can transact an entire user request (which involves many database interactions) and then already be returning a final HTTP response in the amount of time it takes PostgreSQL to complete this one example query. So? My in-memory hashmap can do all that in a fraction of the time it takes SQLite to do its thing. Does that mean that hashmaps are superior to SQLite? SQLite is a different database with a different concept, different goals, different guarantees, different capabilities, and different tradeoffs than PostgreSQL. Choose the best tool for the job. Sometimes it's a hashmap, sometimes it's SQLite, and sometimes it's PostgreSQL.
- bob1029 5y ago

aarondf 5y ago

I'm not sure if it's as much a bottleneck in Postgres as it is in MySQL, but I've just written a tome[1] on more effective offset/limit pagination by using something called a deferred join. The main problem with offset / limit pagination is that the database is forced to fetch and discard more and more rows as you get deeper into the pages. The trick to getting offset / limit to be faster is to reduce the amount of data that the database has to inspect. Using a "deferred join" defers the expensive data access until after the pagination has been calculated. So instead of select * from contacts order by updated_at desc limit 15 offset 10000 you'd do select * from contacts inner join ( select id from contacts order by updated_at desc limit 15 offset 10000 ) as tmp using(id) order by updated_at desc That way the inner query can use a covering index to pull everything it needs (in some cases) and the expensive selecting is done on fewer rows. This works especially well when you intend to select a lot of data and when you're filtering / sorting on more than just the primary key. [1] https://aaronfrancis.com/2022/efficient-pagination-using-deferred-joins https://aaronfrancis.com/2022/efficient-pagination-using-def...

tomnipotent 5y ago
I'm guessing this is due to MySQL using clustered indexes, which require a separate b-tree lookup for each matching row in the secondary index since all you have is the key. A Postgres secondary index actually contains the offset of the slotted page containing the row.