Thread: Optimizer Bug issue
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
"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
"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
> I am actually > working on "fulltext search" program of my own. No need, use contrib/tsearch2 Chris