OK ... so just to clearify... (and pardon my ignorance):
I need to increase the value of 'default_statistics_target' variable and then run VACUUM ANALYZE, right? If so what should I choose for the 'default_statistics_target'?
BTW I only don't do any sub-selection on the View.
I have attached the view in question and the output of: SELECT oid , relname, relpages, reltuples FROM pg_class ORDER BY relpages DESC;
Actually, you only need to alter the statistics for that particular column, not for all columns in the db.
What you want to do is:
ALTER TABLE "ClimateChangeModel40" ALTER COLUMN <whatever the column is> SET STATISTICS 100; VACUUM ANALYZE "ClimateChangeModel40";
The column is just the column that you have the "IX_ClimateId" index on, I don't know which one that is.
The statistics value ranges from 1 - 1000, the default being 10, and for indexed columns you are likely to want somewhere between 100-200.
If you set it to 100 and the planner is still mis-estimating the number of rows, try 200, etc.
The reason to keep the number low is because with a high number the planner has to spend more time planning. But especially for queries like this one, you'd rather the query planner spent a little bit more time planning, and got the right plan.
Description: OpenPGP digital signature