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