Re: How to inject knowledge into a Postgres database

Oleg Bartunov wrote:
> You could increase statistics or try contrib/tsearch2
>
> Oleg
> On Fri, 7 Oct 2005, han(dot)holl(at)informationslogik(dot)nl wrote:
>
>>
>> Hello,
>>
>> We have a table of people with a date-of-birth and a surname, both
>> indexed.
>> We have queries like this:
>> select report from table where dateofbirth = '1966-12-12' and surname
>> like
>> 'boer%'
>>
>> The planner estimates that in a 1.5M record table 'boer%' will have
>> one record
>> and 1966-12-12 about 40.
>> Unfortunately, names are not evenly distributed, and in some
>> combinations it
>> will have to process many thousands of records. On some older and slower
>> systems, this hurts.
>>
>> Is there a way to tell the planner to always prefer the dateofbirth
>> index ?
>> Alternatively, to inform it about the wildly uneven distribution of
>> surnames
>> (this must be even worse in China).
>>
>> A third possibility would be to rewrite such a query as a nested
>> query: is
>> there a rewrite query - hook (like apaches mod_rewrite) available ? Or is
>> there a somewhere a proxy-server that could do this ?
>>
>> Cheers,
>>
>> Han Holl
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>
>
> Regards,
> Oleg
> _____________________________________________________________
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

You can also "force" it to use the index using ORDER BY dateofbirth.

Cheers,
Ben-Nes Yonatan