Thread: 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.
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??""

Pretty straight i think.

Well, i stack on step 1.

It seemed to me that either my question was too naive to deserve some real
investigation (doubtedly), or no one was in a position to comment on
it (doubtedly), or that it is not considered an interesting case (possible),
or that some people move all the mail i send to the lists to /dev/null
(unfortunately possible too).

So Since i really have stuck to postgresql for over 2 years for both
technical and emotional reasons, i would feel much more confident
if i would reach step 2 or greater.

The table i have in question is a critical one in my application
since it monitors important plan maintenance data, and i have
to move on with this problem.

Thanx

P.S. the www (64.49.215.82) server is down for while.

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

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

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

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:

> 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
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>

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

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

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

> > 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, 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 :-)