[bakuninsbart]

Can you point to some good resources on how to write better postgres queries? Or give examples of common pitfalls?

[bbojan]

Start with EXPLAIN ANALYZE then work from there. You can use tools where you paste the output of it and it shows you the data in a more easy to understand format.

[vberg]

I am using https://explain.dalibo.com/ for that exact purpose and it does a great job highlighting the perfs issues.

[Zanfa]

I don't have any other good recommendations other than learning how to read and use EXPLAIN [1]. This should typically be the first tool when you have a slow query.

[1] https://www.postgresql.org/docs/current/sql-explain.html

[Ialdaboth]

An old classic but too many indices can be harmful too.

[branko_d]

https://use-the-index-luke.com/

[lordnacho]

I would guess the most common pitfall is either not having indices or having the wrong kind of index for your query.