Re: Slow query but can't see whats wrong

Henrik Zagerholm <henke(at)mac(dot)se> writes:
> I'm using pgsql 8.2.4 and I have this query which is sooo slow but I
> can seem to figure out why.

Seems the core of the problem is the misestimation here:

> " -> 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)"

Looks like either your stats are not up to date on this table, or you
need to increase the stats target for file_name. Anytime a scan row
estimate is off by a factor of 25000, you're going to get a bad plan :-(

regards, tom lane