The optimizer uses several parameters to optimize queries.
Those parameters have been constant for many years in the past. This was fine for most users. Recently we have seen a couple of systems which were already fully based on SSD disks.
SSDs have a nice advantage over traditional disks: Random disk access is not a nightmare as it used to be on traditional hard drives. On a SSD there is no XX-times difference between random and sequential access anymore. This is an important information when it comes to optimizing a query.
It has turned out to be very beneficial to adjust random_page_cost in postgresql.conf to a value close to 1 (instead of 4 which has been the default for many years) when running PostgreSQL on SSDs.
This is especially important if you happen to work with very large data sets (with small data sets which can be cached, setting random_page_cost to something close to 1 might have been a good idea in the past already anyway).
Recently there have already been some cases where PostgreSQL decided to use too many hash-joins. Adjusting random_page_cost can help here too because index scans and therefore less hashing become more likely (if this is not sufficient, some other stuff has to be adjusted as well).
+43 (0) 2622 93022-0
office@cybertec.at