Re: Statistics use with functions

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 there is such an entry
> and if it looks sane?

What should I be looking for? I don't see anything obvious from this:

modmine-r9=# select attname from pg_stats where tablename = 'geneflankingregion';

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.

modmine-r9=# explain analyse SELECT * FROM geneflankingregion WHERE
LOWER(distance) = '10.0kb' AND LOWER(direction) = 'upstream';
QUERY PLAN
-----------------------------------------------------------------
Bitmap Heap Scan on geneflankingregion
(cost=1197.19..11701.87 rows=45614 width=212)
(actual time=18.336..153.825 rows=45502 loops=1)
Recheck Cond: (lower(distance) = '10.0kb'::text)
Filter: (lower(direction) = 'upstream'::text)
-> Bitmap Index Scan on geneflankingregion__distance_equals
(cost=0.00..1185.78 rows=91134 width=0)
(actual time=16.565..16.565 rows=91004 loops=1)
Index Cond: (lower(distance) = '10.0kb'::text)
Total runtime: 199.282 ms
(6 rows)

Matthew

--
It is better to keep your mouth closed and let people think you are a fool
than to open it and remove all doubt. -- Mark Twain