Making a Postgres query 1,000 times faster
mattermost.com
Making a Postgres query 1,000 times faster
It turns out there's a handy feature in PostgreSQL called row constructor comparisons that allows me to compare tuples of columns. That's exactly what we need. Instead of doing CreateAt > ?1 OR (CreateAt = ?1 AND Id > ?2) , we can do ( CreateAt, Id) > (?1, ?2) . And the row constructor comparisons are lexicographical, meaning t... See more
Making a Postgres query 1,000 times faster
- Always use BUFFERS when running an EXPLAIN . It gives some data that may be crucial for the investigation.
- Always, always try to get an Index Cond (called Index range scan in MySQL) instead of a Filter .
- Always, always, always assume PostgreSQL and MySQL will behave differently. Because they do.