From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Hannu Krosing <hannu@tm.ee>
Cc: pgsql-performance@postgresql.org
Subject: Re: An unresolved performance problem.
Date: Wed, 7 May 2003 17:09:17 -0200 (GMT+2)
Message-ID: <Pine.LNX.4.44.0305071700530.28446-100000@matrix.gatewaynet.com> (raw)
In-Reply-To: <1052314806.2205.6.camel@fuji.krosing.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com, hannu@tm.ee, pgsql-performance@postgresql.org
Subject: Re: An unresolved performance problem.
In-Reply-To: <Pine.LNX.4.44.0305071700530.28446-100000@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox