Re: Slow query but can't see whats wrong

On Tue, Jul 24, 2007 at 10:40:16AM +0200, Henrik Zagerholm wrote:
> I'm using pgsql 8.2.4 and I have this query which is sooo slow but I
> can seem to figure out why.
> It is using the appropriate indexes but there is something wrong with
> the nested loops.
>
> I'm running auto vacuum so the statistics should be up to date. I've
> increase the statistics on most foreign keys.

Have you examined the last_vacuum, last_autovacuum, last_analyze,
and last_autoanalyze columns in pg_stat_user_tables to find out
when the tables were last vacuumed and analyzed?

The estimate on this index scan is a problem:

> -> Index Scan using tbl_file_idx on tbl_file (cost=0.01..8.34 rows=1 width=39) (actual time=0.283..3339.003 rows=25039 loops=1)
> Index Cond: ((lower ((file_name)::text) ~>=~ 'awstats'::character varying) AND (lower ((file_name)::text) ~<~ 'awstatt'::character varying))
> Filter: (lower ((file_name)::text) ~~ 'awstats%'::text)

Is tbl_file_idx an index on the expression lower(file_name)? If
so then I don't think increasing the statistics on tbl_file.file_name
will help, but increasing the statistics on tbl_file_idx.pg_expression_1
might. You'll have to experiment to find a good value.

ALTER TABLE tbl_file_idx ALTER pg_expression_1 SET STATISTICS 100;
ANALYZE tbl_file;

Unfortunately the statistics settings on index expressions don't
survive a pg_dump. Fixing this has been discussed a few times but
I don't think anybody has worked on it. The developers' TODO list
has the following item:

* Allow accurate statistics to be collected on indexes with more
than one column or expression indexes, perhaps using per-index
statistics

--
Michael Fuhr