Date: Wed, 7 May 2003 17:09:17 -0200 (GMT+2)
From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Hannu Krosing <hannu@tm.ee>
Cc: pgsql-performance@postgresql.org
Subject: Re: An unresolved performance problem.
In-Reply-To: <1052314806.2205.6.camel@fuji.krosing.net>
Message-ID: <Pine.LNX.4.44.0305071700530.28446-100000@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

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@postgresl.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@matrix.gatewaynet.com
        mantzios@softlab.ece.ntua.gr