Tom,

You say that default values for optimizer are efficient.
But this is evident that it doesn't function as expected in my case. 
I have a database of 20 tables, ~1gb total size. My biggest table contains ~270,000 newspaper article from Turkish journals. I am actually working on "fulltext search" program of my own.
There is a problem here.
If that values are not designed for an "ordinary" database application.
I am asking to myself for which case they are designed for.
I didn't write to "hackers" list only for myself, but for everyone of us too.
Please examine the values in my messages in past 2 days, you'll see what i mean.
I am sure default values are erronous.

Regards
Ismail Kizir







Tom,
 
You say that default values for optimizer are 
efficient.
But this is evident that it doesn't function 
as expected in my case. 
I have a database of 20 tables, ~1gb total size. My 
biggest table contains ~270,000 newspaper article from Turkish journals. I am 
actually working on "fulltext search" program of my own.
There is a problem here.
If that values are not designed for an 
"ordinary" database application.
I am asking to myself for which case they are 
designed for.
I didn't write to "hackers" list only for myself, 
but for everyone of us too.
Please examine the values in my messages in past 2 
days, you'll see what i mean.
I am sure default values are erronous.
 
Regards
Ismail Kizir
Tue, 25 May 2004 22:53:02 +0300
"Ismail Kizir" <ikizir@tumgazeteler.com>
Optimizer Bug issue
"Ismail Kizir" <ikizir@tumgazeteler.com> writes:

> I have a database of 20 tables, ~1gb total size. My biggest table contains
> ~270,000 newspaper article from Turkish journals. I am actually working on
> "fulltext search" program of my own.

How much RAM does the machine have? Have you already executed the query and
are repeating it? It's likely the entire data set is cached in RAM. That's not
the long-term average as your data set grows.

The numbers there are appropriate for a database where the data being fetched
cannot all fit in RAM and isn't all pre-cached. There are also scenarios where
the algorithms the optimizer uses to estimate costs don't capture everything.
tweaking the parameters to correct for these problems would cause other
queries to be handled even worse.

If anything the penalty for random disk accesses has increased over the years.
My desktop is about 100 times faster than my 486 router. But the hard drive in
the 486 is only about 10x slower than the hard drive in the desktop. And the
ratio of seek times is probably even less.


There is a parameter effective_cache_size which is supposed to help Postgres
take into account the likelihood that the data will already be in cache. How
exactly does this affect planning and perhaps this parameter needs to have
much more impact on the resultant plans. At least for databases that are small
relative to it.

-- 
greg


25 May 2004 16:36:39 -0400
Greg Stark <gsstark@mit.edu>
Re: Optimizer Bug issue
"Ismail Kizir" <ikizir@tumgazeteler.com> writes:
> I am sure default values are erronous.

Perhaps they are --- for your database, on your hardware.  Or perhaps
the problem is somewhere else (we know that the costing of nestloop
indexscan joins needs work, for instance).  But in any case there's
a reason why these numbers are tweakable.  It doesn't follow that the
defaults should be changed.

			regards, tom lane

Tue, 25 May 2004 19:38:26 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Optimizer Bug issue 

> I am actually 
> working on "fulltext search" program of my own.

No need, use contrib/tsearch2

Chris


Wed, 26 May 2004 09:45:03 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Optimizer Bug issue