Search Postgresql Archives

Re: How to inject knowledge into a Postgres database

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Oleg Bartunov wrote:
You could increase statistics or try contrib/tsearch2

    Oleg
On Fri, 7 Oct 2005, han.holl@xxxxxxxxxxxxxxxxxxxx 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@xxxxxxxxxx, 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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux