Request a Demo
Home   >   mailing lists

Re: Slow query but can't see whats wrong - Mailing list pgsql-general

From Tom Lane
Subject Re: Slow query but can't see whats wrong
Date
Msg-id 8837.1185286232@sss.pgh.pa.us
Whole thread Raw
In response to Slow query but can't see whats wrong  (Henrik Zagerholm <henke@mac.se>)
Responses Re: Slow query but can't see whats wrong
List pgsql-general
Tree view 
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

pgsql-general by date:

Previous
From: Gregory Stark
Date:
Subject: Re: varchar does not work too well with IS NOT NULL partial indexes.
Next
From: "A. Kretschmer"
Date:
Subject: Re: regexp_replace