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
