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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
