Re: How to inject knowledge into a Postgres database

On Thursday 13 October 2005 16:06, Tom Lane wrote:
>
> You'd have to change the source code, but it's a simple tweak in the
> ALTER SET STATISTICS code.
>
I don't think I'd want to do that.

> > If the only penalty is slower analyzing, I don't care: we analyze at
> > night when these system are idle.
>
> You'd be wrong about that --- the planner operations that use the data
> would necessarily be slower, too. I don't have any concrete information
> about how much slower, but I'd be hesitant to raise the figure much
> beyond 1000 ...
>
> However, if you can show you have a real-world case that benefits, I'd
> be willing to think about raising the wired-in limit to 10000 or so.
>
The example I gave earlier in the thread, date_of_birth = 'some-date' and
surname like 'blaa%', was a real life example, but I had to pull it from a
logfile that logs queries longer than 500 ms. It happens two or three times a
day in a laboratory with 50 people querying the database all day.

Estimates for date_of_birth number of rows are quite good (even at the default
stats of 10) but surnames are just too unevenly distributed.

But in 99% of all cases the guess is right, and by making it a nested query I
could improve 1% and worsen 99%.

Cheers,

Han Holl