PostgreSQL query performance bottlenecks
pawelurbanek.com
pawelurbanek.com
Find all the coupons that are expired (90 day expiration):
SELECT * FROM coupon
WHERE created_at + INTERVAL '90 DAY' < now()
You should rewrite the inequality to:
SELECT * FROM coupon
WHERE created_at < now() - INTERVAL '90 DAY'
created_at + INTERVAL '90 DAY' < now() says that for every row take the created_at column, add 90 days to it, and then see if it is less than now()
created_at < now() - INTERVAL '90 DAY' says take now() subtract 90 days, and then see which rows are less than the result.
Atleast, thats my guess. I rarely do any db stuff directly.
I can see where the optimization would come from, when comparing `created_at` with a fixed value `now() - INTERVAL...` (assuming PostgreSQL is smart enough to evaluate it only once and reuse it for all the index comparisons), but the word "overflow" throws me out of the lane.
So write your product, then start monitoring it as you release it to production.
Postgres can track aggregate metrics for queries using the pg_stat_statements extension [1]. You then monitor this periodically to find queries that are slow, then use EXPLAIN ANALYZE [2] to dig in. Make improvements, then reset the statistics for the pg_stat_statements view and wait for a new crop of slow queries to arise.
[1]: https://www.postgresql.org/docs/current/pgstatstatements.htm...
[2]: https://www.postgresql.org/docs/current/using-explain.html
After the data is loaded up, start navigating through the application and it will become evident where improvements need to be made. Tools like Django Debug Toolbar [3] help expose where the bad ORM calls are or also by tailing Postgres log files.
[1] https://github.com/FactoryBoy/factory_boy
The predicate in the 1st statement is actually an expression "created_at + INTERVAL '90 DAY'", it's not column "created_at".
Some databases allow users to create indexes on expression. So if you want to write the 1st statement, you need an index on expression, not a normal index.
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
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
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-def...
Maybe one of these things that are so easy to work around that nobody has prioritized fixing them.
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?
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.
I'll add some to the list
1.
select count(*) from x where not exists (select 1 from y where id = http://x.id)
select count(*) from x where id not in (select id from y)
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
select x from table where id in (select id from cte limit (select count(*) from cte)) and date > $1
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
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.
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.
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?
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.
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)
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.
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.
Totally agree. Just trying to get the community thinking about this stuff. Most business apps I have seen over the years do not have any actual value-add proposition for existing as 2+ separate computer systems.
In both cases, the goal is to save the time spent round-tripping queries and responses to the DB. But with OLAP, you more-than-likely have a large dataset that needs its own dedicated beefy DB to process. So rather than moving the data to the code (with e.g. SQLite), move the code to the data, by moving as much of your business layer as possible into the DB. (Or possibly all of it, ala https://postgrest.org/.)
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