I'm running a rather complex query and noticed a peculiarity in the usage
of statistics that seriously affects the plan generated. I can extract the
relevant bit:
modmine-r9=# select * from pg_stats where tablename = 'geneflankingregion' AND
attname IN ('distance', 'direction');
schemaname
Matthew Wakeling matt...@flymine.org writes:
When I wrap the fields in the constraints in a LOWER() function, the
planner stops looking at the statistics and makes a wild guess, even
though it is very obvious from just looking what the result should be.
Well, in general the planner can't
Matthew Wakeling matt...@flymine.org writes:
Ah, now I see it - I re-analysed, and found entries in pg_stats where
tablename is the name of the index. Now the query plans correctly and has
the right estimates. So, one needs to analyse AFTER creating indexes -
didn't know that.
Yes, for
On Fri, 8 May 2009, Tom Lane wrote:
In this case, however, you evidently have an index on lower(distance)
which should have caused ANALYZE to gather stats on the values of that
functional expression. It looks like there might be something wrong
there --- can you look into pg_stats and see if
