4 ms·

Two things that aren't exactly lesser-known, but that I wish more used continuously as part of development: - generate_series(): While not the best to make _re

Two things that aren't exactly lesser-known, but that I wish more used continuously as part of development: - generate_series(): While not the best to make _realistic_ test data for proper load testing, at least it's easy to make a lot of data. If you don't have a few million rows in your tables when you're developing, you probably don't know how things behave, because a full table/seq scan will be fast anyway - and you'll not spot the missing indexes (on e.g. reverse foreign keys, I see missing often enough) - `EXPLAIN` and `EXPLAIN ANALYZE`. Don't save minutes of looking at your query plans during development by spending hours fixing performance problems in production. EXPLAIN all the things. A significant percentage of production issues I've seen (and caused) are easily mitigated by those two. By learning how to read and understand execution plans and how and why they change over time, you'll learn a lot more about databases too. (CTEs/WITH-expressions are life changing too)

dylanz 5y ago
It's worth to note that earlier versions of PostgreSQL didn't include the "AS NOT MATERIALIZED" option when specifying CTE's. In our setup, this had huge hits to performance. If we were on a more recent version of PostgreSQL (I think 11 in this case), or if the query writer just used a sub-query instead of a CTE, we would have been fine.
- brasetvik 5y ago
  Yep! A lot of older posts about CTEs largely advice against them for this reason. Postgres 12 introduced controllable materialization behaviour: https://paquier.xyz/postgresql-2/postgres-12-with-materialize/ https://paquier.xyz/postgresql-2/postgres-12-with-materializ... By default, it'll _not_ materialise unless it's recursive, or if there are >1 other CTEs consuming it. When not materializing, filters may push through the CTEs.
skunkworker 5y ago
Also explain(analyze,buffers) is by far my favorite. It shows you the number of pages loaded from disk or cache. Also to note: EXPLAIN just plans the query, EXPLAIN (ANALYZE) plans and runs the query. Which can take awhile in production.

dylanz 5y ago

It's worth to note that earlier versions of PostgreSQL didn't include the "AS NOT MATERIALIZED" option when specifying CTE's. In our setup, this had huge hits to performance. If we were on a more recent version of PostgreSQL (I think 11 in this case), or if the query writer just used a sub-query instead of a CTE, we would have been fine.

brasetvik 5y ago
Yep! A lot of older posts about CTEs largely advice against them for this reason. Postgres 12 introduced controllable materialization behaviour: https://paquier.xyz/postgresql-2/postgres-12-with-materialize/ https://paquier.xyz/postgresql-2/postgres-12-with-materializ... By default, it'll _not_ materialise unless it's recursive, or if there are >1 other CTEs consuming it. When not materializing, filters may push through the CTEs.

skunkworker 5y ago

Also explain(analyze,buffers) is by far my favorite. It shows you the number of pages loaded from disk or cache. Also to note: EXPLAIN just plans the query, EXPLAIN (ANALYZE) plans and runs the query. Which can take awhile in production.