Re: An unresolved performance problem.

On 7 May 2003, Hannu Krosing wrote:

> Achilleus Mantzios kirjutas K, 07.05.2003 kell 19:33:
> > Hi, few days ago, i posted some really wierd (at least to me)
> > situation (maybe a potentian bug) to the performance and bugs list
> > and to some core hacker(s) privately as well,
> > and i got no response.
> > Moreover i asked for some feedback
> > in order to understand/fix the problem myself,
> > and again received no response.
> >
> > What i asked was pretty simple:
> > "1. Is it possible that the absense of statistics make the planer produce
> > better plans than in the case of statistcs generated with vacuum
> > analyze/analyze?
>
> Yes, the planner is not perfect, the statistics are just statistics
> (based on a random sample), etc..
>
> This question comes up at least once a month on either [PERFORM] or
> [HACKERS], search the mailing lists to get more thorough
> discussion/explanation.

Ooopss i am i pgsql-performance(at)postgresl(dot)org newbie
(up to now i thought -sql was where all the fun takes place :)

>
> > 2. If No, i found a bug,
>
> Rather a feature ;-p
>
> > 3. If yes then under what conditions??
>
> if
>
> 1) ANALYZE produced skewed data which was worse than default.
>
> or.
>
> 2) some costs are way off for your system (try changing them in
> postgresql.conf)
>

My systems are (rather usual) linux/freebsd and the costs defined (by
default) in postgresql.conf worked well for all queries except
a cursed query on a cursed table.
So i start to believe its an estimation selectivity
problem.

> > 4. If no person knows the answer or no hacker wants to dig into the
> > problem then is there a direction i must follow to understand/fix whats
> > going on myself??""
>
> You can sturt by enabling/disabling various scan methods
>
> psqldb# set enable_seqscan to off;
> SET
>

I have about 10 indexes on this table, and the "correct" one
is used only if i do set enable_seqscan to off; and
drop all other indexes.
Otherwise i get either a seq scan or the wrong index.

>
> and see what happens, then adjust the weights in postgresql.conf or use
> some combination of SETs around critical queries to force the plan you
> like.
>

Also i played with ALTER TABLE set statistics
but could not generate this ideal situation when
no stats where available (right after a load).

The problem is that other queries on this table
need some indexes.
I dunno whata do :(

>
> ------------
> Hannu
>

--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr