Re: bad query plans for ~ "^string" (and like "string%") (8.3.6)

От: Tom Lane
Тема: Re: bad query plans for ~ "^string" (and like "string%") (8.3.6)
Дата: ,
Msg-id: 9986.1239200889@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: bad query plans for ~ "^string" (and like "string%") (8.3.6)  (Marinos Yannikos)
Список: pgsql-performance

Скрыть дерево обсуждения

bad query plans for ~ "^string" (and like "string%") (8.3.6)  (Marinos Yannikos, )
 Re: bad query plans for ~ "^string" (and like "string%") (8.3.6)  (Marinos Yannikos, )
  Re: bad query plans for ~ "^string" (and like "string%") (8.3.6)  (Tom Lane, )
 Re: bad query plans for ~ "^string" (and like "string%") (8.3.6)  (Robert Haas, )

Marinos Yannikos <> writes:
> Marinos Yannikos wrote:
>> (what exactly does ANALYZE look at for text columns? in our case, about
>> 7% of the rows match the index condition, so it seems that left-anchored
>> regexp/like matches are not evaluated using the gathered
>> most-common-value list at all)

> oops, I think I gave myself the answer there. Of course the
> most-common-value list will not help if all the values that match the
> "bad" index condition exist only once, but have a common prefix...

The costing is really done off the range condition ((e >= 'ean'::text)
AND (e < 'eao'::text) in your example).  I wouldn't think it would have
such a hard time getting a good rowcount estimate for that.  Maybe you
need to bump up the statistics target for that column?

            regards, tom lane


В списке pgsql-performance по дате сообщения:

От: Jeff
Дата:
Сообщение: Re: Best replication solution?
От: Dimitri Fontaine
Дата:
Сообщение: Re: Best replication solution?