Re: Bad estimate on LIKE matching
От | Simon Riggs |
---|---|
Тема | Re: Bad estimate on LIKE matching |
Дата | |
Msg-id | 1137571680.3180.316.camel@localhost.localdomain обсуждение исходный текст |
Ответ на | Bad estimate on LIKE matching ("Magnus Hagander" <mha@sollentuna.net>) |
Ответы |
Re: Bad estimate on LIKE matching
|
Список | pgsql-hackers |
On Tue, 2006-01-17 at 13:53 +0100, Magnus Hagander wrote: > On this table, I do a query like: > SELECT * FROM path WHERE path LIKE 'f:/userdirs/s/super_73/%' > > The estimate for this query is comlpetely off, which I beleive is the > cause for a very bad selection of a query plan when it's used in a big > join (creating nestloops that ends up taking 15+ minutes to complete..). > > > Explain analyze gives: > QUERY PLAN > ------------------------------------------------------------------------ > ----------------------------------------------- > Index Scan using path_name_idx on path (cost=0.00..3.24 rows=1 > width=74) (actual time=0.035..0.442 rows=214 loops=1) > Index Cond: ((path >= 'f:/userdirs/s/super'::text) AND (path < > 'f:/userdirs/s/supes'::text)) > Filter: (path ~~ 'f:/userdirs/s/super_73%'::text) > > > No matter what I search on (when it's very selective), the estimate is > always 1 row, whereas the actual value is at least a couple of hundred. > If I try with say "f:/us", the difference is 377,759 estimated vs > 562,459 returned, which is percentage-wise a lot less, but... > > I have tried upping the statistics target up to 1000, with no changes. > Any way to teach the planner about this? In a recent thread on -perform, I opined that this case could best be solved by using dynamic random block sampling at plan time followed by a direct evaluation of the LIKE against the sample. This would yield a more precise selectivity and lead to the better plan. So it can be improved for the next release. Best Regards, Simon Riggs
В списке pgsql-hackers по дате отправления: