How to inject knowledge into a Postgres database

From: han(dot)holl(at)informationslogik(dot)nl
To: pgsql-general(at)postgresql(dot)org
Subject: How to inject knowledge into a Postgres database
Date: 2005-10-07 13:21:53
Message-ID: 200510071521.54187.han.holl@informationslogik.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2005-10-07 13:29:37 Re: SELECT FOR SHARE
Previous Message Douglas McNaught 2005-10-07 13:13:36 Re: How to delete Large Object from Database?