11–20 of 329 posts
Postgres doesn't automatically create indexes for foreign keys. This may come as a surprise if you're more familiar with other databases, so pay attention to the implications as it can hurt you in a few ways. I don't know of any database system that does this. In the case for SQL Server, the foreign keys usually get added by the ORM layer (Entity Framework migrations if you're using dotnet).
These are some good tips but I've not hit these performance issues. I work on smaller scale applications. One has been in production since 2012 the database performs very well. I guess I need to get out and work for bigger companies to experience this.
These are some good tips but I've not hit these performance issues. I work on smaller scale applications. One has been in production since 2012 the database performs very well. I guess I need to get out and work for bigger companies to experience this.
In our Postgres DB we have more than 4 TB of data, which I don’t think is too big. We didn’t need any special sauce, no vendors chiming in, only a better understanding of the technology than average Joe.
On the big company part - I have yet to employ more than five developers.
1. Get a better name, PL/pgSQL doesn't exactly roll off the tongue.
2. Get rid of those $$ at the start and end of any PL/pgSQL, it's just verbose and ugly.
PL/pgSQL needs some styling improvements: 1. Get a better name, PL/pgSQL doesn't exactly roll off the tongue. 2. Get rid of those $$ at the start and end of any PL/pgSQL, it's just verbose and ugly.
https://www.postgresql.org/docs/current/sql-syntax-lexical.h...
> 9: Compare indexed columns with IS NOT DISTINCT FROM Does anybody know why this is the case? Usually, an index is not used if the semantics of the index do not match the semantics of the query, so "using" it cannot ever produce correct results. But the workaround presented seems to have identical semantics to IS DISTINCT FROM and still uses the index, so why isn't IS DISTINCT FROM using the index then?
Most of the time the answer to that is: because nobody cared enough or had time enough to implement it
The biggest way I have seen this be true is with fragmented tables/indexes - same data but organized more compactly can change planner behavior.
Article actually touches on another way that can be true - if your `work_mem` is drastically different the planner may prefer not to use an index, for example, and there are similar settings. Even with identical settings PG may choose different plans, it can be a bit tempermental in my experience, so sometimes you have to run the nonperformant query in offpeak production to understand performance.