3 ms·

"They would rather spend time improving the optimiser in future versions". OK nice idealistic thinking. Meanwhile on planet Earth, I’m trying to convince manage

"They would rather spend time improving the optimiser in future versions". OK nice idealistic thinking. Meanwhile on planet Earth, I’m trying to convince management that they need to redesign their app that hasn’t been touched in years because of some gradual growth in the data that screwed up PostgreSQL into thinking it should run the queries in a sub-optimal way. Wouldn't re-ANALYZE-ing the tables in question help fix this problem, where the statistics that Postgres has about the table access patterns no longer fit reality?

lobster_johnson 16y ago
That presumes that Postgres will always make good decisions based on up-to-date statistics, which is unfortunately not true. The statistics are just a sample across a subset of your data, collecting things like value frequencies, for a single table. It does not track inter-tabular correlations, which means that it can never truly plan joins optimally because it does not know at the outset how many rows will match any given row in a join. I have hit many cases where a query will use a nested loop and take 5-10 seconds to run where an index scan would have taken just a few milliseconds. Some discussion here: http://archives.postgresql.org/message-id/603c8f070902180908j3ae46774g535d96ece2c90e74@mail.gmail.com http://archives.postgresql.org/message-id/603c8f070902180908...