Re: An unresolved performance problem.

Achilleus,

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.

We can probably fix the problem by re-writing the query then; see my previous
example this weekend about overdetermining criteria in order to force the use
of an index.

How about posting the query and the EXPLAIN ANALYZE results?

--
Josh Berkus
Aglio Database Solutions
San Francisco

----- Original Message -----
From: "Achilleus Mantzios" <achill@matrix.gatewaynet.com>
To: <pgsql-sql@postgresql.org>; <pgsql-performance@postgresql.org>;
<pgsql-bugs@postgresql.org>
Sent: Wednesday, May 07, 2003 6:33 PM
Subject: [SQL] An unresolved performance problem.

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.

I seen around a lot of questions are remaining without any reply,
may be in this period the guys like Tom Lane are too busy.

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?
2. If No, i found a bug,
3. If yes then under what conditions??
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??""

Can you give us more informations? Like the table structure, wich kind
of query are you tring to do and so on...

Gaetano

On Wed, 7 May 2003, Achilleus Mantzios wrote:

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?

One of the common examples of this happening was posted a few weeks back.
someone was basically doing this:

delete from table;
analyze table;
insert into table (1,000,000 times);

the problem was that the table had fk constraints to another table, and
the query planner for the inserts (all 1,000,000 of them) assumed it was
inserting into a mostly empty table, and therefore used seq scans instead
of index scans.

It's not a bug, not quite a feature, just a corner case.

Gaetano,

I seen around a lot of questions are remaining without any reply,
may be in this period the guys like Tom Lane are too busy.

Yes, they are. Currently the major contributors are working hard to shape up
both 7.3.3. and 7.4 (and having a long-running discussion about the due date
for 7.4), so they don't have much time for questions.

And for my part, I'm too busy with my paying job to answer all the questions
that get posted, as I suspect are Stephan and Bruno and several other people
who field newbie questions. Given the flood of requests, I have to
prioritize ... and a question which is missing several crucial details (like
a copy of the query!!!) is going to get answered way later than a question
which provides all the needed information -- if at all.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

On Wed, 7 May 2003 17:09:17 -0200 (GMT+2), Achilleus Mantzios
<achill@matrix.gatewaynet.com> wrote:

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.

What we already have is

|dynacom=# EXPLAIN ANALYZE
|SELECT count(*)
| FROM status
| WHERE assettable='vessels' AND appname='ISM PMS' AND apptblname='items' AND status='warn' AND isvalid AND assetidval=57;
|
|QUERY PLAN (fbsd)
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Aggregate (cost=6.02..6.02 rows=1 width=0) (actual time=14.16..14.16 rows=1 loops=1)
| -> Index Scan using status_all on status (cost=0.00..6.02 rows=1 width=0) (actual time=13.09..13.95 rows=75 loops=1)
| Index Cond: ((assettable = 'vessels'::character varying) AND (assetidval = 57) AND (appname = 'ISM PMS'::character varying) AND (apptblname = 'items'::character varying) AND (status = 'warn'::character varying))
| Filter: isvalid
| Total runtime: 14.40 msec
|(5 rows)
|
|QUERY PLAN (lnx)
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Aggregate (cost=1346.56..1346.56 rows=1 width=0) (actual time=244.05..244.05 rows=1 loops=1)
| -> Seq Scan on status (cost=0.00..1345.81 rows=300 width=0) (actual time=0.63..243.93 rows=75 loops=1)
| Filter: ((assettable = 'vessels'::character varying) AND (appname = 'ISM PMS'::character varying) AND (apptblname = 'items'::character varying) AND (status = 'warn'::character varying) AND isvalid AND (assetidval = 57))
| Total runtime: 244.12 msec
|(4 rows)

Now set enable_seqscan to off, and show as the EXPLAIN ANALYSE output.
If the wrong index is used, remove it and rerun the query. Repeat
until you arrive at the correct index and show us these results, too.

Otherwise i get either a seq scan or the wrong index.

| -> Seq Scan on status (cost=0.00..1345.81 rows=300 width=0) (actual time=0.63..243.93 rows=75 loops=1)
^^^^
This seems strange, given that relpages = 562.
What are your config settings? And what hardware is this running on,
especially how much RAM?

Servus
Manfred

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

Hi,

Mendola Gaetano wrote:

...

I seen around a lot of questions are remaining without any reply,
may be in this period the guys like Tom Lane are too busy. ...

And we should remember that this is still free, open source software -
so we have no right to claim _any_ support whatsoever.
So thanks a lot to the PostgreSQL team for all the hard work that has
been and is being put into this software.

// Bernd vdB

Folks,

I suspect that a good number of fairly simple questions aren't being
answered because they're either misdirected or because the poster
hasn't included an "answerable" question (one with sufficient
information to answer).

A suggestion to partially counter this, at least for "slow query" type
questions, has been put forth. If we make it a social norm on the
pg-lists in general to reply off-list to inadequately descriptive "slow
query" questions with a canned message of helpful guidance, we may be
able to up the level of "answerability" of most questions. Ideally,
this would make the questions more transparent, so that more responses
can come from folks other than the major contributors.

Thoughts? Josh and I have placed a draft at
http://techdocs.postgresql.org/guides/SlowQueryPostingGuidelines

I'd specifically like to hear whether people would suggest more of an
emphasis on heuristics for self-help in such a message, what other info
should be included in a "good" slow query question, and people's
thoughts on the netiquette of the whole idea.

Best,

Randall

On Wednesday, May 7, 2003, at 12:57 PM, Josh Berkus wrote:

Randall Lucas <rlucas@tercent.net> writes:

I suspect that a good number of fairly simple questions aren't being
answered because they're either misdirected or because the poster
hasn't included an "answerable" question (one with sufficient
information to answer).

That's always been a problem, but it does seem to have been getting
worse lately.

A suggestion to partially counter this, at least for "slow query" type
questions, has been put forth. If we make it a social norm on the
pg-lists in general to reply off-list to inadequately descriptive "slow
query" questions with a canned message of helpful guidance, we may be
able to up the level of "answerability" of most questions.

The idea of some canned guidance doesn't seem bad, but I'm not sure if
it should be off-list or not. If newbies are corrected off-list then
other newbies who might be lurking, or reading the archives, don't learn
any better and will make the same mistakes in their turn.

How about a standard answer of "you haven't really provided enough info
for us to be helpful, please see this-URL for some hints"? That would
avoid bulking up the list archives with many copies, yet at the same
time the archives would provide evidence of the existence of hints...

Thoughts? Josh and I have placed a draft at
http://techdocs.postgresql.org/guides/SlowQueryPostingGuidelines

Looks good, though I concur with Stephan's comment that the table
schemas aren't optional.

It might be worth including a checklist of the standard kinds of errors
(for example, datatype mismatch preventing index usage). Come to think
of it, that starts to make it look like a FAQ list directed towards
performance issues. Maybe we could make this a subsection of the main
FAQ?

regards, tom lane

I suspect that a good number of fairly simple questions aren't
being answered because they're either misdirected or because the
poster hasn't included an "answerable" question (one with
sufficient information to answer).

That's always been a problem, but it does seem to have been getting
worse lately.

I hate to point this out, but "TIP 4" is getting a bit old and the 6
tips that we throw out to probably about 40K people about 1-200 times
a day have probably reached saturation. Without looking at the
archives, I bet anyone a shot of good scotch that, it's probably
pretty infrequent that people don't kill -9 their postmasters.

Any chance we could flush out the TIPs at the bottom to include,
"VACUUM ANALYZE your database regularly," or "When reporting a
problem, include the output from EXPLAIN [query]," or "ANALYZE tables
before examining the output from an EXPLAIN [query]," or "Visit [url]
for a tutorial on (schemas|triggers|views)."

-sc

--
Sean Chittenden

On Thu, May 08, 2003 at 10:48:52AM -0200, Achilleus Mantzios wrote:

That is, we have a marginal decrease of the total cost
for the index scan when random_page_cost = 1.9,
whereas the "real cost" in the means of total runtime
ranges from 218 msecs (seq scan) to 19 msecs (index scan).
(is it sane?)

You're right that the problem is the poor estimate of the cost of
that selection. I recall you mentioning that you'd expanded the
statistics on the field, but I don't recall to what. I know that
under some circumstances, you _really_ have to increase the stats to
get a meaningful sample.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

On Wed, 7 May 2003, Bernd von den Brincken wrote:

Hi,

Mendola Gaetano wrote:

...

I seen around a lot of questions are remaining without any reply,
may be in this period the guys like Tom Lane are too busy. ...

And we should remember that this is still free, open source software -
so we have no right to claim _any_ support whatsoever.
So thanks a lot to the PostgreSQL team for all the hard work that has
been and is being put into this software.

I fully support your statement.
Also i must add that the confidense of the longterm/power users
is am essential element that applies to all software
(open source included)

// Bernd vdB

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
==================================================================
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

About the unanswered questions problem:

There seems to be a trade off between
describing a problem as minimalistically
as possible so that it gets the chance
of being read (on one hand) and giving
the full details, explain analyze,
pg_class,pg_statistic data (on the other hand),
in order to be more informational.
At the extreme cases: provide a "query slow" post
on one hand and provide the whole pg_dump
on the other.
The problem is that in the first case
"he hasnt given any real info"
and in the second case every one is avoiding
reading 10 pages of data.
I think i must have missed the "golden intersection".

Well now to the point.

The problem was dealt using a hint
from Mr Kenneth Marshall.
Setting random_page_cost = 1.9
resulted in a smaller cost calculation
for the index than the seq scan.

Now the question is:
With random_page_cost = 4 (default)
i get
dynacom=# EXPLAIN ANALYZE select count(*) from status where
assettable='vessels' and appname='ISM PMS' and apptblname='items' and
status='warn' and isvalid and assetidval=57;

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1669.01..1669.01 rows=1 width=0) (actual
time=258.45..258.46 rows=1 loops=1)
-> Seq Scan on status (cost=0.00..1668.62 rows=158 width=0) (actual
time=171.26..258.38 rows=42 loops=1)
Filter: ((assettable = 'vessels'::character varying) AND (appname
= 'ISM PMS'::character varying) AND (apptblname = 'items'::character
varying) AND (status = 'warn'::character varying) AND isvalid AND
(assetidval = 57))
Total runtime: 258.52 msec
(4 rows)

dynacom=#

And with random_page_cost = 1.9, i get
dynacom=# EXPLAIN ANALYZE select count(*) from status where
assettable='vessels' and appname='ISM PMS' and apptblname='items' and
status='warn' and isvalid and assetidval=57;

QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1650.39..1650.39 rows=1 width=0) (actual
time=18.86..18.86 rows=1 loops=1)
-> Index Scan using status_all on status (cost=0.00..1650.04 rows=139
width=0) (actual time=18.26..18.77 rows=42 loops=1)
Index Cond: ((assettable = 'vessels'::character varying) AND
(assetidval = 57) AND (appname = 'ISM PMS'::character varying) AND
(apptblname = 'items'::character varying) AND (status = 'warn'::character
varying))
Filter: isvalid
Total runtime: 18.94 msec
(5 rows)

dynacom=#

That is, we have a marginal decrease of the total cost
for the index scan when random_page_cost = 1.9,
whereas the "real cost" in the means of total runtime
ranges from 218 msecs (seq scan) to 19 msecs (index scan).
(is it sane?)
-----
(returning to the general -performance posting problem)
Altho a FAQ with "please do VACUUM ANALYZE before
posting to the lists" is something usefull in general,
it does not provide enuf info for the users,
at least for "corner cases" (as a fellow pgsql'er
wrote)

I think in order to stop this undesirable phaenomenon
of flooding the lists, the best way is to provide
the actual algorithms that govern the planer/optimiser,
in a form of lets say "advanced documentation".

(If there is such thing, i am sorry but i wasnt
told so by anyone.)

Otherwise there are gonna be unhappy core hackers
(having to examine each case individually)
and of course bad performing systems on the users side.

P.S.

Of course there are newbies in postgresql,
ofcourse there are people who think that
"support" is to be taken for granted,
ofcourse there are people with minimal
programming/hacking skills,
but i think the average "power user"
altho he didnt get the chance to
follow the "hard core" hacking path
in his life, he has a CompScience BSc or MSc,
and can deal with both complicated algoritmic
issues and source code reading,
and morever on the average he likes to
give and receive respect.

(not to mention that he is the person who can
"spread the word" based on strong arguments
and solid ground)

Thats my 20 drachmas.

==================================================================
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

Andrew Sullivan <andrew@libertyrms.info> writes:

On Thu, May 08, 2003 at 10:48:52AM -0200, Achilleus Mantzios wrote:

That is, we have a marginal decrease of the total cost
for the index scan when random_page_cost = 1.9,
whereas the "real cost" in the means of total runtime
ranges from 218 msecs (seq scan) to 19 msecs (index scan).
(is it sane?)

You're right that the problem is the poor estimate of the cost of
that selection.

Are the table and index orders the same? Oliver Elphick pointed out
awhile ago that we're doing a bad job of index order correlation
estimation for multi-column indexes --- the correlation is taken to
be much lower than it should be. But if the correlation is near
zero anyway then this wouldn't explain Achilleus' problem...

regards, tom lane

On Wed, May 07, 2003 at 09:57:49PM -0700, Sean Chittenden wrote:

I hate to point this out, but "TIP 4" is getting a bit old and the 6
tips that we throw out to probably about 40K people about 1-200
times a day have probably reached saturation. Without looking at
the archives, I bet anyone a shot of good scotch that, it's probably
pretty infrequent that people don't kill -9 their postmasters.

Any chance we could flush out the TIPs at the bottom to include,
"VACUUM ANALYZE your database regularly," or "When reporting a
problem, include the output from EXPLAIN [query]," or "ANALYZE
tables before examining the output from an EXPLAIN [query]," or
"Visit [url] for a tutorial on (schemas|triggers|views)."

Better yet, have TIPs that are appropriate to the subscribed
list. -performance has different posting guidelines, things to try,
etc. than does -bugs, than does -sql (than does -hackers, than does
-interfaces, ...).

I don't know how feasible it is to separate them out, but i think it's
worth looking into.

-johnnnnnnnnnnn

On Thu, 8 May 2003, johnnnnnn wrote:

On Wed, May 07, 2003 at 09:57:49PM -0700, Sean Chittenden wrote:

I hate to point this out, but "TIP 4" is getting a bit old and the 6
tips that we throw out to probably about 40K people about 1-200
times a day have probably reached saturation. Without looking at
the archives, I bet anyone a shot of good scotch that, it's probably
pretty infrequent that people don't kill -9 their postmasters.

Any chance we could flush out the TIPs at the bottom to include,
"VACUUM ANALYZE your database regularly," or "When reporting a
problem, include the output from EXPLAIN [query]," or "ANALYZE
tables before examining the output from an EXPLAIN [query]," or
"Visit [url] for a tutorial on (schemas|triggers|views)."

Better yet, have TIPs that are appropriate to the subscribed
list. -performance has different posting guidelines, things to try,
etc. than does -bugs, than does -sql (than does -hackers, than does
-interfaces, ...).

I don't know how feasible it is to separate them out, but i think it's
worth looking into.

Agreed.

Also, some tips might well cross over, like say, vacuum and analyze
regularly. Hmmm. Sounds like a job for a relational database :-)

Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:

If so, how can one find the correlation between the ordering
of a table and a multicolumn index?

Well, it is surely no better than the correlation of the index's
first column --- what is that?

regards, tom lane

On Thu, 8 May 2003, Tom Lane wrote:

Andrew Sullivan <andrew@libertyrms.info> writes:

On Thu, May 08, 2003 at 10:48:52AM -0200, Achilleus Mantzios wrote:

That is, we have a marginal decrease of the total cost
for the index scan when random_page_cost = 1.9,
whereas the "real cost" in the means of total runtime
ranges from 218 msecs (seq scan) to 19 msecs (index scan).
(is it sane?)

You're right that the problem is the poor estimate of the cost of
that selection.

Are the table and index orders the same? Oliver Elphick pointed out
awhile ago that we're doing a bad job of index order correlation
estimation for multi-column indexes --- the correlation is taken to
be much lower than it should be. But if the correlation is near
zero anyway then this wouldn't explain Achilleus' problem...

Please correct me if i am wrong. (i think i probably am)
The correlation value in pg_statistc for a column refers to the
correlation between
the ordering of a table's tuples and the ordering of that column.
(So it plays some role in determining the execution plan
if an index exists on that column. Also CLUSTERing a
single-column index on the table makes reordering
of the table according to that index, that is the ordering of that
column).

Is that correct??

If so, how can one find the correlation between the ordering
of a table and a multicolumn index?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
==================================================================
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

Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:

On Fri, 9 May 2003, Tom Lane wrote:

Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:

If so, how can one find the correlation between the ordering
of a table and a multicolumn index?

Well, it is surely no better than the correlation of the index's
first column --- what is that?

it is 1

Well, that's suggestive, isn't it? What about the remaining columns?

regards, tom lane