Re: Slow query but can't see whats wrong

Henrik Zagerholm <henke@mac.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