Обсуждение: Re: [HACKERS] Slow count(*) again...

On 2/1/2011 6:03 PM, Andrew Dunstan wrote:
> Whether or not it's bad application design, it's ubiquitous, and we
> should make it work as best we can, IMNSHO. This often generates
> complaints about Postgres, and if we really plan for world domination
> this needs to be part of it.

There are many other things to fix first. One of them would be optimizer
decisions when a temp table is involved. I would also vote for wait
event interface, tracing and hints, much rather than speeding up
count(*). World domination will not be achieved by speeding up count(*),
it will be achieved by providing overall performance akin to what the
player who has already achieved the world domination. I believe that the
company is called "Oracle Corp." or something like that?

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

Samuel Gendler wrote:
>
>
> Don't listen to him.  He's got an oracle bias.
And bad sinuses, too.
>  Slashdot already announced that NoSQL is actually going to dominate
> the world, so postgres has already lost that battle.  Everything
> postgres devs do now is just an exercise in relational masturbation.
>  Trust me.
>
I knew that there is some entertainment value on this list. Samuel, your
point of view is very..., er, refreshing. Trust me.


--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com

On Tue, Feb 1, 2011 at 6:44 PM, Mladen Gogala <mladen.gogala@vmsinfo.com> wrote:
> On 2/1/2011 6:03 PM, Andrew Dunstan wrote:
>>
>> Whether or not it's bad application design, it's ubiquitous, and we
>> should make it work as best we can, IMNSHO. This often generates
>> complaints about Postgres, and if we really plan for world domination
>> this needs to be part of it.
>
> There are many other things to fix first. One of them would be optimizer
> decisions when a temp table is involved.

It would be pretty hard to make autoanalyze work on such tables
without removing some of the performance benefits of having such
tables in the first place - namely, the local buffer manager.  But you
could ANALYZE them by hand.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas wrote:
> On Tue, Feb 1, 2011
> It would be pretty hard to make autoanalyze work on such tables
> without removing some of the performance benefits of having such
> tables in the first place - namely, the local buffer manager.  But you
> could ANALYZE them by hand.
>
>
Not necessarily autoanalyze, some default rules for the situations when
stats is not there should be put in place,
like the following:
1) If there is a usable index on the temp table - use it.
2) It there isn't a usable index on the temp table and there is a join,
make the temp table the first table
    in the nested loop join.

People are complaining about the optimizer not using the indexes all
over the place, there should be a way to
make the optimizer explicitly prefer the indexes, like was the case with
Oracle's venerable RBO (rules based
optimizer). RBO didn't use statistics, it had a rank of access method
and used the access method with the highest
rank of all available access methods. In practice, it translated into:
if an index exists - use it.


--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

On Wed, Feb 2, 2011 at 12:11 PM, Mladen Gogala
<mladen.gogala@vmsinfo.com> wrote:
> Robert Haas wrote:
>>
>> On Tue, Feb 1, 2011 It would be pretty hard to make autoanalyze work on
>> such tables
>> without removing some of the performance benefits of having such
>> tables in the first place - namely, the local buffer manager.  But you
>> could ANALYZE them by hand.
>>
>>
>
> Not necessarily autoanalyze, some default rules for the situations when
> stats is not there should be put in place,
> like the following:
> 1) If there is a usable index on the temp table - use it.
> 2) It there isn't a usable index on the temp table and there is a join, make
> the temp table the first table
>   in the nested loop join.
>
> People are complaining about the optimizer not using the indexes all over
> the place, there should be a way to
> make the optimizer explicitly prefer the indexes, like was the case with
> Oracle's venerable RBO (rules based
> optimizer). RBO didn't use statistics, it had a rank of access method and
> used the access method with the highest
> rank of all available access methods. In practice, it translated into: if an
> index exists - use it.

However, sometimes using an index results in a HORRIBLE HORRIBLE plan.
I recently encountered the issue myself, and plopping an ANALYZE
$tablename in there, since I was using a temporary table anyway, make
all the difference. The planner switched from an index-based query to
a sequential scan, and a sequential scan was (is) vastly more
efficient in this particular case.

Personally, I'd get rid of autovacuum/autoanalyze support on temporary
tables (they typically have short lives and are often accessed
immediately after creation preventing the auto* stuff from being
useful anyway), *AND* every time I ask I'm always told "make sure
ANALYZE the table before you use it".


--
Jon

On Wed, Feb 2, 2011 at 1:11 PM, Mladen Gogala <mladen.gogala@vmsinfo.com> wrote:
> Not necessarily autoanalyze, some default rules for the situations when
> stats is not there should be put in place,
> like the following:
> 1) If there is a usable index on the temp table - use it.
> 2) It there isn't a usable index on the temp table and there is a join, make
> the temp table the first table
>   in the nested loop join.

The default selectivity estimates ought to make this happen already.

create temporary table foo (a integer, b text);
CREATE TABLE
insert into foo select g, random()::text||random()::text from
generate_series(1, 10000) g;
INSERT 0 10000
alter table foo add primary key (a);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
ALTER TABLE
explain select * from foo where a = 1;
                             QUERY PLAN
---------------------------------------------------------------------
 Index Scan using foo_pkey on foo  (cost=0.00..8.27 rows=1 width=36)
   Index Cond: (a = 1)
(2 rows)

You're going to need to come up with actual examples of situations
that you think can be improved upon if you want to get anywhere here.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, Feb 2, 2011 at 1:19 PM, Jon Nelson <jnelson+pgsql@jamponi.net> wrote:
> However, sometimes using an index results in a HORRIBLE HORRIBLE plan.
> I recently encountered the issue myself, and plopping an ANALYZE
> $tablename in there, since I was using a temporary table anyway, make
> all the difference. The planner switched from an index-based query to
> a sequential scan, and a sequential scan was (is) vastly more
> efficient in this particular case.

Yep...

> Personally, I'd get rid of autovacuum/autoanalyze support on temporary
> tables

We don't have any such support, which I think is the root of Mladen's complaint.

> (they typically have short lives and are often accessed
> immediately after creation preventing the auto* stuff from being
> useful anyway), *AND* every time I ask I'm always told "make sure
> ANALYZE the table before you use it".

Yeah.  Any kind of bulk load into an empty table can be a problem,
even if it's not temporary.  When you load a bunch of data and then
immediately plan a query against it, autoanalyze hasn't had a chance
to do its thing yet, so sometimes you get a lousy plan.  In the case
of temporary tables, this can happen even if there's a delay before
you use the data.  Some sort of fix for this - where the first query
that needs the stats does an analyze first - seems like it could be
quite useful (although it would suck if the transaction that took it
upon itself to do the analyze then rolled back, losing the stats and
forcing the next guy to do it all over again).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Mladen Gogala wrote:
> People are complaining about the optimizer not using the indexes all
> over the place, there should be a way to
> make the optimizer explicitly prefer the indexes, like was the case
> with Oracle's venerable RBO (rules based
> optimizer). RBO didn't use statistics, it had a rank of access method
> and used the access method with the highest
> rank of all available access methods. In practice, it translated into:
> if an index exists - use it.

Given that even Oracle kicked out the RBO a long time ago, I'm not so
sure longing for those good old days will go very far.  I regularly see
queries that were tweaked to always use an index run at 1/10 or less the
speed of a sequential scan against the same data.  The same people
complaining "all over the place" about this topic are also the sort who
write them.  There are two main fallacies at play here that make this
happen:

1) Even if you use an index, PostgreSQL must still retrieve the
associated table data to execute the query in order to execute its
version of MVCC

2) The sort of random I/O done by index lookups can be as much as 50X as
expensive on standard hard drives as sequential, if every block goes to
physical hardware.

If I were to work on improving this area, it would be executing on some
plans a few of us have sketched out for exposing some notion about what
indexes are actually in memory to the optimizer.  There are more obvious
fixes to the specific case of temp tables though.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

Greg Smith wrote:
> Given that even Oracle kicked out the RBO a long time ago, I'm not so
> sure longing for those good old days will go very far.  I regularly see
> queries that were tweaked to always use an index run at 1/10 or less the
> speed of a sequential scan against the same data.  The same people
> complaining "all over the place" about this topic are also the sort who
> write them.  There are two main fallacies at play here that make this
> happen:
>
Oracle just gives an impression that RBO is gone. It's actually still
there, even in 11.2:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options

SQL> alter session set optimizer_mode=rule;

Session altered.

Oracle people were just as puritanical as Postgres  people, if not more
so. However, the huge backlash made them reconsider the decision. RBO is
officially de-supported, obsolete and despised but it is also widely
used, even in the Oracle's own SYS schema. Oracle is having huge
problems with trying to get people to the cost based optimizer, but they
are not yet quite done.

> 1) Even if you use an index, PostgreSQL must still retrieve the
> associated table data to execute the query in order to execute its
> version of MVCC
>
Of course. Nobody contests that.  However, index scans for OLTP are
indispensable. Sequential scans just don't do the trick in some situations.


> 2) The sort of random I/O done by index lookups can be as much as 50X as
> expensive on standard hard drives as sequential, if every block goes to
> physical hardware.
>

Greg, how many questions about queries not using an index have you seen?
There is a reason why people keep asking that. The sheer number of
questions like that on this group should tell you that there is a
problem there.
There must be a relatively simple way of influencing optimizer
decisions. With all due respect, I consider myself smarter than the
optimizer.  I'm 6'4", 235LBS so telling me that you disagree and that I
am more stupid than a computer program,  would not be a smart thing to
do. Please, do not misunderestimate me.

> If I were to work on improving this area, it would be executing on some
> plans a few of us have sketched out for exposing some notion about what
> indexes are actually in memory to the optimizer.  There are more obvious
> fixes to the specific case of temp tables though.
>
>
I've had a run in with a temporary table, that I had to resolve by
disabling hash join and merge join, that really irritated me.

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

Mladen Gogala wrote:
> > 2) The sort of random I/O done by index lookups can be as much as 50X as
> > expensive on standard hard drives as sequential, if every block goes to
> > physical hardware.
> >
>
> Greg, how many questions about queries not using an index have you seen?
> There is a reason why people keep asking that. The sheer number of
> questions like that on this group should tell you that there is a
> problem there.

Very few of those reports found that an index scan was indeed faster ---
they just assumed so but when they actually tested it, they understood.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

On Wed, Feb 02, 2011 at 03:54:26PM -0500, Mladen Gogala wrote:
> Greg Smith wrote:
>> Given that even Oracle kicked out the RBO a long time ago, I'm not so sure
>> longing for those good old days will go very far.  I regularly see queries
>> that were tweaked to always use an index run at 1/10 or less the speed of
>> a sequential scan against the same data.  The same people complaining "all
>> over the place" about this topic are also the sort who write them.  There
>> are two main fallacies at play here that make this happen:
>>
> Oracle just gives an impression that RBO is gone. It's actually still
> there, even in 11.2:
>
> Connected to:
> Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
> With the Partitioning, OLAP, Data Mining and Real Application Testing
> options
>
> SQL> alter session set optimizer_mode=rule;
>
> Session altered.
>
> Oracle people were just as puritanical as Postgres  people, if not more so.
> However, the huge backlash made them reconsider the decision. RBO is
> officially de-supported, obsolete and despised but it is also widely used,
> even in the Oracle's own SYS schema. Oracle is having huge problems with
> trying to get people to the cost based optimizer, but they are not yet
> quite done.
>

This problem in getting people to migrate to the cost-based optimizer
seems to stem from the original use of the rule based optimizer and
the ability to (mis)hint every option in the DB. If I were running
a shop with 100k-1m lines of SQL code with embedded hints, I would
run screaming at the QA required to move to the cost-based system.
In many ways, the RBO itself + hints is hindering the adoption of
the CBO. Are there any stats on the adoption/use of the CBO on new
Oracle users/shops?

>> 1) Even if you use an index, PostgreSQL must still retrieve the associated
>> table data to execute the query in order to execute its version of MVCC
>>
> Of course. Nobody contests that.  However, index scans for OLTP are
> indispensable. Sequential scans just don't do the trick in some situations.
>
>
>> 2) The sort of random I/O done by index lookups can be as much as 50X as
>> expensive on standard hard drives as sequential, if every block goes to
>> physical hardware.
>>
>
> Greg, how many questions about queries not using an index have you seen?
> There is a reason why people keep asking that. The sheer number of
> questions like that on this group should tell you that there is a problem
> there. There must be a relatively simple way of influencing optimizer
> decisions. With all due respect, I consider myself smarter than the
> optimizer.  I'm 6'4", 235LBS so telling me that you disagree and that I am
> more stupid than a computer program,  would not be a smart thing to do.
> Please, do not misunderestimate me.
>

I see them come up regularly. However, there really are not all that
many when you consider how many people are using PostgreSQL. Its
optimizer works quite well. Knowing how hints can be misused, I would
rather have the developers use their resource to improve the optimizer
than spend time on a hint system that would be mis-used over and over
by beginners, with the attendent posts to HACKERS/PERFORM/NOVICE/...
groups. I certainly have had a fun time or two in my limited Oracle
experience tracking down a hint-based performance problem, so it
works both ways.

Regards,
Ken

Kenneth Marshall wrote:
>
>
> I see them come up regularly. However, there really are not all that
> many when you consider how many people are using PostgreSQL. Its
> optimizer works quite well. Knowing how hints can be misused, I would
> rather have the developers use their resource to improve the optimizer
> than spend time on a hint system that would be mis-used over and over
> by beginners, with the attendent posts to HACKERS/PERFORM/NOVICE/...
> groups. I certainly have had a fun time or two in my limited Oracle
> experience tracking down a hint-based performance problem, so it
> works both ways.
>
> Regards,
> Ken
>

Ken, the story is really simple: when a problem with a bad query arises,
the DBA has to make it work, one way or another.  The weapon of choice
are usually hints, but there is also the ability to set the critical
statistic variables to the desired values. If my users are screaming
that the application response time is slow, I cannot afford to wait for
developers to fix the optimizer. I will therefore not use Postgres for
my mission critical applications, as long as there are no hints.

Oracle is expensive, but not as expensive as the downtime. And that's
the bottom line. Yes, hints can cause problems, but the absence of hints
and wait interface can cause even bigger problems. This is not a choice
between good and evil, as in the Nick Cage movies, it is a choice
between evil and lesser evil. I would love to be able  to use Postgres
for some of my mission critical applications. Saving tens of thousands
of dollars would make me a company hero and earn me a hefty bonus, so I
have a personal incentive to do so. Performance is normally not a
problem. If the application is carefully crafted and designed, it will
work more or less the same as Oracle. However, applications sometimes
need maintenance. Ruth from sales wants the IT to start ingesting data
in UTF8 because we have clients in other countries. She also wants us to
track language and countries. Columns have to be added to the tables,
applications have to be changed, foreign keys added, triggers altered,
etc, etc.  What you end up with is usually less than optimal.
Applications have life cycle and they move from being young and sexy to
being an old fart application, just as people do.  Hints are Viagra for
applications. Under the ideal conditions, it is not needed, but once the
app is past certain age....

The other problem is that plans change with the stats, not necessarily
for the better. People clean a large table, Postgres runs auto-vacuum,
stats change and all the plans change, too. If some of the new plans are
unacceptable, there isn't much you can do about it, but to hint it to
the proper plan. Let's not pretend, Postgres does support sort of hints
with the "set enable_<access method>" and random/sequential scan cost.
Also, effective cache size is openly used to trick the optimizer into
believing that there is more memory than there actually is. Hints are
already there, they're just not as elegant as Oracle's solution. If I
set sequential page cost to 4 and random page cost to 1, I have,
effectively, introduced rule based optimizer to Postgres.  I am not sure
why is there such a puritanical resistance to hints on one side and, on
other side, there are means to achieve exactly the same thing.  As my
signature line says, I am a senior Oracle DBA, with quite a bit  of
experience. What I need to approve moving mission critical applications
to Postgres are better monitoring tools and something to help me with
quick and dirty fixes when necessary. I am willing to learn, I got the
company to invest some money and do pilot projects, but I am not
prepared to have my boss saying "we could have fixed the problem, had we
stayed on Oracle".

BTW:
On my last airplane trip, I saw Nick Cage in the "Sorcerer's Apprentice"
and my brain still hurts.


--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

Justin Pitts wrote:
>> With all
>> due respect, I consider myself smarter than the optimizer.  I'm 6'4", 235LBS
>> so telling me that you disagree and that I am more stupid than a computer
>> program,  would not be a smart thing to do. Please, do not misunderestimate
>> me.
>>
>
> I don't see computer programs make thinly veiled threats, especially
> in a public forum.
> I'll do what you claim is not the smart thing and disagree with you.
> You are wrong.
> You are dragging the signal-to-noise ratio of this discussion down.
> You owe Greg an apology.
>
I apologize if that was understood as a threat. It was actually a joke.
I thought that my using of the word "misunderestimate" has made it
abundantly clear. Apparently, G.W. doesn't have as many fans as I have
previously thought. Once again, it was a joke, I humbly apologize if
that was misunderstood.

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

Mladen Gogala wrote:
> Greg, how many questions about queries not using an index have you
> seen? There is a reason why people keep asking that. The sheer number
> of questions like that on this group should tell you that there is a
> problem there. There must be a relatively simple way of influencing
> optimizer decisions.

I think that's not quite the right question.  For every person like
yourself who is making an informed "the optimizer is really picking the
wrong index" request, I think there are more who are asking for that but
are not actually right that it will help.  I think you would agree that
this area is hard to understand, and easy to make mistakes about, yes?
So the right question is "how many questions about queries not using an
index would have actually benefitted from the behavior they asked for?"
That's a much fuzzier and harder to answer question.

I agree that it would be nice to provide a UI for the informed.
Unfortunately, the problem I was pointing out is that doing so could, on
average, make PostgreSQL appear to run worse to people who use it.
Things like which index and merge type are appropriate changes as data
comes in, and some of the plan switches that occur because of that are
the right thing to do--not a mistake on the optimizer's part.  I'm sure
you've seen people put together plan rules for the RBO that worked fine
on small data sets, but were very wrong as production data volume went
up.  That problem should be less likely to happen to a CBO approach.  It
isn't always, of course, but trying to build a RBO-style approach from
scratch now to resolve those cases isn't necessarily the right way to
proceed.

Given limited resources as a development community, it's hard to justify
working on hinting--which has its own complexity to do right--when there
are so many things that I think are more likely to help *everyone* that
could be done instead.  The unfortunate situation we're in, unlike
Oracle, is that there isn't a practically infinite amount of money
available to fund every possible approach here, then see which turn out
to work later after our customers suffer through the bad ones for a while.

> With all due respect, I consider myself smarter than the optimizer.
> I'm 6'4", 235LBS so telling me that you disagree and that I am more
> stupid than a computer program,  would not be a smart thing to do.
> Please, do not misunderestimate me.

I remember when I used to only weigh that much.  You are lucky to be
such a slim little guy!

Oh, I guess I should add, :)

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

Greg Smith wrote:
> Mladen Gogala wrote:
> > Greg, how many questions about queries not using an index have you
> > seen? There is a reason why people keep asking that. The sheer number
> > of questions like that on this group should tell you that there is a
> > problem there. There must be a relatively simple way of influencing
> > optimizer decisions.
>
> I think that's not quite the right question.  For every person like
> yourself who is making an informed "the optimizer is really picking the
> wrong index" request, I think there are more who are asking for that but
> are not actually right that it will help.  I think you would agree that
> this area is hard to understand, and easy to make mistakes about, yes?
> So the right question is "how many questions about queries not using an
> index would have actually benefitted from the behavior they asked for?"
> That's a much fuzzier and harder to answer question.

Agreed.  I created an FAQ entry years ago to explain this point and tell
people how to test it:

    http://wiki.postgresql.org/wiki/FAQ#Why_are_my_queries_slow.3F_Why_don.27t_they_use_my_indexes.3F

Once I added that FAQ we had many fewer email questions about index
choice.

> > With all due respect, I consider myself smarter than the optimizer.
> > I'm 6'4", 235LBS so telling me that you disagree and that I am more
> > stupid than a computer program,  would not be a smart thing to do.
> > Please, do not misunderestimate me.
>
> I remember when I used to only weigh that much.  You are lucky to be
> such a slim little guy!
>
> Oh, I guess I should add, :)

Oh, wow, what a great retort.  :-)

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

On Wed, Feb 2, 2011 at 7:03 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> Given limited resources as a development community, it's hard to justify
> working on hinting--which has its own complexity to do right--when there are
> so many things that I think are more likely to help *everyone* that could be
> done instead.  The unfortunate situation we're in, unlike Oracle, is that
> there isn't a practically infinite amount of money available to fund every
> possible approach here, then see which turn out to work later after our
> customers suffer through the bad ones for a while.

There are actually very few queries where I actually want to force the
planner to use a particular index, which is the sort of thing Oracle
lets you do.  If it's a simple query and
random_page_cost/seq_page_cost are reasonably well adjusted, the
planner's choice is very, very likely to be correct.  If it's a
complex query, the planner has more likelihood of going wrong, but
forcing it to use an index on one table isn't going to help much if
that table is being used on the inner side of a hash join.  You almost
need to be able to force the entire plan into the shape you've chosen,
and that's a lot of work and not terribly robust.  The most common
type of "hard to fix" query problem - by far - is a bad selectivity
estimate.  Being able to hint that would be worth more than any number
of hints about which indexes to use, in my book.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 2/2/2011 7:03 PM, Greg Smith wrote:
> I think that's not quite the right question.  For every person like
> yourself who is making an informed "the optimizer is really picking the
> wrong index" request, I think there are more who are asking for that but
> are not actually right that it will help.  I think you would agree that
> this area is hard to understand, and easy to make mistakes about, yes?
> So the right question is "how many questions about queries not using an
> index would have actually benefitted from the behavior they asked for?"
> That's a much fuzzier and harder to answer question.
>
> I agree that it would be nice to provide a UI for the informed.
> Unfortunately, the problem I was pointing out is that doing so could, on
> average, make PostgreSQL appear to run worse to people who use it.
Greg, I understand your concerns, but let me point out two things:
1)  The basic mechanism is already there. PostgreSQL has a myriad of
ways to actually control the optimizer.  One, completely analogous to
Oracle mechanisms, is to control the cost of sequential vs. random page
scan. The other,  completely analogous to Oracle  hints, is based on the
group of switches for turning on and off various join and access
methods.  This also includes setting join_collapse limit to 1, to force
the desired join order. The third way is to actually make the optimizer
work a lot harder by setting gego_effort to 10 and
default_statistics_target to 1000 or more, which will increase the size
of histograms and increase the time and CPU spent on parsing.  I can
literally force the plan of my choosing on Postgres optimizer. The
mechanisms are already there, I am only pleading for a more elegant version.

2) The guys who may spread Postgres and help it achieve the desired
world domination, discussed here the other day, are database
administrators in the big companies. If you get people from JP Morgan
Chase, Bank of America, Goldman Sachs or Lehman Brothers to start using
Postgres for serious projects, the rest will follow the suit.  People
from some of these companies have already been seen on NYC Postgres
meetings.
Granted, MySQL started on the other end of the spectrum, by being used
for ordering downloaded MP3 collections, but it had found its way into
the corporate server rooms, too. The techies at big companies are the
guys who will or will not make it happen. And these guys are not
beginners.  Appeasing them may actually go a long way.

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com

Mladen Gogala wrote:
> The techies at big companies are the guys who will or will not make it
> happen. And these guys are not beginners.  Appeasing them may actually
> go a long way.

The PostgreSQL community isn't real big on appeasing people if it's at
the expense of robustness or correctness, and this issue falls into that
category.  There are downsides to that, but good things too.  Chasing
after whatever made people happy regardless of its impact on the server
code itself has in my mind contributed to why Oracle is so bloated and
MySQL so buggy, to pick two examples from my favorite horse to whip.
Trying to graft an alternate UI for the stuff that needs to be tweaked
here to do better, one flexible enough to actually handle the complexity
of the job, is going to add some code with a new class of bugs and
continous maintenance headaches.  Being picky about rejecting such
things is part of the reason why the PostgreSQL code has developed a
good reputation.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

02.02.11 20:32, Robert Haas написав(ла):
>
> Yeah.  Any kind of bulk load into an empty table can be a problem,
> even if it's not temporary.  When you load a bunch of data and then
> immediately plan a query against it, autoanalyze hasn't had a chance
> to do its thing yet, so sometimes you get a lousy plan.

May be introducing something like 'AutoAnalyze' threshold will help? I
mean that any insert/update/delete statement that changes more then x%
of table (and no less then y records) must do analyze right after it was
finished.
Defaults like x=50 y=10000 should be quite good as for me.

Best regards, Vitalii Tymchyshyn

On Thu, 3 Feb 2011, Vitalii Tymchyshyn wrote:

> 02.02.11 20:32, Robert Haas ???????(??):
>>
>> Yeah.  Any kind of bulk load into an empty table can be a problem,
>> even if it's not temporary.  When you load a bunch of data and then
>> immediately plan a query against it, autoanalyze hasn't had a chance
>> to do its thing yet, so sometimes you get a lousy plan.
>
> May be introducing something like 'AutoAnalyze' threshold will help? I mean
> that any insert/update/delete statement that changes more then x% of table
> (and no less then y records) must do analyze right after it was finished.
> Defaults like x=50 y=10000 should be quite good as for me.

If I am understanding things correctly, a full Analyze is going over all
the data in the table to figure out patterns.

If this is the case, wouldn't it make sense in the situation where you are
loading an entire table from scratch to run the Analyze as you are
processing the data? If you don't want to slow down the main thread that's
inserting the data, you could copy the data to a second thread and do the
analysis while it's still in RAM rather than having to read it off of disk
afterwords.

this doesn't make sense for updates to existing databases, but the use
case of loading a bunch of data and then querying it right away isn't
_that_ uncommon.

David Lang

On Thu, Feb 03, 2011 at 02:11:58AM -0800, david@lang.hm wrote:
> On Thu, 3 Feb 2011, Vitalii Tymchyshyn wrote:
>
>> 02.02.11 20:32, Robert Haas ???????(??):
>>> Yeah.  Any kind of bulk load into an empty table can be a problem,
>>> even if it's not temporary.  When you load a bunch of data and then
>>> immediately plan a query against it, autoanalyze hasn't had a chance
>>> to do its thing yet, so sometimes you get a lousy plan.
>>
>> May be introducing something like 'AutoAnalyze' threshold will help? I
>> mean that any insert/update/delete statement that changes more then x% of
>> table (and no less then y records) must do analyze right after it was
>> finished.
>> Defaults like x=50 y=10000 should be quite good as for me.
>
> If I am understanding things correctly, a full Analyze is going over all
> the data in the table to figure out patterns.
>
> If this is the case, wouldn't it make sense in the situation where you are
> loading an entire table from scratch to run the Analyze as you are
> processing the data? If you don't want to slow down the main thread that's
> inserting the data, you could copy the data to a second thread and do the
> analysis while it's still in RAM rather than having to read it off of disk
> afterwords.
>
> this doesn't make sense for updates to existing databases, but the use case
> of loading a bunch of data and then querying it right away isn't _that_
> uncommon.
>
> David Lang
>

+1 for in-flight ANALYZE. This would be great for bulk loads of
real tables as well as temp tables.

Cheers,
Ken

On Thu, Feb 3, 2011 at 7:41 AM, Kenneth Marshall <ktm@rice.edu> wrote:
> On Thu, Feb 03, 2011 at 02:11:58AM -0800, david@lang.hm wrote:
>> On Thu, 3 Feb 2011, Vitalii Tymchyshyn wrote:
>>
>>> 02.02.11 20:32, Robert Haas ???????(??):
>>>> Yeah.  Any kind of bulk load into an empty table can be a problem,
>>>> even if it's not temporary.  When you load a bunch of data and then
>>>> immediately plan a query against it, autoanalyze hasn't had a chance
>>>> to do its thing yet, so sometimes you get a lousy plan.
>>>
>>> May be introducing something like 'AutoAnalyze' threshold will help? I
>>> mean that any insert/update/delete statement that changes more then x% of
>>> table (and no less then y records) must do analyze right after it was
>>> finished.
>>> Defaults like x=50 y=10000 should be quite good as for me.
>>
>> If I am understanding things correctly, a full Analyze is going over all
>> the data in the table to figure out patterns.
>>
>> If this is the case, wouldn't it make sense in the situation where you are
>> loading an entire table from scratch to run the Analyze as you are
>> processing the data? If you don't want to slow down the main thread that's
>> inserting the data, you could copy the data to a second thread and do the
>> analysis while it's still in RAM rather than having to read it off of disk
>> afterwords.
>>
>> this doesn't make sense for updates to existing databases, but the use case
>> of loading a bunch of data and then querying it right away isn't _that_
>> uncommon.
>>
>> David Lang
>>
>
> +1 for in-flight ANALYZE. This would be great for bulk loads of
> real tables as well as temp tables.

Yes, please, that would be really nice.




--
Jon

On Thu, Feb 3, 2011 at 4:54 AM, Vitalii Tymchyshyn <tivv00@gmail.com> wrote:
> 02.02.11 20:32, Robert Haas написав(ла):
>>
>> Yeah.  Any kind of bulk load into an empty table can be a problem,
>> even if it's not temporary.  When you load a bunch of data and then
>> immediately plan a query against it, autoanalyze hasn't had a chance
>> to do its thing yet, so sometimes you get a lousy plan.
>
> May be introducing something like 'AutoAnalyze' threshold will help? I mean
> that any insert/update/delete statement that changes more then x% of table
> (and no less then y records) must do analyze right after it was finished.
> Defaults like x=50 y=10000 should be quite good as for me.

That would actually be a pessimization for many real world cases.  Consider:

COPY
COPY
COPY
COPY
COPY
COPY
COPY
COPY
COPY
COPY
COPY
COPY
COPY
SELECT

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Thu, Feb 3, 2011 at 5:11 AM,  <david@lang.hm> wrote:
> If I am understanding things correctly, a full Analyze is going over all the
> data in the table to figure out patterns.

No.  It's going over a small, fixed-size sample which depends on
default_statistics_target but NOT on the table size.  It's really
important to come up with a solution that's not susceptible to running
ANALYZE over and over again, in many cases unnecessarily.

> If this is the case, wouldn't it make sense in the situation where you are
> loading an entire table from scratch to run the Analyze as you are
> processing the data? If you don't want to slow down the main thread that's
> inserting the data, you could copy the data to a second thread and do the
> analysis while it's still in RAM rather than having to read it off of disk
> afterwords.

Well that's basically what autoanalyze is going to do anyway, if the
table is small enough to fit in shared_buffers.  And it's actually
usually BAD if it starts running while you're doing a large bulk load,
because it competes for I/O bandwidth and the buffer cache and slows
things down.  Especially when you're bulk loading for a long time and
it tries to run over and over.  I'd really like to suppress all those
asynchronous ANALYZE operations and instead do ONE synchronous one at
the end, when we try to use the data.

Of course, the devil is in the nontrivial details.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

03.02.11 17:31, Robert Haas написав(ла):
>
>> May be introducing something like 'AutoAnalyze' threshold will help? I mean
>> that any insert/update/delete statement that changes more then x% of table
>> (and no less then y records) must do analyze right after it was finished.
>> Defaults like x=50 y=10000 should be quite good as for me.
> That would actually be a pessimization for many real world cases.  Consider:
>
> COPY
> COPY
> COPY
> COPY
> COPY
> COPY
> COPY
> COPY
> COPY
> COPY
> COPY
> COPY
> COPY
> SELECT
If all the copies are ~ same in size and large this will make it:

COPY
ANALYZE
COPY
ANALYZE
COPY
COPY
ANALYZE
COPY
COPY
COPY
COPY
ANALYZE
COPY
COPY
COPY
COPY
COPY
SELECT

instead of

COPY
COPY
COPY
COPY
COPY
COPY
COPY
COPY
COPY
COPY
COPY
COPY
COPY
ANALYZE (manual, if one is clever enough)
SELECT

So, yes this will add 3 more analyze, but
1) Analyze is pretty cheap comparing to large data loading. I'd say this
would add few percent of burden. And NOT doing analyze manually before
select can raise select costs orders of magnitude.
2) How often in real world a single table is loaded in many COPY
statements? (I don't say it's not often, I really don't know). At least
for restore it is not the case, is not it?
3) default thresholds are things to discuss. You can make x=90 or x=200
(latter will make it run only for massive load/insert operations). You
can even make it disabled by default for people to test. Or enable by
default for temp tables only (and have two sets of thresholds)
4) As most other settings, this threshold can be changed on up to
per-query basis.

P.S. I would also like to have index analyze as part of any create index
process.

Best regards, Vitalii Tymchyshyn

Greg Smith wrote:
> Mladen Gogala wrote:
>
>> The techies at big companies are the guys who will or will not make it
>> happen. And these guys are not beginners.  Appeasing them may actually
>> go a long way.
>>
>
> The PostgreSQL community isn't real big on appeasing people if it's at
> the expense of robustness or correctness, and this issue falls into that
> category.
With all due respect, I don't see how does the issue of hints fall into
this category? As I explained, the mechanisms are already there, they're
just not elegant enough. The verb "appease" doesn't convey the meaning
that I had in mind quite correctly. The phrase "target population" would
have  described what I wanted to say in a much better way .
> There are downsides to that, but good things too.  Chasing
> after whatever made people happy regardless of its impact on the server
> code itself has in my mind contributed to why Oracle is so bloated and
> MySQL so buggy, to pick two examples from my favorite horse to whip.
>
Well, those two databases are also used much more widely than Postgres,
which means that they're doing something better than Postgres.

Hints are not even that complicated to program. The SQL parser should
compile the list of hints into a table and optimizer should check
whether any of the applicable access methods exist in the table. If it
does - use it. If not, ignore it. This looks to me like a philosophical
issue, not a programming issue. Basically, the current Postgres
philosophy can be described like this: if the database was a gas stove,
it would occasionally catch fire. However, bundling a fire extinguisher
with the stove is somehow seen as bad. When the stove catches fire,
users is expected to report the issue and wait for a better stove to be
developed. It is a very rough analogy, but rather accurate one, too.

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

Mladen Gogala wrote:
> Greg Smith wrote:
> > Mladen Gogala wrote:
> >
> >> The techies at big companies are the guys who will or will not make it
> >> happen. And these guys are not beginners.  Appeasing them may actually
> >> go a long way.
> >>
> >
> > The PostgreSQL community isn't real big on appeasing people if it's at
> > the expense of robustness or correctness, and this issue falls into that
> > category.
>
> With all due respect, I don't see how does the issue of hints fall into
> this category? As I explained, the mechanisms are already there, they're
> just not elegant enough. The verb "appease" doesn't convey the meaning
> that I had in mind quite correctly. The phrase "target population" would
> have  described what I wanted to say in a much better way .

The settings are currently there to better model the real world
(random_page_cost), or for testing (enable_seqscan).  They are not there
to force certain plans.  They can be used for that, but that is not
their purpose and they would not have been added if that was their
purpose.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

Mladen Gogala wrote:
> Hints are not even that complicated to program. The SQL parser should
> compile the list of hints into a table and optimizer should check
> whether any of the applicable access methods exist in the table. If it
> does - use it. If not, ignore it. This looks to me like a philosophical
> issue, not a programming issue. Basically, the current Postgres
> philosophy can be described like this: if the database was a gas stove,
> it would occasionally catch fire. However, bundling a fire extinguisher
> with the stove is somehow seen as bad. When the stove catches fire,
> users is expected to report the issue and wait for a better stove to be
> developed. It is a very rough analogy, but rather accurate one, too.

That might be true.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

On 02/03/2011 10:38 AM, Mladen Gogala wrote:

> With all due respect, I don't see how does the issue of hints fall
> into this category?

You have a few good arguments, and if you hadn't said this, it wouldn't
have been so obvious that there was a fundamental philosophical
disconnect. I asked this same question almost ten years ago, and the
answer Tom gave me was more than sufficient.

It all boils down to the database. Hints, whether they're
well-intentioned or not, effectively cover up bugs in the optimizer,
planner, or some other approach the database is using to build its
execution. Your analogy is that PG is a gas stove, so bundle a fire
extinguisher. Well, the devs believe that the stove should be upgraded
to electric or possibly even induction to remove the need for the
extinguisher.

If they left hints in, it would just be one more thing to deprecate as
the original need for the hint was removed. If you really need hints
that badly, EnterpriseDB cobbled the Oracle syntax into the planner, and
it seems to work alright. That doesn't mean it's right, just that it
works. EnterpriseDB will now have to support those query hints forever,
even if the planner gets so advanced they're effectively useless.

> Well, those two databases are also used much more widely than
> Postgres, which means that they're doing something better than
> Postgres.

Please don't make arguments like this. "Better" is such a subjective
evaluation it means nothing. Are Honda Accords "better" than Lamborghini
Gallardos because more people buy Accords? The MySQL/PostgreSQL flame
war is a long and sometimes bitter one, and bringing it up to try and
persuade the devs to "see reason" is just going to backfire.

> Hints are not even that complicated to program.

Then write a contrib module. It's not part of the core DB, and it
probably never will be. This is a *very* old argument. There's literally
nothing you can say, no argument you can bring, that hasn't been heard a
million times in the last decade.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com

______________________________________________

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email

Mladen Gogala <mladen.gogala@vmsinfo.com> writes:
> Hints are not even that complicated to program.

With all due respect, you don't know what you're talking about.

            regards, tom lane

On Thu, Feb 3, 2011 at 11:56 AM, Bruce Momjian <bruce@momjian.us> wrote:
> The settings are currently there to better model the real world
> (random_page_cost), or for testing (enable_seqscan).  They are not there
> to force certain plans.  They can be used for that, but that is not
> their purpose and they would not have been added if that was their
> purpose.

Sure.  But Mladen's point is that this is rather narrow-minded.  I
happen to agree.  We are not building an ivory tower.  We are building
a program that real people will use to solve real problems, and it is
not our job to artificially prevent them from achieving their
objectives so that we remain motivated to improve future versions of
the code.

I don't, however, agree with his contention that this is easy to
implement.  It would be easy to implement something that sucked.  It
would be hard to implement something that actually helped in the cases
where the existing settings aren't already sufficient.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> I don't, however, agree with his contention that this is easy to
> implement.  It would be easy to implement something that sucked.  It
> would be hard to implement something that actually helped in the cases
> where the existing settings aren't already sufficient.

Exactly.  A hint system that actually did more good than harm would be a
very nontrivial project.  IMO such effort is better spent on making the
optimizer smarter.

            regards, tom lane

mladen.gogala@vmsinfo.com (Mladen Gogala) writes:
> Hints are not even that complicated to program. The SQL parser should
> compile the list of hints into a table and optimizer should check
> whether any of the applicable access methods exist in the table. If it
> does - use it. If not, ignore it. This looks to me like a
> philosophical issue, not a programming issue.

It's worth looking back to what has already been elaborated on in the
ToDo.

http://wiki.postgresql.org/wiki/Todo
-----------------------------------
Optimizer hints (not wanted)

Optimizer hints are used to work around problems in the optimizer and
introduce upgrade and maintenance issues. We would rather have the
problems reported and fixed. We have discussed a more sophisticated
system of per-class cost adjustment instead, but a specification remains
to be developed.
-----------------------------------

The complaint is that kludging hints into a particular query attacks the
problem from the wrong direction.

The alternative recommended is to collect some declarative information,
that *won't* be part of the query, that *won't* be processed by the
parser, and that *won't* kludge up the query with information that is
liable to turn into crud over time.

Tom Lane was pretty specific about some kinds of declarative information
that seemed useful:
   <http://archives.postgresql.org/pgsql-hackers/2006-10/msg00663.php>

On Jeapordy, participants are expected to phrase one's answers in the
form of a question, and doing so is rewarded.

Based on the presence of "query hints" on the Not Wanted portion of the
ToDo list, it's pretty clear that participants here are expected to
propose optimizer hints in ways that do NOT involve decorating queries
with crud.  You'll get a vastly friendlier response if you at least make
an attempt to attack the problem in the "declarative information"
fashion.

Perhaps we're all wrong in believing that pushing query optimization
information into application queries by decorating the application with
hints, is the right idea but it's a belief that certainly seems to be
regularly agreed upon by gentle readers.
--
"cbbrowne","@","linuxdatabases.info"
The people's revolutionary committee has  decided that the name "e" is
retrogressive, unmulticious   and reactionary, and  has  been flushed.
Please update your abbrevs.

Mladen Gogala wrote:
> With all due respect, I don't see how does the issue of hints fall
> into this category? As I explained, the mechanisms are already there,
> they're just not elegant enough.

You're making some assumptions about what a more elegant mechanism would
look to develop that are simplifying the actual situation here.  If you
take a survey of everyone who ever works on this area of the code, and
responses to this thread are already approaching a significant
percentage of such people, you'll discover that doing what you want is
more difficult--and very much "not elegant enough" from the perspective
of the code involved--than you think it would be.

It's actually kind of funny...I've run into more than one person who
charged into the PostgreSQL source code with the goal of "I'm going to
add good hinting!"  But it seems like the minute anyone gets enough
understanding of how it fits together to actually do that, they realize
there are just plain better things to be done in there instead.  I used
to be in the same situation you're in--thinking that all it would take
is a better UI for tweaking the existing parameters.  But now that I've
actually done such tweaking for long enough to get a feel for what's
really wrong with the underlying assumptions, I can name 3 better uses
of development resources that I'd rather work on instead.  I mentioned
incorporating cache visibility already, Robert has talked about
improvements to the sensitivity estimates, and the third one is
improving pooling of work_mem so individual clients can get more of it
safely.

> Well, those two databases are also used much more widely than
> Postgres, which means that they're doing something better than Postgres.

"Starting earlier" is the only "better" here.  Obviously Oracle got a
much earlier start than either open-source database.  The real
divergence in MySQL adoption relative to PostgreSQL was when they
released a Windows port in January of 1998.  PostgreSQL didn't really
match that with a fully native port until January of 2005.

Check out
http://www.indeed.com/jobtrends?q=postgres%2C+mysql%2C+oracle&relative=1&relative=1
if you want to see the real story here.  Oracle has a large installed
base, but it's considered a troublesome legacy product being replaced
whenever possible now in every place I visit.  Obviously my view of the
world as seen through my client feedback is skewed a bit toward
PostgreSQL adoption.  But you would be hard pressed to support any view
that suggests Oracle usage is anything other than flat or decreasing at
this point.  When usage of one product is growing at an expontential
rate and the other is not growing at all, eventually the market share
curves always cross too.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

Chris Browne wrote:
> It's worth looking back to what has already been elaborated on in the
> ToDo.
>

And that precisely is what I am trying to contest.


--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

On Thu, Feb 03, 2011 at 12:44:23PM -0500, Chris Browne wrote:
> mladen.gogala@vmsinfo.com (Mladen Gogala) writes:
> > Hints are not even that complicated to program. The SQL parser should
> > compile the list of hints into a table and optimizer should check
> > whether any of the applicable access methods exist in the table. If it
> > does - use it. If not, ignore it. This looks to me like a
> > philosophical issue, not a programming issue.
>
> It's worth looking back to what has already been elaborated on in the
> ToDo.
>
> http://wiki.postgresql.org/wiki/Todo
> -----------------------------------
> Optimizer hints (not wanted)
>
> Optimizer hints are used to work around problems in the optimizer and
> introduce upgrade and maintenance issues. We would rather have the
> problems reported and fixed. We have discussed a more sophisticated
> system of per-class cost adjustment instead, but a specification remains
> to be developed.

And as to the 'wait around for a new version to fix that': there are
constantly excellent examples of exactly this happening, all the time
with PostgreSQL - most recent example I've seen -
http://archives.postgresql.org/pgsql-performance/2011-01/msg00337.php

The wait often isn't long, at all.

Ross
--
Ross Reedstrom, Ph.D.                                 reedstrm@rice.edu
Systems Engineer & Admin, Research Scientist        phone: 713-348-6166
Connexions                  http://cnx.org            fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E  F888 D3AE 810E 88F0 BEDE

On Thu, 3 Feb 2011, Robert Haas wrote:

> On Thu, Feb 3, 2011 at 5:11 AM,  <david@lang.hm> wrote:
>> If I am understanding things correctly, a full Analyze is going over all the
>> data in the table to figure out patterns.
>
> No.  It's going over a small, fixed-size sample which depends on
> default_statistics_target but NOT on the table size.  It's really
> important to come up with a solution that's not susceptible to running
> ANALYZE over and over again, in many cases unnecessarily.
>
>> If this is the case, wouldn't it make sense in the situation where you are
>> loading an entire table from scratch to run the Analyze as you are
>> processing the data? If you don't want to slow down the main thread that's
>> inserting the data, you could copy the data to a second thread and do the
>> analysis while it's still in RAM rather than having to read it off of disk
>> afterwords.
>
> Well that's basically what autoanalyze is going to do anyway, if the
> table is small enough to fit in shared_buffers.  And it's actually
> usually BAD if it starts running while you're doing a large bulk load,
> because it competes for I/O bandwidth and the buffer cache and slows
> things down.  Especially when you're bulk loading for a long time and
> it tries to run over and over.  I'd really like to suppress all those
> asynchronous ANALYZE operations and instead do ONE synchronous one at
> the end, when we try to use the data.

If the table is not large enough to fit in ram, then it will compete for
I/O, and the user will have to wait.

what I'm proposing is that as the records are created, the process doing
the creation makes copies of the records (either all of them, or some of
them if not all are needed for the analysis, possibly via shareing memory
with the analysis process), this would be synchronous with the load, not
asynchronous.

this would take zero I/O bandwidth, it would take up some ram, memory
bandwidth, and cpu time, but a load of a large table like this is I/O
contrained.

it would not make sense for this to be the default, but as an option it
should save a significant amount of time.

I am making the assumption that an Analyze run only has to go over the
data once (a seqential scan of the table if it's >> ram for example) and
gathers stats as it goes.

with the current code, this is a completely separate process that knows
nothing about the load, so if you kick it off when you start the load, it
makes a pass over the table (competing for I/O), finishes, you continue to
update the table, so it makes another pass, etc. As you say, this is a bad
thing to do. I am saying to have an option that ties the two togeather,
essentially making the data feed into the Analyze run be a fork of the
data comeing out of the insert run going to disk. So the Analyze run
doesn't do any I/O and isn't going to complete until the insert is
complete. At which time it will have seen one copy of the entire table.

David Lang

Shaun Thomas wrote:
> On 02/03/2011 10:38 AM, Mladen Gogala wrote:
>
>
> It all boils down to the database. Hints, whether they're
> well-intentioned or not, effectively cover up bugs in the optimizer,
> planner, or some other approach the database is using to build its
> execution.
Hints don't cover up bugs, they simply make it possible for the user to
circumvent the bugs and keep the users happy. As I hinted before, this
is actually a purist argument which was made by someone who has never
had to support a massive production database with many users for living.
> Your analogy is that PG is a gas stove, so bundle a fire
> extinguisher. Well, the devs believe that the stove should be upgraded
> to electric or possibly even induction to remove the need for the
> extinguisher.
>
In the meantime, the fire is burning. What should the hapless owner of
the database application do in the meantime? Tell the users that it will
be better in the next version? As I've said before: hints are make it or
break it point. Without hints, I cannot consider Postgres for the
mission critical projects. I am managing big databases for living and I
flatter myself that after more than two decades of doing it, I am not
too bad at it.

> If they left hints in, it would just be one more thing to deprecate as
> the original need for the hint was removed. If you really need hints
> that badly, EnterpriseDB cobbled the Oracle syntax into the planner, and
> it seems to work alright. That doesn't mean it's right, just that it
> works. EnterpriseDB will now have to support those query hints forever,
> even if the planner gets so advanced they're effectively useless.
>

I don't foresee that to happen in my lifetime. And I plan to go on for
quite a while. There will always be optimizer bugs, users will be
smarter and know more about their data than computer programs in
foreseeable future. What this attitude boils down to is that developers
don't trust their users enough to give them control of the execution
path. I profoundly disagree with that type of philosophy. DB2 also has
hints: http://tinyurl.com/48fv7w7
So does SQL Server:
http://www.sql-server-performance.com/tips/hints_general_p1.aspx
Finally, even the Postgres greatest  open source competitor MySQL
supports hints: http://dev.mysql.com/doc/refman/5.0/en/index-hints.html

I must say that this purist attitude is extremely surprising to me. All
the major DB vendors support optimizer hints, yet in the Postgres
community, they are considered bad with almost religious fervor.
Postgres community is quite unique with the fatwa against hints.

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

mladen.gogala@vmsinfo.com (Mladen Gogala) writes:
> I must say that this purist attitude is extremely surprising to
> me. All the major DB vendors support optimizer hints, yet in the
> Postgres community, they are considered bad with almost religious
> fervor.
> Postgres community is quite unique with the fatwa against hints.

Well, the community declines to add hints until there is actual
consensus on a good way to add hints.

Nobody has ever proposed a way to add hints where consensus was arrived
at that the way was good, so...
--
http://linuxfinances.info/info/nonrdbms.html
Rules of the Evil Overlord #192.  "If I appoint someone as my consort,
I will  not subsequently inform  her that she  is being replaced  by a
younger, more attractive woman.  <http://www.eviloverlord.com/>

Mladen Gogala <mladen.gogala@vmsinfo.com> wrote:

> In the meantime, the fire is burning. What should the hapless
> owner of the database application do in the meantime? Tell the
> users that it will be better in the next version? As I've said
> before: hints are make it or break it point. Without hints, I
> cannot consider Postgres for the mission critical projects. I am
> managing big databases for living and I flatter myself that after
> more than two decades of doing it, I am not too bad at it.

Well, I've been at it since 1972, and I'm OK with the current
situation because I push hard for *testing* in advance of production
deployment.  So I generally discover that leaving a pan of grease on
maximum flame unattended is a bad idea in the test lab, where no
serious damage is done.  Then I take steps to ensure that this
doesn't happen in the user world.

We've got about 100 production databases, some at 2TB and growing,
and 100 development, testing, and staging databases.  About 3,000
directly connected users and millions of web hits per day generating
tens of millions of queries.  Lots of fun replication and automated
interfaces to business partners -- DOT, county sheriffs, local
police agencies, district attorneys, public defenders offices,
Department of Revenue (for tax intercept collections), Department of
Justice, etc.  (That was really just the tip of the iceberg.)

Almost all of this was converted inside of a year with minimal fuss
and only a one user complaint that I can recall.  Most users
described it as a "non-event", with the only visible difference
being that applications were "snappier" than under the commercial
database product.  One type of query was slow in Milwaukee County
(our largest).  We tuned seq_page_cost and random_page_cost until
all queries were running with good plans.  It did not require any
down time to sort this out and fix it -- same day turnaround.  This
is not a matter of hinting; it's a matter of creating a cost model
for the planner which matches reality.  (We don't set this or any
other "hint" per query, we tune the model.)  When the cost estimates
mirror reality, good plans are chosen.

-Kevin

On 02/03/2011 03:01 PM, Mladen Gogala wrote:

> As I hinted before, this is actually a purist argument which was made
> by someone who has never had to support a massive production database
> with many users for living.

Our database handles 9000 transactions per second and over 200-million
transactions per day just fine, thanks. It may not be a "real database"
in your world, but it's real enough for us.

> I must say that this purist attitude is extremely surprising to me.
> All the major DB vendors support optimizer hints, yet in the
> Postgres community, they are considered bad with almost religious
> fervor. Postgres community is quite unique with the fatwa against
> hints.

You missed the argument. The community, or at least the devs, see hints
as an ugly hack. Do I agree? Not completely, but I can definitely
understand the perspective. Saying every other "vendor" has hints is
really just admitting every other vendor has a crappy optimizer. Is that
something to be proud of?

In almost every single case I've seen a query with bad performance, it's
the fault of the author or the DBA. Not enough where clauses; not paying
attention to cardinality or selectivity; inappropriate or misapplied
indexes; insufficient table statistics... the list of worse grievances
out there is endless.

And here's something I never saw you consider: hints making performance
worse. Sure, for now, forcing a sequence scan or forcing it to use
indexes on a specific table is faster for some specific edge-case. But
hints are like most code, and tend to miss frequent refactor. As the
optimizer improves, hints likely won't, meaning code is likely to be
slower than if the hints didn't exist. This of course ignores the
contents of a table are likely to evolve or grow in volume, which can
also drastically alter the path the optimizer would choose, but can't
because a hint is forcing it to take a specific path.

Want to remove a reverse index scan? Reindex with DESC on the column
being reversed. That was added in 8.3. Getting too many calls for nested
loops when a merge or hash would be faster? Increase the statistics
target for the column causing the problems and re-analyze. Find an
actual bug in the optimizer? Tell the devs and they'll fix it. Just stay
current, and you get all those benefits. This is true for any database;
bugs get fixed, things get faster and more secure.

Or like I said, if you really need hints that badly, use EnterpriseDB
instead. It's basically completely Oracle-compatible at this point. But
pestering the PostgreSQL dev community about how inferior they are, and
how they're doing it wrong, and how they're just another vendor making a
database product that can't support massive production databases, is
doing nothing but ensuring they'll ignore you. Flies, honey, vinegar, etc.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com

______________________________________________

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email

Chris Browne wrote:
> Well, the community declines to add hints until there is actual
> consensus on a good way to add hints.
>
OK. That's another matter entirely.   Who should make that decision? Is
there a committee or a person who would be capable of making that decision?

> Nobody has ever proposed a way to add hints where consensus was arrived
> at that the way was good, so...
>

So, I will have to go back on my decision to use Postgres and
re-consider MySQL? I will rather throw away the effort invested in
studying Postgres than to risk an unfixable application downtime.  I am
not sure about the world domination thing, though. Optimizer hints are a
big feature that everybody else has and Postgres does not have because
of religious reasons.

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

On 04/02/11 10:01, Mladen Gogala wrote:
> In the meantime, the fire is burning. What should the hapless owner of
> the database application do in the meantime? Tell the users that it
> will be better in the next version? As I've said before: hints are
> make it or break it point. Without hints, I cannot consider Postgres
> for the mission critical projects. I am managing big databases for
> living and I flatter myself that after more than two decades of doing
> it, I am not too bad at it.

This is somewhat of a straw man argument. This sort of query that the
optimizer does badly usually gets noticed during the test cycle i.e
before production, so there is some lead time to get a fix into the
code, or add/subtract indexes/redesign the query concerned.

The cases I've seen in production typically involve "outgrowing"
optimizer parameter settings: (e.g work_mem, effective_cache_size) as
the application dataset gets bigger over time. I would note that this is
*more* likely to happen with hints, as they lobotomize the optimizer so
it *cannot* react to dataset size or distribution changes.

regards

Mark

Shaun Thomas wrote:
> You missed the argument. The community, or at least the devs, see hints
> as an ugly hack. Do I agree? Not completely, but I can definitely
> understand the perspective. Saying every other "vendor" has hints is
> really just admitting every other vendor has a crappy optimizer. Is that
> something to be proud of?
>
This is funny? Everybody else has a crappy optimizer? That's a funny way
of looking at the fact that every other major database supports hints. I
would be tempted to call that a major missing feature, but the statement
that everybody else has a crappy optimizer sounds kind of funny. No
disrespect meant. It's not unlike claiming that the Earth is 6000 years old.

>
> And here's something I never saw you consider: hints making performance
> worse.
>
Sure. If you give me the steering wheell, there is a chance that I might
get car off the cliff or even run someone over, but that doesn't mean
that there is no need for having one. After all, we're talking about the
ability to control the optimizer decision.

> Want to remove a reverse index scan? Reindex with DESC on the column
> being reversed. That was added in 8.3. Getting too many calls for nested
> loops when a merge or hash would be faster? Increase the statistics
> target for the column causing the problems and re-analyze. Find an
> actual bug in the optimizer? Tell the devs and they'll fix it. Just stay
> current, and you get all those benefits. This is true for any database;
> bugs get fixed, things get faster and more secure.
>
In the meantime, the other databases provide hints which help me bridge
the gap. As I said before: hints are there, even if they were not meant
to be used that way. I can do things in a way that I consider very
non-elegant. The hints are there because they are definitely needed.
Yet, there is a religious zeal and a fatwa against them.

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

On Feb 3, 2011, at 1:50 PM, Mladen Gogala wrote:

> So, I will have to go back on my decision to use Postgres and re-consider MySQL? I will rather throw away the effort
investedin studying Postgres than to risk an unfixable application downtime.  I am not sure about the world domination
thing,though. Optimizer hints are a big feature that everybody else has and Postgres does not have because of religious
reasons.

As always, you should use the tool you consider best for the job. If you think MySQL as both a product and a community
hasa better chance of giving you what you want, then you should use MySQL. 

2011/2/3 Mladen Gogala <mladen.gogala@vmsinfo.com>:
> Chris Browne wrote:
>>
>> Well, the community declines to add hints until there is actual
>> consensus on a good way to add hints.
>>
>
> OK. That's another matter entirely.   Who should make that decision? Is
> there a committee or a person who would be capable of making that decision?
>

Because there are not consensus about hints, then hints are not in pg.

And community development must be based on consensus. There are not second way.

Hints are not a win from some reasons.

Situation isn't immutable. There are a lot of features, that was
rejected first time - like replication. But it needs a different
access. You have to show tests, use cases, code and you have to
satisfy all people, so your request is good and necessary. Argument,
so other databases has this feature is a last on top ten.

>> Nobody has ever proposed a way to add hints where consensus was arrived
>> at that the way was good, so...
>>
>
> So, I will have to go back on my decision to use Postgres and re-consider
> MySQL? I will rather throw away the effort invested in studying Postgres
> than to risk an unfixable application downtime.  I am not sure about the
> world domination thing, though. Optimizer hints are a big feature that
> everybody else has and Postgres does not have because of religious reasons.

it's not correct from you. There is a real arguments against hints.

>

you can try a edb. There is a other external modul

http://postgresql.1045698.n5.nabble.com/contrib-plantuner-enable-PostgreSQL-planner-hints-td1924794.html

Regards

Pavel Stehule


> --
>
> Mladen Gogala Sr. Oracle DBA
> 1500 Broadway
> New York, NY 10036
> (212) 329-5251
> http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions
>
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

On 2/3/11 1:18 PM, Chris Browne wrote:
> mladen.gogala@vmsinfo.com (Mladen Gogala) writes:
>> I must say that this purist attitude is extremely surprising to
>> me. All the major DB vendors support optimizer hints,

I don't think that's actually accurate.  Can you give me a list of
DBMSes which support hints other than Oracle?

> Well, the community declines to add hints until there is actual
> consensus on a good way to add hints.
>
> Nobody has ever proposed a way to add hints where consensus was arrived
> at that the way was good, so...

Well, we did actually have some pretty good proposals (IIRC) for
selectively adjusting the cost model to take into account DBA knowledge.
 These needed some refinement, but in general seem like the right way to go.

However, since this system wasn't directly compatible with Oracle Hints,
folks pushing for hints dropped the solution as unsatisfactory. This is
the discussion we have every time: the users who want hints specifically
want hints which work exactly like Oracle's, and aren't interested in a
system designed for PostgreSQL.  It's gotten very boring; it's like the
requests to support MySQL-only syntax.

--
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com

> In the meantime, the other databases provide hints which help me bridge the
> gap. As I said before: hints are there, even if they were not meant to be
> used that way. I can do things in a way that I consider very non-elegant.
> The hints are there because they are definitely needed. Yet, there is a
> religious zeal and a fatwa against them.
>

Other databases has different development model. It isn't based on
consensus. The are not any commercial model for PostgreSQL. There are
not possible to pay programmers. So you can pay and as customer, you
are boss or use it freely and search a consensus - a common talk.

Regards

Pavel Stehule

> --
>
> Mladen Gogala Sr. Oracle DBA
> 1500 Broadway
> New York, NY 10036
> (212) 329-5251
> http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions
>
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Josh Berkus wrote:
> However, since this system wasn't directly compatible with Oracle Hints,
> folks pushing for hints dropped the solution as unsatisfactory. This is
> the discussion we have every time: the users who want hints specifically
> want hints which work exactly like Oracle's, and aren't interested in a
> system designed for PostgreSQL.  It's gotten very boring; it's like the
> requests to support MySQL-only syntax.
>
Actually, I don't want Oracle hints. Oracle hints are ugly and
cumbersome. I would prefer something like this:

http://dev.mysql.com/doc/refman/5.0/en/index-hints.html

That should also answer the question about other databases supporting hints.

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

Mladen Gogala wrote:
> Actually, I don't want Oracle hints. Oracle hints are ugly and
> cumbersome. I would prefer something like this:
>
> http://dev.mysql.com/doc/refman/5.0/en/index-hints.html
>
> That should also answer the question about other databases supporting hints.
>

Sorry. I forgot that MySQL too is now an Oracle product.


--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

> The hints are there because they are definitely needed. Yet, there is a
> religious zeal and a fatwa against them.

The opposition is philosophical, not "religious". There is no "fatwa".
If you want a serious discussion, avoid inflammatory terms.

---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
www.truviso.com

On 04/02/11 11:08, Josh Berkus wrote:
> I don't think that's actually accurate.  Can you give me a list of
> DBMSes which support hints other than Oracle?
>
DB2 LUW (Linux, Unix, Win32 code base) has hint profiles:

http://justdb2chatter.blogspot.com/2008/06/db2-hints-optimizer-selection.html

david@lang.hm wrote:
> I am making the assumption that an Analyze run only has to go over the
> data once (a seqential scan of the table if it's >> ram for example)
> and gathers stats as it goes.

And that's the part there's some confusion about here.  ANALYZE grabs a
random set of samples from the table, the number of which is guided by
the setting for default_statistics_target.  The amount of time it takes
is not proportional to the table size; it's only proportional to the
sampling size.  Adding a process whose overhead is proportional to the
table size, such as the continuous analyze idea you're proposing, is
quite likely to be a big step backwards relative to just running a
single ANALYZE after the loading is finished.

What people should be doing if concerned about multiple passes happening
is something like this:

CREATE TABLE t (s serial, i integer) WITH (autovacuum_enabled=off);
[populate table]
ANALYZE t;
ALTER TABLE t SET (autovacuum_enabled=on);

I'm not optimistic the database will ever get smart enough to recognize
bulk loading and do this sort of thing automatically, but as the
workaround is so simple it's hard to get motivated to work on trying.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

Maciek Sakrejda wrote:
>> The hints are there because they are definitely needed. Yet, there is a
>> religious zeal and a fatwa against them.
>>
>
> The opposition is philosophical, not "religious". There is no "fatwa".
> If you want a serious discussion, avoid inflammatory terms.
>
>
>
I don't want to insult anybody but the whole thing does look strange.
Maybe we can agree to remove that ridiculous "we don't want hints" note
from Postgresql wiki? That would make it look less like , hmph,
philosophical issue and more "not yet implemented" issue, especially if
we have in mind that hints are already here, in the form of
"enable_<method>" switches.


--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

Mark Kirkwood wrote:
> On 04/02/11 11:08, Josh Berkus wrote:
>
>> I don't think that's actually accurate.  Can you give me a list of
>> DBMSes which support hints other than Oracle?
>>
>>
> DB2 LUW (Linux, Unix, Win32 code base) has hint profiles:
>
> http://justdb2chatter.blogspot.com/2008/06/db2-hints-optimizer-selection.html
>
>
>
SQL Server and MySQL too.

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

On Feb 3, 2011, at 17:08, Josh Berkus wrote:

> On 2/3/11 1:18 PM, Chris Browne wrote:
>> mladen.gogala@vmsinfo.com (Mladen Gogala) writes:
>>> I must say that this purist attitude is extremely surprising to
>>> me. All the major DB vendors support optimizer hints,
>
> I don't think that's actually accurate.  Can you give me a list of
> DBMSes which support hints other than Oracle?

1 minute of Googling shows results for:

db2:
<http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.admin/p9li375.htm>

informix:
<http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/0502fan/0502fan.html>

sybase:
<http://searchenterpriselinux.techtarget.com/answer/Query-hints-to-override-optimizer>

mysql:
<http://dev.mysql.com/doc/refman/5.0/en/index-hints.html>

I haven't read much of the rest of this thread, so others may have brought these up before.

Michael Glaesemann
grzm seespotcode net

> I don't want to insult anybody but the whole thing does look strange.
> Maybe we can agree to remove that ridiculous "we don't want hints" note
> from Postgresql wiki? That would make it look less like , hmph,
> philosophical issue and more "not yet implemented" issue, especially if
> we have in mind that hints are already here, in the form of
> "enable_<method>" switches.

Link? There's a lot of stuff on the wiki.


--
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com

Mladen Gogala <mladen.gogala@vmsinfo.com> wrote:

> Maybe we can agree to remove that ridiculous "we don't want hints"
> note from Postgresql wiki?

I'd be against that.  This is rehashed less frequently since that
went in.  Less wasted time and bandwidth with it there.

> That would make it look less like , hmph, philosophical issue and
> more "not yet implemented" issue,

Exactly what we don't want.

> especially if we have in mind that hints are already here, in the
> form of "enable_<method>" switches.

Those aren't intended as hints for production use.  They're there
for diagnostic purposes.  In our shop we've never used any of those
flags in production.

That said, there are ways to force an optimization barrier when
needed, which I have occasionally seen people find useful.  And
there are sometimes provably logically equivalent ways to write a
query which result in different plans with different performance.
It's rare that someone presents a poorly performing query on the
list and doesn't get a satisfactory resolution fairly quickly -- if
they present sufficient detail and work nicely with others who are
volunteering their time to help.

-Kevin

> With all
> due respect, I consider myself smarter than the optimizer.  I'm 6'4", 235LBS
> so telling me that you disagree and that I am more stupid than a computer
> program,  would not be a smart thing to do. Please, do not misunderestimate
> me.

I don't see computer programs make thinly veiled threats, especially
in a public forum.
I'll do what you claim is not the smart thing and disagree with you.
You are wrong.
You are dragging the signal-to-noise ratio of this discussion down.
You owe Greg an apology.

Thank you.

It appears I owe an apology also, for jumping to that conclusion. It
was rash and unfair of me. I am sorry.

On Wed, Feb 2, 2011 at 5:03 PM, Mladen Gogala <mladen.gogala@vmsinfo.com> wrote:
> Justin Pitts wrote:
>>>
>>> With all
>>> due respect, I consider myself smarter than the optimizer.  I'm 6'4",
>>> 235LBS
>>> so telling me that you disagree and that I am more stupid than a computer
>>> program,  would not be a smart thing to do. Please, do not
>>> misunderestimate
>>> me.
>>>
>>
>> I don't see computer programs make thinly veiled threats, especially
>> in a public forum.
>> I'll do what you claim is not the smart thing and disagree with you.
>> You are wrong.
>> You are dragging the signal-to-noise ratio of this discussion down.
>> You owe Greg an apology.
>>
>
> I apologize if that was understood as a threat. It was actually a joke. I
> thought that my using of the word "misunderestimate" has made it abundantly
> clear. Apparently, G.W. doesn't have as many fans as I have previously
> thought. Once again, it was a joke, I humbly apologize if that was
> misunderstood.
>
> --
>
> Mladen Gogala Sr. Oracle DBA
> 1500 Broadway
> New York, NY 10036
> (212) 329-5251
> http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions
>
>
>
>

Josh Berkus wrote:
>> I don't want to insult anybody but the whole thing does look strange.
>> Maybe we can agree to remove that ridiculous "we don't want hints" note
>> from Postgresql wiki? That would make it look less like , hmph,
>> philosophical issue and more "not yet implemented" issue, especially if
>> we have in mind that hints are already here, in the form of
>> "enable_<method>" switches.
>>
>
> Link? There's a lot of stuff on the wiki.
>
>
>
http://wiki.postgresql.org/wiki/Todo#Features_We_Do_Not_Want

No. 2 on the list.

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

On Thu, Feb 3, 2011 at 3:54 PM,  <david@lang.hm> wrote:
> with the current code, this is a completely separate process that knows
> nothing about the load, so if you kick it off when you start the load, it
> makes a pass over the table (competing for I/O), finishes, you continue to
> update the table, so it makes another pass, etc. As you say, this is a bad
> thing to do. I am saying to have an option that ties the two togeather,
> essentially making the data feed into the Analyze run be a fork of the data
> comeing out of the insert run going to disk. So the Analyze run doesn't do
> any I/O and isn't going to complete until the insert is complete. At which
> time it will have seen one copy of the entire table.

Yeah, but you'll be passing the entire table through this separate
process that may only need to see 1% of it or less on a large table.
If you want to write the code and prove it's better than what we have
now, or some other approach that someone else may implement in the
meantime, hey, this is an open source project, and I like improvements
as much as the next guy.  But my prediction for what it's worth is
that the results will suck.  :-)

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Kevin Grittner wrote:
> Mladen Gogala <mladen.gogala@vmsinfo.com> wrote:
>
>
>> Maybe we can agree to remove that ridiculous "we don't want hints"
>> note from Postgresql wiki?
>>
>
> I'd be against that.  This is rehashed less frequently since that
> went in.  Less wasted time and bandwidth with it there.
>

Well, the problem will not go away.  As I've said before, all other
databases have that feature and none of the reasons listed here
convinced me that everybody else has a crappy optimizer.  The problem
may go away altogether if people stop using PostgreSQL.
>
>
>> That would make it look less like , hmph, philosophical issue and
>> more "not yet implemented" issue,
>>
>
> Exactly what we don't want.
>
Who is "we"?


--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

On Thu, 2011-02-03 at 18:33 -0500, Mladen Gogala wrote:
>
> >
> > Exactly what we don't want.
> >
> Who is "we"?

The majority of long term hackers.

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

On Thu, Feb 3, 2011 at 6:33 PM, Mladen Gogala <mladen.gogala@vmsinfo.com> wrote:
> Kevin Grittner wrote:
>> Mladen Gogala <mladen.gogala@vmsinfo.com> wrote:
>>>
>>> Maybe we can agree to remove that ridiculous "we don't want hints"
>>> note from Postgresql wiki?
>>>
>>
>>  I'd be against that.  This is rehashed less frequently since that
>> went in.  Less wasted time and bandwidth with it there.
>
> Well, the problem will not go away.  As I've said before, all other
> databases have that feature and none of the reasons listed here convinced me
> that everybody else has a crappy optimizer.  The problem may go away
> altogether if people stop using PostgreSQL.

You seem to be asserting that without hints, problem queries can't be
fixed.  But you haven't offered any evidence for that proposition, and
it doesn't match my experience, or the experience of other people on
this list who have been using PostgreSQL for a very long time.  If you
want to seriously advance this conversation, you should (1) learn how
people who use PostgreSQL solve these problems and then (2) if you
think there are cases where those methods are inadequate, present
them, and let's have a discussion about it.  People in this community
DO change their mind about things - but they do so in response to
*evidence*.  You haven't presented one tangible example of where the
sort of hints you seem to want would actually help anything, and yet
you're accusing the people who don't agree with you of being engaged
in a religious war.  It seems to me that the shoe is on the other
foot.  Religion is when you believe something first and then look for
evidence to support it.  Science goes the other direction.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Joshua D. Drake wrote:
> On Thu, 2011-02-03 at 18:33 -0500, Mladen Gogala wrote:
>
>>
>>
>>>
>>> Exactly what we don't want.
>>>
>>>
>> Who is "we"?
>>
>
> The majority of long term hackers.
>
>
If that is so,  I don't see "world domination" in the future, exactly
the opposite. Database whose creators don't trust their users cannot
count on the very bright future. All other databases do have that
feature. I must say, this debate gave me a good deal of stuff to think
about.

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

On 2/3/11 1:34 PM, Shaun Thomas wrote:
>> I must say that this purist attitude is extremely surprising to me.
>> All the major DB vendors support optimizer hints, yet in the
>> Postgres community, they are considered bad with almost religious
>> fervor. Postgres community is quite unique with the fatwa against
>> hints.
>
> You missed the argument. The community, or at least the devs, see hints
>  as an ugly hack.

Let's kill the myth right now that Postgres doesn't have hints.  It DOES have hints.

Just read this forum for a few days and see how many time there are suggestions like "disable nested loops" or "disable
seqscan",or "change the random page cost", or "change the join collapse limit". 

All of these options are nothing more than a way of altering the planner's choices so that it will pick the plan that
thedesigner already suspects is more optimal. 

If that's not a hint, I don't know what is.

Craig

> All other databases do have that feature. I must say, this
> debate gave me a good deal of stuff to think about.

Aaaaand, I think we're done here. The idea that the lack of hints will kill
PostgreSQL is already demonstrably false. This is sounding more and
more like a petulant tantrum.

Folks, I apologize for ever taking part in this conversation and contributing
to the loss of signal to noise. Please forgive me.

--
Shaun Thomas
Peak6 | 141 W. Jackson Blvd. | Suite 800 | Chicago, IL 60604
312-676-8870
sthomas@peak6.com

______________________________________________

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email

On Thu, 3 Feb 2011, Robert Haas wrote:

> On Thu, Feb 3, 2011 at 3:54 PM,  <david@lang.hm> wrote:
>> with the current code, this is a completely separate process that knows
>> nothing about the load, so if you kick it off when you start the load, it
>> makes a pass over the table (competing for I/O), finishes, you continue to
>> update the table, so it makes another pass, etc. As you say, this is a bad
>> thing to do. I am saying to have an option that ties the two togeather,
>> essentially making the data feed into the Analyze run be a fork of the data
>> comeing out of the insert run going to disk. So the Analyze run doesn't do
>> any I/O and isn't going to complete until the insert is complete. At which
>> time it will have seen one copy of the entire table.
>
> Yeah, but you'll be passing the entire table through this separate
> process that may only need to see 1% of it or less on a large table.
> If you want to write the code and prove it's better than what we have
> now, or some other approach that someone else may implement in the
> meantime, hey, this is an open source project, and I like improvements
> as much as the next guy.  But my prediction for what it's worth is
> that the results will suck.  :-)

I will point out that 1% of a very large table can still be a lot of disk
I/O that is avoided (especially if it's random I/O that's avoided)

David Lang

Robert Haas wrote:
> On Thu, Feb 3, 2011 at 6:33 PM, Mladen Gogala <mladen.gogala@vmsinfo.com> wrote:
>
>> Kevin Grittner wrote:
>>
>>> Mladen Gogala <mladen.gogala@vmsinfo.com> wrote:
>>>
>>>> Maybe we can agree to remove that ridiculous "we don't want hints"
>>>> note from Postgresql wiki?
>>>>
>>>>
>>>  I'd be against that.  This is rehashed less frequently since that
>>> went in.  Less wasted time and bandwidth with it there.
>>>
>> Well, the problem will not go away.  As I've said before, all other
>> databases have that feature and none of the reasons listed here convinced me
>> that everybody else has a crappy optimizer.  The problem may go away
>> altogether if people stop using PostgreSQL.
>>
>
> You seem to be asserting that without hints, problem queries can't be
> fixed.  But you haven't offered any evidence for that proposition, and
> it doesn't match my experience, or the experience of other people on
> this list who have been using PostgreSQL for a very long time.  If you
> want to seriously advance this conversation, you should (1) learn how
> people who use PostgreSQL solve these problems and then (2) if you
> think there are cases where those methods are inadequate, present
> them, and let's have a discussion about it.  People in this community
> DO change their mind about things - but they do so in response to
> *evidence*.  You haven't presented one tangible example of where the
> sort of hints you seem to want would actually help anything, and yet
> you're accusing the people who don't agree with you of being engaged
> in a religious war.  It seems to me that the shoe is on the other
> foot.  Religion is when you believe something first and then look for
> evidence to support it.  Science goes the other direction.
>
>
Actually, it is not unlike a religious dogma, only stating that "hints
are bad". It even says so in the wiki. The arguments are
1) Refusal to implement hints is motivated by distrust toward users,
citing that some people may mess things up.
    Yes, they can, with and without hints.
2) All other databases have them. This is a major feature and if I were
in the MySQL camp, I would use it as an
   argument. Asking me for some "proof" is missing the point. All other
databases have hints precisely because
   they are useful. Assertion that only Postgres is so smart that can
operate without hints doesn't match the
   reality. As a matter of fact, Oracle RDBMS on the same machine will
regularly beat PgSQL in performance.
   That has been my experience so far.   I even posted counting query
results.
3) Hints are "make it or break it" feature. They're absolutely needed in
the fire extinguishing situations.

I see no arguments to say otherwise and until that ridiculous "we don't
want hints" dogma is on wiki, this is precisely what it is:  a dogma.
Dogmas do not change and I am sorry that you don't see it that way.
However, this discussion
did convince me that I need to take another look at MySQL and tone down
my engagement with PostgreSQL community. This is my last post on the
subject because posts are becoming increasingly personal. This level of
irritation is also
characteristic of a religious community chastising a sinner. Let me
remind you again: all other major databases have that possibility:
Oracle, MySQL, DB2, SQL Server and Informix. Requiring burden of proof
about hints is equivalent to saying that all these databases are
developed by idiots and have a crappy optimizer.
I am not going to back down, but I may stop using Postgres altogether.
If that was your goal, you almost achieved it. Oh yes, and good luck
with the world domination. If there is not enough common sense even to
take down  that stupid dogma on the wiki, there isn't much hope left.
With this post, my participation in this group is finished, for the
foreseeable future.


--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

On 2011-02-03 23:29, Robert Haas wrote:
> Yeah, but you'll be passing the entire table through this separate
> process that may only need to see 1% of it or less on a large table.

It doesn't sound too impossible to pass only a percentage, starting high
and dropping towards 1% once the loaded size has become "large".
--
Jeremy

On 2011-02-03 21:51, Mark Kirkwood wrote:
> The cases I've seen in production typically involve "outgrowing" optimizer parameter settings: (e.g work_mem,
effective_cache_size)as the application dataset gets bigger over time. 

An argument in favour of the DBMS maintaining a running estimate of such things.
--
Jeremy

On PostgreSQL, the difference in no hints and hints for that one query
with skewed data is that the query finishes a little faster.   On some
others, which shall remain nameless, it is the difference between
finishing in seconds or days, or maybe never.  Hints can be useful, but
I can also see why they are not a top priority.  They are rarely needed,
and only when working around a bug.  If you want them so badly, you have
the source, write a contrib module  (can you do that on Oracle or
MSSQL?)  If I have a choice between the developers spending time on
implementing hints, and spending time on improving the optimiser, I'll
take the optimiser.

Tom Kyte agrees:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8912905298920
http://tkyte.blogspot.com/2006/08/words-of-wisdom.html



Oracle can be faster on count queries, but that is the only case I have
seen.   Generally on most other queries, especially when it involves
complex joins, or indexes on text fields, PostgreSQL is faster on the
same hardware.

On 2011-02-03 21:51, Mark Kirkwood wrote:

The cases I've seen in production typically involve "outgrowing" optimizer parameter settings: (e.g work_mem, effective_cache_size) as the application dataset gets bigger over time.

An argument in favour of the DBMS maintaining a running estimate of such things.

On Thu, Feb 3, 2011 at 7:39 PM,  <david@lang.hm> wrote:
>> Yeah, but you'll be passing the entire table through this separate
>> process that may only need to see 1% of it or less on a large table.
>> If you want to write the code and prove it's better than what we have
>> now, or some other approach that someone else may implement in the
>> meantime, hey, this is an open source project, and I like improvements
>> as much as the next guy.  But my prediction for what it's worth is
>> that the results will suck.  :-)
>
> I will point out that 1% of a very large table can still be a lot of disk
> I/O that is avoided (especially if it's random I/O that's avoided)

Sure, but I think that trying to avoid it will be costly in other ways
- you'll be streaming a huge volume of data through some auxiliary
process, which will have to apply some algorithm that's very different
from the one we use today.  The reality is that I think there's little
evidence that the way we do ANALYZE now is too expensive.  It's
typically very cheap and works very well.  It's a bit annoying when it
fires off in the middle of a giant data load, so we might need to
change the time of it a little, but if there's a problem with the
operation itself being too costly, this is the first I'm hearing of
it.  We've actually worked *really* hard to make it cheap.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Thu, Feb 3, 2011 at 7:39 PM, Mladen Gogala <mladen.gogala@vmsinfo.com> wrote:
>  reality. As a matter of fact, Oracle RDBMS on the same machine will
> regularly beat PgSQL in performance.
>  That has been my experience so far.   I even posted counting query results.

It sure is, but those count queries didn't run faster because of query
planner hints.  They ran faster because of things like index-only
scans, fast full index scans, asynchronous I/O, and parallel query.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Thu, 3 Feb 2011, Robert Haas wrote:

> On Thu, Feb 3, 2011 at 7:39 PM,  <david@lang.hm> wrote:
>>> Yeah, but you'll be passing the entire table through this separate
>>> process that may only need to see 1% of it or less on a large table.
>>> If you want to write the code and prove it's better than what we have
>>> now, or some other approach that someone else may implement in the
>>> meantime, hey, this is an open source project, and I like improvements
>>> as much as the next guy.  But my prediction for what it's worth is
>>> that the results will suck.  :-)
>>
>> I will point out that 1% of a very large table can still be a lot of disk
>> I/O that is avoided (especially if it's random I/O that's avoided)
>
> Sure, but I think that trying to avoid it will be costly in other ways
> - you'll be streaming a huge volume of data through some auxiliary
> process, which will have to apply some algorithm that's very different
> from the one we use today.  The reality is that I think there's little
> evidence that the way we do ANALYZE now is too expensive.  It's
> typically very cheap and works very well.  It's a bit annoying when it
> fires off in the middle of a giant data load, so we might need to
> change the time of it a little, but if there's a problem with the
> operation itself being too costly, this is the first I'm hearing of
> it.  We've actually worked *really* hard to make it cheap.

I could be misunderstanding things here, but my understanding is that it's
'cheap' in that it has little impact on the database while it is running.

the issue here is that the workflow is

load data
analyze
start work

so the cost of analyze in this workflow is not "1% impact on query speed
for the next X time", it's "the database can't be used for the next X time
while we wait for analyze to finish running"

I don't understand why the algorithm would have to be so different than
what's done today, surely the analyze thread could easily be tweaked to
ignore the rest of the data (assuming we don't have the thread sending the
data to analyze do the filtering)

David Lang

> http://wiki.postgresql.org/wiki/Todo#Features_We_Do_Not_Want
>
> No. 2 on the list.

Heck, *I* wrote that text.

I quote:

"Optimizer hints are used to work around problems in the optimizer and
introduce upgrade and maintenance issues. We would rather have the
problems reported and fixed. We have discussed a more sophisticated
system of per-class cost adjustment instead, but a specification remains
to be developed."

That seems pretty straightforwards.  There are even links to prior
discussions about what kind of system would work.  I don't think this
text needs any adjustment; that's our clear consensus on the hint issue:
we want a tool which works better than what we've seen in other databases.

Quite frankly, the main reason why most DBMSes have a hinting system has
nothing to do with the quality of optimizer and everything to do with
DBAs who think they're smarter than the optimizer (incorrectly).  Oracle
has a darned good query optimizer, and SQL server's is even better.
However, there are a lot of undereducated or fossilized DBAs out there
who don't trust the query planner and want to override it in fairly
arbitrary ways; I refer you to the collected works of Dan Tow, for example.

In many cases Hints are used by DBAs in "emergency" situations because
they are easier than figuring out what the underlying issue is, even
when that could be done relatively simply.  Improving diagnostic query
tools would be a much better approach here; for example, the team
working on hypothetical indexes has a lot to offer.  If you can figure
out what's really wrong with the query in 10 minutes, you don't need a hint.

Yes, I occasionally run across cases where having a query tweaking
system would help me fix a pathological failure in the planner.
However, even on data warehouses that's less than 0.1% of the queries I
deal with, so this isn't exactly a common event.  And any hinting system
we develop needs to address those specific cases, NOT a hypothetical
case which can't be tested.  Otherwise we'll implement hints which
actually don't improve queries.

Commercial DBMSes have to give in to what their big paying customers
want, no matter how stupid it is. I'm grateful that I can work on a DBMS
-- the third most popular SQL DBMS in the world -- which can focus on
quality instead.

--
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com

On Thu, Feb 3, 2011 at 8:37 PM,  <david@lang.hm> wrote:
> On Thu, 3 Feb 2011, Robert Haas wrote:
>
>> On Thu, Feb 3, 2011 at 7:39 PM,  <david@lang.hm> wrote:
>>>>
>>>> Yeah, but you'll be passing the entire table through this separate
>>>> process that may only need to see 1% of it or less on a large table.
>>>> If you want to write the code and prove it's better than what we have
>>>> now, or some other approach that someone else may implement in the
>>>> meantime, hey, this is an open source project, and I like improvements
>>>> as much as the next guy.  But my prediction for what it's worth is
>>>> that the results will suck.  :-)
>>>
>>> I will point out that 1% of a very large table can still be a lot of disk
>>> I/O that is avoided (especially if it's random I/O that's avoided)
>>
>> Sure, but I think that trying to avoid it will be costly in other ways
>> - you'll be streaming a huge volume of data through some auxiliary
>> process, which will have to apply some algorithm that's very different
>> from the one we use today.  The reality is that I think there's little
>> evidence that the way we do ANALYZE now is too expensive.  It's
>> typically very cheap and works very well.  It's a bit annoying when it
>> fires off in the middle of a giant data load, so we might need to
>> change the time of it a little, but if there's a problem with the
>> operation itself being too costly, this is the first I'm hearing of
>> it.  We've actually worked *really* hard to make it cheap.
>
> I could be misunderstanding things here, but my understanding is that it's
> 'cheap' in that it has little impact on the database while it is running.

I mean that it's cheap in that it usually takes very little time to complete.

> the issue here is that the workflow is
>
> load data
> analyze
> start work
>
> so the cost of analyze in this workflow is not "1% impact on query speed for
> the next X time", it's "the database can't be used for the next X time while
> we wait for analyze to finish running"

OK.

> I don't understand why the algorithm would have to be so different than
> what's done today, surely the analyze thread could easily be tweaked to
> ignore the rest of the data (assuming we don't have the thread sending the
> data to analyze do the filtering)

If you want to randomly pick 10,000 rows out of all the rows that are
going to be inserted in the table without knowing in advance how many
there will be, how do you do that?  Maybe there's an algorithm, but
it's not obvious to me.  But mostly, I question how expensive it is to
have a second process looking at the entire table contents vs. going
back and rereading a sample of rows at the end.  I can't remember
anyone ever complaining "ANALYZE took too long to run".  I only
remember complaints of the form "I had to remember to manually run it
and I wish it had just happened by itself".

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

> I can't remember
> anyone ever complaining "ANALYZE took too long to run".  I only
> remember complaints of the form "I had to remember to manually run it
> and I wish it had just happened by itself".

Robert,

This sounds like an argument in favor of an implicit ANALYZE after all
COPY statements, and/or an implicit autoanalyze check after all
INSERT/UPDATE statements.

-Conor

On Thu, 2011-02-03 at 18:12 -0800, Conor Walsh wrote:
> > I can't remember
> > anyone ever complaining "ANALYZE took too long to run".  I only
> > remember complaints of the form "I had to remember to manually run it
> > and I wish it had just happened by itself".
>
> Robert,
>
> This sounds like an argument in favor of an implicit ANALYZE after all
> COPY statements, and/or an implicit autoanalyze check after all
> INSERT/UPDATE statements.

Well that already happens. Assuming you insert/update or copy in a
greater amount than the threshold for the

autovacuum_analyze_scale_factor

Then autovacuum is going to analyze on the next run. The default is .1
so it certainly doesn't take much.

JD

>
> -Conor
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

On Thu, Feb 3, 2011 at 6:33 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> Well that already happens...

My understanding is that auto-analyze will fire only after my
transaction is completed, because it is a seperate daemon.  If I do
like so:

BEGIN;
COPY ...;
-- Dangerously un-analyzed
SELECT complicated-stuff ...;
END;

Auto-analyze does not benefit me, or might not because it won't fire
often enough.  I agree that analyze is very fast, and it often seems
to me like the cost/benefit ratio suggests making auto-analyze even
more aggressive.

Disclaimer/disclosure: I deal exclusively with very large data sets
these days, so analyzing all the time is almost a highly effective
worst-case amortization.  I understand that constant analyze is not so
great in, say, an OLTP setting.  But if the check is cheap, making
auto-analyze more integrated and less daemon-driven might be a net
win.  I'm not sure.

-Conor

On Thu, Feb 3, 2011 at 5:39 PM, Mladen Gogala <mladen.gogala@vmsinfo.com> wrote:
> Actually, it is not unlike a religious dogma, only stating that "hints are
> bad". It even says so in the wiki. The arguments are

There's been considerably more output than "hints bad!  Hulk Smash!"

> 1) Refusal to implement hints is motivated by distrust toward users, citing
> that some people may mess things up.

It's more about creating a knob that will create more problems than it
solves.  Which I get.  And making sure that if you make such a knob
that it'll do the least damage and give the most usefulness.  Until a
good proposal and some code to do it shows up, we're all just waving
our hands around describing different parts of the elephant.

> 2) All other databases have them. This is a major feature and if I were in
> the MySQL camp, I would use it as an
>  argument. Asking me for some "proof" is missing the point. All other
> databases have hints precisely because
>  they are useful.

Uh, two points.  1: Argumentum Ad Populum.  Just because it's popular
doesn't mean it's right. 2: Other databases have them because their
optimizers can't make the right decision even most of the time.  Yes
they're useful, but like a plastic bad covering a broken car window,
they're useful because they cover something that's inherently broken.


> Assertion that only Postgres is so smart that can operate
> without hints doesn't match the
>  reality.

Again, you're twisting what people have said.  the point being that
while postgresql makes mistakes, we'd rather concentrate on making the
planner smarter than giving it a lobotomy and running it remotely like
a robot.


> As a matter of fact, Oracle RDBMS on the same machine will
> regularly beat PgSQL in performance.

Yes.  And this has little to do with hints.  It has to do with years
of development lead with THOUSANDS of engineers who can work on the
most esoteric corner cases in their spare time.  Find the pg project a
couple hundred software engineers and maybe we'll catch Oracle a
little quicker.  Otherwise we'll have to marshall our resources to do
the best we can on the project ,and that means avoiding maintenance
black holes and having the devs work on the things that give the most
benefit for the cost.  Hints are something only a tiny percentage of
users could actually use and use well.

Write a check, hire some developers and get the code done and present
it to the community.  If it's good and works it'll likely get
accepted.  Or use EDB, since it has oracle compatibility in it.

>  That has been my experience so far.   I even posted counting query results.
> 3) Hints are "make it or break it" feature. They're absolutely needed in the
> fire extinguishing situations.

I've been using pg since 6.5.2.  I've used Oracle since version 8 or
so.  I have never been in a situation with postgresql where I couldn't
fix the problem with either tuning, query editing, or asking Tom for a
patch for a problem I found in it.  Turnaround time on the last patch
that was made to fix my problem was somewhere in the 24 hour range.
If Oracle can patch their planner that fast, let me know.

> I see no arguments to say otherwise and until that ridiculous "we don't want
> hints" dogma is on wiki, this is precisely what it is:  a dogma. Dogmas do
> not change and I am sorry that you don't see it that way. However, this
> discussion

No, it's not dogma, you need to present a strong coherent argument,
not threaten people on the list etc.

On Thu, 2011-02-03 at 16:50 -0500, Mladen Gogala wrote:
> Chris Browne wrote:
> > Well, the community declines to add hints until there is actual
> > consensus on a good way to add hints.
> >
> OK. That's another matter entirely.   Who should make that decision? Is
> there a committee or a person who would be capable of making that decision?

Admittedly I haven't read this whole discussion, but it seems like
"hints" might be too poorly defined right now.

If by "hints" we mean some mechanism to influence the planner in a more
fine-grained way, I could imagine that some proposal along those lines
might gain significant support.

But, as always, it depends on the content and quality of the proposal
more than the title. If someone has thoughtful proposal that tries to
balance things like:
* DBA control versus query changes/comments
* compatibility across versions versus finer plan control
* allowing the existing optimizer to optimize portions of the
  query while controlling other portions
* indicating costs and cardinalities versus plans directly

I am confident that such a proposal will gain traction among the
community as a whole.

However, a series proposals for individual hacks for specific purposes
will probably be rejected. I am in no way implying that you are
approaching it this way -- I am just trying to characterize an approach
that won't make progress.

Regards,
    Jeff Davis

On Thu, Feb 3, 2011 at 7:05 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> If you want to randomly pick 10,000 rows out of all the rows that are
> going to be inserted in the table without knowing in advance how many
> there will be, how do you do that?

Maybe you could instead just have it use some % of the rows going by?
Just a guess.

Scott Marlowe wrote:
> Yes they're useful, but like a plastic bad covering a broken car window,
> they're useful because they cover something that's inherently broken.
>

Awesome.  Now we have a car anology, with a funny typo no less.
"Plastic bad", I love it.  This is real progress toward getting all the
common list argument idioms aired out.  All we need now is a homage to
Mike Godwin and we can close this down.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

On 02/03/2011 09:45 PM, Conor Walsh wrote:
> My understanding is that auto-analyze will fire only after my
> transaction is completed, because it is a seperate daemon.  If I do
> like so:
>
> BEGIN;
> COPY ...;
> -- Dangerously un-analyzed
> SELECT complicated-stuff ...;
> END;
>
> Auto-analyze does not benefit me, or might not because it won't fire
> often enough.  I agree that analyze is very fast, and it often seems
> to me like the cost/benefit ratio suggests making auto-analyze even
> more aggressive.

The count discussion is boring. Nothing new there. But auto-analyze on
dirty writes does interest me. :-)

My understanding is:

1) Background daemon wakes up and checks whether a number of changes
have happened to the database, irrelevant of transaction boundaries.

2) Background daemon analyzes a percentage of rows in the database for
statistical data, irrelevant of row visibility.

3) Analyze is important for both visible rows and invisible rows, as
plan execution is impacted by invisible rows. As long as they are part
of the table, they may impact the queries performed against the table.

4) It doesn't matter if the invisible rows are invisible because they
are not yet committed, or because they are not yet vacuumed.

Would somebody in the know please confirm the above understanding for my
own piece of mind?

Thanks,
mark

--
Mark Mielke<mark@mielke.cc>

On Thu, Feb 3, 2011 at 8:40 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> Scott Marlowe wrote:
>>
>> Yes they're useful, but like a plastic bad covering a broken car window,
>> they're useful because they cover something that's inherently broken.
>>
>
> Awesome.  Now we have a car anology, with a funny typo no less.  "Plastic
> bad", I love it.  This is real progress toward getting all the common list
> argument idioms aired out.  All we need now is a homage to Mike Godwin and
> we can close this down.

It's not so much a car analogy as a plastic bad analogy.

Scott Marlowe wrote:
> It's not so much a car analogy as a plastic bad analogy.
>

Is that like a Plastic Ono Band?  Because I think one of those is the
only thing holding the part of my bumper I smashed in the snow on right
now.  I could be wrong about the name.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

On Thu, Feb 3, 2011 at 8:56 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> Scott Marlowe wrote:
>>
>> It's not so much a car analogy as a plastic bad analogy.
>>
>
> Is that like a Plastic Ono Band?  Because I think one of those is the only
> thing holding the part of my bumper I smashed in the snow on right now.  I
> could be wrong about the name.

No, that's a plastic oh no! band you have.

Scott Marlowe wrote:
> No, that's a plastic oh no! band you have.
>

Wow, right you are.  So with this type holding together my Japanese car,
if it breaks and parts fall off, I'm supposed to yell "Oh, no!  There
goes Tokyo!", yes?

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

On Fri, 4 Feb 2011, ??????? ???????? wrote:

> 2011/2/3 <david@lang.hm>
>
>>
>> If the table is not large enough to fit in ram, then it will compete for
>> I/O, and the user will have to wait.
>>
>> what I'm proposing is that as the records are created, the process doing
>> the creation makes copies of the records (either all of them, or some of
>> them if not all are needed for the analysis, possibly via shareing memory
>> with the analysis process), this would be synchronous with the load, not
>> asynchronous.
>>
>> this would take zero I/O bandwidth, it would take up some ram, memory
>> bandwidth, and cpu time, but a load of a large table like this is I/O
>> contrained.
>>
>> it would not make sense for this to be the default, but as an option it
>> should save a significant amount of time.
>>
>> I am making the assumption that an Analyze run only has to go over the data
>> once (a seqential scan of the table if it's >> ram for example) and gathers
>> stats as it goes.
>>
>> with the current code, this is a completely separate process that knows
>> nothing about the load, so if you kick it off when you start the load, it
>> makes a pass over the table (competing for I/O), finishes, you continue to
>> update the table, so it makes another pass, etc. As you say, this is a bad
>> thing to do. I am saying to have an option that ties the two togeather,
>> essentially making the data feed into the Analyze run be a fork of the data
>> comeing out of the insert run going to disk. So the Analyze run doesn't do
>> any I/O and isn't going to complete until the insert is complete. At which
>> time it will have seen one copy of the entire table.
>>
> Actually that are two different problems. The one is to make analyze more
> automatic to make select right after insert more clever by providing
> statistics to it.
> Another is to make it take less IO resources.
> I dont like for it to be embedded into insert (unless the threshold can be
> determined before inserts starts). Simply because it is more CPU/memory that
> will slow down each insert. And if you will add knob, that is disabled by
> default, this will be no more good than manual analyze.

if it can happen during the copy instead of being a step after the copy it
will speed things up. things like the existing parallel restore could use
this instead ofneeding a separate pass. so I don't think that having to
turn it on manually makes it useless, any more than the fact that you have
to explicity disable fsync makes that disabling feature useless (and the
two features would be likely to be used togeather)

when a copy command is issued, I assume that there is some indication of
how much data is going to follow. I know that it's not just 'insert
everything until the TCP connection terminates' because that would give
you no way of knowing if the copy got everything in or was interrupted
part way through. think about what happens with ftp if the connection
drops, you get a partial file 'successfully' as there is no size provided,
but with HTTP you get a known-bad transfer that you can abort or resume.

David Lang

Mladen Gogala schrieb:

> Well, the problem will not go away.  As I've said before, all other
> databases have that feature and none of the reasons listed here
> convinced me that everybody else has a crappy optimizer.  The problem
> may go away altogether if people stop using PostgreSQL.

A common problem of programmers is, that they want a solution they
already know for a problem they already know, even if it is the worst
solution the can choose.

There are so many possibilities to solve a given problem and you even
have time to do this before your application get released.

Also: if you rely so heavily on hints, then use a database which
supports hints. A basic mantra in every training i have given is: use
the tool/technic/persons which fits best for the needs of the project.
There are many databases out there - choose for every project the one,
which fits best!

Greetings from Germany,
Torsten
--
http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8
verschiedenen Datenbanksystemen abstrahiert,
Queries von Applikationen trennt und automatisch die Query-Ergebnisse
auswerten kann.

> Yes.  And this has little to do with hints.  It has to do with years
> of development lead with THOUSANDS of engineers who can work on the
> most esoteric corner cases in their spare time.  Find the pg project a
> couple hundred software engineers and maybe we'll catch Oracle a
> little quicker.  Otherwise we'll have to marshall our resources to do
> the best we can on the project ,and that means avoiding maintenance
> black holes and having the devs work on the things that give the most
> benefit for the cost.  Hints are something only a tiny percentage of
> users could actually use and use well.
>
> Write a check, hire some developers and get the code done and present
> it to the community.  If it's good and works it'll likely get
> accepted.  Or use EDB, since it has oracle compatibility in it.
>
I have to disagree with you here.   I have never seen Oracle outperform
PostgreSQL on complex joins, which is where the planner comes in.
Perhaps on certain throughput things, but this is likely do to how we
handle dead rows, and counts, which is definitely because of how dead
rows are handled, but the easier maintenance makes up for those.  Also
both of those are by a small percentage.

I have many times had Oracle queries that never finish (OK maybe not
never, but not over a long weekend) on large hardware, but can be
finished on PostgreSQL in a matter or minutes on cheap hardware.   This
happens to the point that often I have set up a PostgreSQL database to
copy the data to for querying and runnign the complex reports, even
though the origin of the data was Oracle, since the application was
Oracle specific.   It took less time to duplicate the database and run
the query on PostgreSQL than it did to just run it on Oracle.

On Thu, Feb 03, 2011 at 04:39:12PM -0800, david@lang.hm wrote:
> On Thu, 3 Feb 2011, Robert Haas wrote:
>
>> On Thu, Feb 3, 2011 at 3:54 PM,  <david@lang.hm> wrote:
>>> with the current code, this is a completely separate process that knows
>>> nothing about the load, so if you kick it off when you start the load, it
>>> makes a pass over the table (competing for I/O), finishes, you continue
>>> to
>>> update the table, so it makes another pass, etc. As you say, this is a
>>> bad
>>> thing to do. I am saying to have an option that ties the two togeather,
>>> essentially making the data feed into the Analyze run be a fork of the
>>> data
>>> comeing out of the insert run going to disk. So the Analyze run doesn't
>>> do
>>> any I/O and isn't going to complete until the insert is complete. At
>>> which
>>> time it will have seen one copy of the entire table.
>>
>> Yeah, but you'll be passing the entire table through this separate
>> process that may only need to see 1% of it or less on a large table.
>> If you want to write the code and prove it's better than what we have
>> now, or some other approach that someone else may implement in the
>> meantime, hey, this is an open source project, and I like improvements
>> as much as the next guy.  But my prediction for what it's worth is
>> that the results will suck.  :-)
>
> I will point out that 1% of a very large table can still be a lot of disk
> I/O that is avoided (especially if it's random I/O that's avoided)
>
> David Lang
>

In addition, the streaming ANALYZE can provide better statistics at
any time during the load and it will be complete immediately. As far
as passing the entire table through the ANALYZE process, a simple
counter can be used to only send the required samples based on the
statistics target. Where this would seem to help the most is in
temporary tables which currently do not work with autovacuum but it
would streamline their use for more complicated queries that need
an analyze to perform well.

Regards,
Ken

04.02.11 16:33, Kenneth Marshall написав(ла):
>
> In addition, the streaming ANALYZE can provide better statistics at
> any time during the load and it will be complete immediately. As far
> as passing the entire table through the ANALYZE process, a simple
> counter can be used to only send the required samples based on the
> statistics target. Where this would seem to help the most is in
> temporary tables which currently do not work with autovacuum but it
> would streamline their use for more complicated queries that need
> an analyze to perform well.
>
Actually for me the main "con" with streaming analyze is that it adds
significant CPU burden to already not too fast load process. Especially
if it's automatically done for any load operation performed (and I can't
see how it can be enabled on some threshold).
And you can't start after some threshold of data passed by since you may
loose significant information (like minimal values).

Best regards, Vitalii Tymchyshyn

On Thu, Feb 03, 2011 at 09:05:52PM -0500, Robert Haas wrote:
> On Thu, Feb 3, 2011 at 8:37 PM,  <david@lang.hm> wrote:
> > On Thu, 3 Feb 2011, Robert Haas wrote:
> >
> >> On Thu, Feb 3, 2011 at 7:39 PM, ?<david@lang.hm> wrote:
> >>>>
> >>>> Yeah, but you'll be passing the entire table through this separate
> >>>> process that may only need to see 1% of it or less on a large table.
> >>>> If you want to write the code and prove it's better than what we have
> >>>> now, or some other approach that someone else may implement in the
> >>>> meantime, hey, this is an open source project, and I like improvements
> >>>> as much as the next guy. ?But my prediction for what it's worth is
> >>>> that the results will suck. ?:-)
> >>>
> >>> I will point out that 1% of a very large table can still be a lot of disk
> >>> I/O that is avoided (especially if it's random I/O that's avoided)
> >>
> >> Sure, but I think that trying to avoid it will be costly in other ways
> >> - you'll be streaming a huge volume of data through some auxiliary
> >> process, which will have to apply some algorithm that's very different
> >> from the one we use today. ?The reality is that I think there's little
> >> evidence that the way we do ANALYZE now is too expensive. ?It's
> >> typically very cheap and works very well. ?It's a bit annoying when it
> >> fires off in the middle of a giant data load, so we might need to
> >> change the time of it a little, but if there's a problem with the
> >> operation itself being too costly, this is the first I'm hearing of
> >> it. ?We've actually worked *really* hard to make it cheap.
> >
> > I could be misunderstanding things here, but my understanding is that it's
> > 'cheap' in that it has little impact on the database while it is running.
>
> I mean that it's cheap in that it usually takes very little time to complete.
>
> > the issue here is that the workflow is
> >
> > load data
> > analyze
> > start work
> >
> > so the cost of analyze in this workflow is not "1% impact on query speed for
> > the next X time", it's "the database can't be used for the next X time while
> > we wait for analyze to finish running"
>
> OK.
>
> > I don't understand why the algorithm would have to be so different than
> > what's done today, surely the analyze thread could easily be tweaked to
> > ignore the rest of the data (assuming we don't have the thread sending the
> > data to analyze do the filtering)
>
> If you want to randomly pick 10,000 rows out of all the rows that are
> going to be inserted in the table without knowing in advance how many
> there will be, how do you do that?  Maybe there's an algorithm, but
> it's not obvious to me.  But mostly, I question how expensive it is to
> have a second process looking at the entire table contents vs. going
> back and rereading a sample of rows at the end.  I can't remember
> anyone ever complaining "ANALYZE took too long to run".  I only
> remember complaints of the form "I had to remember to manually run it
> and I wish it had just happened by itself".
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

Probably doomed to be shot down, but since you are effectively inline,
you could sample assuming a range of table row counts. Start at the
size of a table where random (index) lookups are faster than a sequential
scan and then at appropriate multiples, 100x, 100*100X,... then you should
be able to generate appropriate statistics. I have not actually looked at
how that would happen, but it would certainly allow you to process far, far
fewer rows than the entire table.

Regards,
Ken

On Fri, Feb 4, 2011 at 6:05 AM, Grant Johnson <grant@amadensor.com> wrote:
>
>> Yes.  And this has little to do with hints.  It has to do with years
>> of development lead with THOUSANDS of engineers who can work on the
>> most esoteric corner cases in their spare time.  Find the pg project a
>> couple hundred software engineers and maybe we'll catch Oracle a
>> little quicker.  Otherwise we'll have to marshall our resources to do
>> the best we can on the project ,and that means avoiding maintenance
>> black holes and having the devs work on the things that give the most
>> benefit for the cost.  Hints are something only a tiny percentage of
>> users could actually use and use well.
>>
>> Write a check, hire some developers and get the code done and present
>> it to the community.  If it's good and works it'll likely get
>> accepted.  Or use EDB, since it has oracle compatibility in it.
>>
> I have to disagree with you here.   I have never seen Oracle outperform
> PostgreSQL on complex joins, which is where the planner comes in.  Perhaps
> on certain throughput things, but this is likely do to how we handle dead
> rows, and counts, which is definitely because of how dead rows are handled,
> but the easier maintenance makes up for those.  Also both of those are by a
> small percentage.
>
> I have many times had Oracle queries that never finish (OK maybe not never,
> but not over a long weekend) on large hardware, but can be finished on
> PostgreSQL in a matter or minutes on cheap hardware.   This happens to the
> point that often I have set up a PostgreSQL database to copy the data to for
> querying and runnign the complex reports, even though the origin of the data
> was Oracle, since the application was Oracle specific.   It took less time
> to duplicate the database and run the query on PostgreSQL than it did to
> just run it on Oracle.

It very much depends on the query.  With lots of tables to join, and
with pg 8.1 which is what I used when we were running Oracle 9, Oracle
won.  With fewer tables to join in an otherwise complex reporting
query PostgreSQL won.  I did the exact thing you're talking about. I
actually wrote a simple replication system fro Oracle to PostgreSQL
(it was allowed to be imperfect because it was stats data and we could
recreate at a moment).

PostgreSQL on a PIV workstation with 2G ram and 4 SATA drives in
RAID-10 stomped Oracle on much bigger Sun hardware into the ground for
reporting queries.  Queries that ran for hours or didn't finish in
Oracle ran in 5 to 30 minutes on the pg box.

But not all queries were like that.

On Fri, Feb 4, 2011 at 9:38 AM, Vitalii Tymchyshyn <tivv00@gmail.com> wrote:
> Actually for me the main "con" with streaming analyze is that it adds
> significant CPU burden to already not too fast load process.

Exactly.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Greg Smith wrote:
> Check out
> http://www.indeed.com/jobtrends?q=postgres%2C+mysql%2C+oracle&relative=1&relative=1
> if you want to see the real story here.  Oracle has a large installed
> base, but it's considered a troublesome legacy product being replaced

+1 for Oracle being a "troublesome legacy product".

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

Mladen Gogala wrote:
> Chris Browne wrote:
> > Well, the community declines to add hints until there is actual
> > consensus on a good way to add hints.
> >
> OK. That's another matter entirely.   Who should make that decision? Is
> there a committee or a person who would be capable of making that decision?
>
> > Nobody has ever proposed a way to add hints where consensus was arrived
> > at that the way was good, so...
> >
>
> So, I will have to go back on my decision to use Postgres and
> re-consider MySQL? I will rather throw away the effort invested in

You want to reconsider using MySQL because Postgres doesn't have hints.
Hard to see how that logic works.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

Mladen Gogala wrote:
> Actually, it is not unlike a religious dogma, only stating that "hints
> are bad". It even says so in the wiki. The arguments are
> 1) Refusal to implement hints is motivated by distrust toward users,
> citing that some people may mess things up.
>     Yes, they can, with and without hints.
> 2) All other databases have them. This is a major feature and if I were
> in the MySQL camp, I would use it as an
>    argument. Asking me for some "proof" is missing the point. All other
> databases have hints precisely because
>    they are useful. Assertion that only Postgres is so smart that can
> operate without hints doesn't match the
>    reality. As a matter of fact, Oracle RDBMS on the same machine will
> regularly beat PgSQL in performance.
>    That has been my experience so far.   I even posted counting query
> results.
> 3) Hints are "make it or break it" feature. They're absolutely needed in
> the fire extinguishing situations.
>
> I see no arguments to say otherwise and until that ridiculous "we don't
> want hints" dogma is on wiki, this is precisely what it is:  a dogma.

Uh, that is kind of funny considering that text is on a 'wiki', meaning
everything there is open to change if the group agrees.

> Dogmas do not change and I am sorry that you don't see it that way.
> However, this discussion
> did convince me that I need to take another look at MySQL and tone down
> my engagement with PostgreSQL community. This is my last post on the
> subject because posts are becoming increasingly personal. This level of
> irritation is also
> characteristic of a religious community chastising a sinner. Let me
> remind you again: all other major databases have that possibility:
> Oracle, MySQL, DB2, SQL Server and Informix. Requiring burden of proof
> about hints is equivalent to saying that all these databases are
> developed by idiots and have a crappy optimizer.

You need to state the case for hints independent of what other databases
do, and indepdendent of fixing the problems where the optimizer doesn't
match reatility.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

On 02/04/2011 10:41 AM, Tom Lane wrote:
> 1. Autovacuum fires when the stats collector's insert/update/delete
> counts have reached appropriate thresholds.  Those counts are
> accumulated from messages sent by backends at transaction commit or
> rollback, so they take no account of what's been done by transactions
> still in progress.
>
> 2. Only live rows are included in the stats computed by ANALYZE.
> (IIRC it uses SnapshotNow to decide whether rows are live.)
>
> Although the stats collector does track an estimate of the number of
> dead rows for the benefit of autovacuum, this isn't used by planning.
> Table bloat is accounted for only in terms of growth of the physical
> size of the table in blocks.

Thanks, Tom.

Does this un-analyzed "bloat" not impact queries? I guess the worst case
here is if autovaccum is disabled for some reason and 99% of the table
is dead rows. If I understand the above correctly, I think analyze might
generate a bad plan under this scenario, thinking that a value is
unique, using the index - but every tuple in the index has the same
value and each has to be looked up in the table to see if it is visible?

Still, I guess the idea here is not to disable autovacuum, making dead
rows insignificant in the grand scheme of things. I haven't specifically
noticed any performance problems here - PostgreSQL is working great for
me as usual. Just curiosity...

Cheers,
mark

--
Mark Mielke<mark@mielke.cc>

On Fri, Feb 4, 2011 at 5:17 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Mladen Gogala wrote:
>> characteristic of a religious community chastising a sinner. Let me
>> remind you again: all other major databases have that possibility:
>> Oracle, MySQL, DB2, SQL Server and Informix. Requiring burden of proof
>> about hints is equivalent to saying that all these databases are
>> developed by idiots and have a crappy optimizer.
>
> You need to state the case for hints independent of what other databases
> do, and indepdendent of fixing the problems where the optimizer doesn't
> match reatility.

And that kind of limits to an area where we would the ability to nudge
costs instead of just set them for an individual part of a query.
i.e. join b on (a.a=b.b set selectivity=0.01) or (a.a=b.b set
selectivity=1.0) or something like that.  i.e. a.a and b.b have a lot
of matches or few, etc.  If there's any thought of hinting it should
be something that a DBA, knowing his data model well, WILL know more
than the current planner because the planner can't get cross table
statistics yet.

But then, why not do something to allow cross table indexes and / or
statistics?  To me that would go much further to helping fix the
issues where the current planner "flies blind".

--
To understand recursion, one must first understand recursion.

On Fri, 4 Feb 2011, Vitalii Tymchyshyn wrote:

> 04.02.11 16:33, Kenneth Marshall ???????(??):
>>
>> In addition, the streaming ANALYZE can provide better statistics at
>> any time during the load and it will be complete immediately. As far
>> as passing the entire table through the ANALYZE process, a simple
>> counter can be used to only send the required samples based on the
>> statistics target. Where this would seem to help the most is in
>> temporary tables which currently do not work with autovacuum but it
>> would streamline their use for more complicated queries that need
>> an analyze to perform well.
>>
> Actually for me the main "con" with streaming analyze is that it adds
> significant CPU burden to already not too fast load process. Especially if
> it's automatically done for any load operation performed (and I can't see how
> it can be enabled on some threshold).

two thoughts

1. if it's a large enough load, itsn't it I/O bound?


2. this chould be done in a separate process/thread than the load itself,
that way the overhead of the load is just copying the data in memory to
the other process.

with a multi-threaded load, this would eat up some cpu that could be used
for the load, but cores/chip are still climbing rapidly so I expect that
it's still pretty easy to end up with enough CPU to handle the extra load.

David Lang

> And you can't start after some threshold of data passed by since you may
> loose significant information (like minimal values).
>
> Best regards, Vitalii Tymchyshyn
>

On Sat, Feb 5, 2011 at 12:46 AM,  <david@lang.hm> wrote:
>> Actually for me the main "con" with streaming analyze is that it adds
>> significant CPU burden to already not too fast load process. Especially if
>> it's automatically done for any load operation performed (and I can't see
>> how it can be enabled on some threshold).
>
> two thoughts
>
> 1. if it's a large enough load, itsn't it I/O bound?

Sometimes.  Our COPY is not as cheap as we'd like it to be.

> 2. this chould be done in a separate process/thread than the load itself,
> that way the overhead of the load is just copying the data in memory to the
> other process.

I think that's more expensive than you're giving it credit for.

But by all means implement it and post the patch if it works out...!

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Feb 4, 2011 at 11:37 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Sat, Feb 5, 2011 at 12:46 AM,  <david@lang.hm> wrote:
>>> Actually for me the main "con" with streaming analyze is that it adds
>>> significant CPU burden to already not too fast load process. Especially if
>>> it's automatically done for any load operation performed (and I can't see
>>> how it can be enabled on some threshold).
>>
>> two thoughts
>>
>> 1. if it's a large enough load, itsn't it I/O bound?
>
> Sometimes.  Our COPY is not as cheap as we'd like it to be.

With a 24 drive RAID-10 array that can read at ~1GB/s I am almost
always CPU bound during copies.  This isn't wholly bad as it leaves
spare IO for the rest of the machine so regular work carries on just
fine.

Scott Marlowe wrote:
> With a 24 drive RAID-10 array that can read at ~1GB/s I am almost
> always CPU bound during copies.  This isn't wholly bad as it leaves
> spare IO for the rest of the machine so regular work carries on just
> fine.
>

And you don't need nearly that much I/O bandwidth to reach that point.
I've hit being CPU bound on COPY...FROM on systems with far less drives
than 24.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

On 02/04/2011 02:32 AM, david@lang.hm wrote:
>
> when a copy command is issued, I assume that there is some indication
> of how much data is going to follow.
>
>

No of course there isn't. How would we do that with a stream like STDIN?
Read the code.

cheers

andrew

Mark Mielke <mark@mark.mielke.cc> writes:
> My understanding is:

> 1) Background daemon wakes up and checks whether a number of changes
> have happened to the database, irrelevant of transaction boundaries.

> 2) Background daemon analyzes a percentage of rows in the database for
> statistical data, irrelevant of row visibility.

> 3) Analyze is important for both visible rows and invisible rows, as
> plan execution is impacted by invisible rows. As long as they are part
> of the table, they may impact the queries performed against the table.

> 4) It doesn't matter if the invisible rows are invisible because they
> are not yet committed, or because they are not yet vacuumed.

> Would somebody in the know please confirm the above understanding for my
> own piece of mind?

No.

1. Autovacuum fires when the stats collector's insert/update/delete
counts have reached appropriate thresholds.  Those counts are
accumulated from messages sent by backends at transaction commit or
rollback, so they take no account of what's been done by transactions
still in progress.

2. Only live rows are included in the stats computed by ANALYZE.
(IIRC it uses SnapshotNow to decide whether rows are live.)

Although the stats collector does track an estimate of the number of
dead rows for the benefit of autovacuum, this isn't used by planning.
Table bloat is accounted for only in terms of growth of the physical
size of the table in blocks.

            regards, tom lane

On 2011-02-03 22:48, Scott Marlowe wrote:
> On Thu, Feb 3, 2011 at 8:40 PM, Greg Smith<greg@2ndquadrant.com>  wrote:
>> Scott Marlowe wrote:
>>>
>>> Yes they're useful, but like a plastic bad covering a broken car window,
>>> they're useful because they cover something that's inherently broken.
>>>
>>
>> Awesome.  Now we have a car anology, with a funny typo no less.  "Plastic
>> bad", I love it.  This is real progress toward getting all the common list
>> argument idioms aired out.  All we need now is a homage to Mike Godwin and
>> we can close this down.
>
> It's not so much a car analogy as a plastic bad analogy.
>


Don't be such an analogy Nazi.

On Thu, Feb 3, 2011 at 8:46 PM, Josh Berkus <josh@agliodbs.com> wrote:
> "Optimizer hints are used to work around problems in the optimizer and
> introduce upgrade and maintenance issues. We would rather have the
> problems reported and fixed. We have discussed a more sophisticated
> system of per-class cost adjustment instead, but a specification remains
> to be developed."
>
> That seems pretty straightforwards.  There are even links to prior
> discussions about what kind of system would work.  I don't think this
> text needs any adjustment; that's our clear consensus on the hint issue:
> we want a tool which works better than what we've seen in other databases.

I think it's just dumb to say we don't want hints.  We want hints, or
at least many of us do.  We just want them to actually work, and to
not suck.  Can't we just stop saying we don't want them, and say that
we do want something, but it has to be really good?

> Yes, I occasionally run across cases where having a query tweaking
> system would help me fix a pathological failure in the planner.
> However, even on data warehouses that's less than 0.1% of the queries I
> deal with, so this isn't exactly a common event.  And any hinting system
> we develop needs to address those specific cases, NOT a hypothetical
> case which can't be tested.  Otherwise we'll implement hints which
> actually don't improve queries.

No argument.

The bottom line here is that a lot of features that we don't have are
things that we don't want in the sense that we're not interested in
working on them over other things that seem more pressing, and we have
finite manpower.  But if someone feels motivated to work on it, and
can actually come up with something good, then why should we give the
impression that such a thing would be rejected out of hand?  I think
we ought to nuke that item and replace it with some items in the
optimizer section that express what we DO want, which is some better
ways of fixing queries the few queries that suck despite our best (and
very successful) efforts to produce a top-notch optimizer.

The problem with multi-column statistics is a particularly good
example of something in this class.  We may have a great solution to
that problem for PostgreSQL 11.0.  But between now and then, if you
have that problem, there is no good way to adjust the selectivity
estimates.  If this were an academic research project or just being
used for toy projects that didn't really matter, we might not care.
But this is a real database that people are relying on for their
livelihood, and we should be willing to provide a way for those people
to not get fired when they hit the 0.1% of queries that can't be fixed
using existing methods.  I don't know exactly what the right solution
is off the top of my head, but digging in our heels is not it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> wrote:

> I think it's just dumb to say we don't want hints.  We want hints,
> or at least many of us do.

Well, yeah.  Even those most ostensibly opposed to hints have been
known to post that they would rather not have the optimizer
recognize two logically equivalent constructs and optimize them the
same because they find the current difference "useful to coerce the
optimizer" to choose a certain plan.  That's implementing hints but
refusing to document them.  And it sometimes bites those who don't
know they're accidentally using a hint construct.  An explicit and
documented hint construct would be better.  Probably not a "use this
plan" type hint, but some form of optimization barrier hint, maybe.
You know, like OFFSET 0, but more explicitly hint-like.

> The bottom line here is that a lot of features that we don't have
> are things that we don't want in the sense that we're not
> interested in working on them over other things that seem more
> pressing, and we have finite manpower.  But if someone feels
> motivated to work on it, and can actually come up with something
> good, then why should we give the impression that such a thing
> would be rejected out of hand?  I think we ought to nuke that item
> and replace it with some items in the optimizer section that
> express what we DO want, which is some better ways of fixing
> queries the few queries that suck despite our best (and very
> successful) efforts to produce a top-notch optimizer.
>
> The problem with multi-column statistics is a particularly good
> example of something in this class.  We may have a great solution
> to that problem for PostgreSQL 11.0.  But between now and then, if
> you have that problem, there is no good way to adjust the
> selectivity estimates.

Yeah, this is probably the most important area to devise some
explicit way for a DBA who knows that such multicolumn selections
are going to be used, and is capable of calculating some correlation
factor, could supply it to the optimizer to override the naive
calculation it currently does.  Even there I would tend to think
that the sort of "do it this way" hints that people seem to
initially want wouldn't be good; it should be a way to override the
costing factor which the optimizer gets wrong, so it can do its
usual excellent job of evaluating plans with accurate costs.

> I don't know exactly what the right solution is off the top of my
> head, but digging in our heels is not it.

Well, I'm comfortable digging in my heels against doing *lame* hints
just because "it's what all the other kids are doing," which I think
is the only thing which would have satisfied the OP on this thread.
From both on-list posts and ones exchanged off-list with me, it
seems he was stubbornly resistant to properly tuning the server to
see if any problems remained, or posting particular problems to see
how they would be most effectively handled in PostgreSQL.  We
obviously can't be drawn into dumb approaches because of
ill-informed demands like that.

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Robert Haas <robertmhaas@gmail.com> wrote:
>> I don't know exactly what the right solution is off the top of my
>> head, but digging in our heels is not it.

> Well, I'm comfortable digging in my heels against doing *lame* hints
> just because "it's what all the other kids are doing," which I think
> is the only thing which would have satisfied the OP on this thread.

Right.  If someone comes up with a design that avoids the serious
pitfalls of traditional hinting schemes, that'd be great.  But I'm
not interested in implementing Oracle-like hints just because Oracle
has them, which I think was basically what the OP wanted.  I haven't
seen a hinting scheme that didn't suck (and that includes the aspects
of our own current behavior that are hint-like).  I don't say that
there can't be one.

I believe that the FAQ entry is meant to answer people who come along
and say "oh, this is easily solved, just do what $PRODUCT does".  The
generic answer to that is "no, it's not that easy".  But maybe the FAQ
should be rephrased to be more like "we don't want traditional hints
because of problems X, Y, and Z.  If you have an idea that avoids those
problems, let us know."

            regards, tom lane

On Thu, Feb 10, 2011 at 11:45 AM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Well, I'm comfortable digging in my heels against doing *lame* hints
> just because "it's what all the other kids are doing," which I think
> is the only thing which would have satisfied the OP on this thread.
> From both on-list posts and ones exchanged off-list with me, it
> seems he was stubbornly resistant to properly tuning the server to
> see if any problems remained, or posting particular problems to see
> how they would be most effectively handled in PostgreSQL.  We
> obviously can't be drawn into dumb approaches because of
> ill-informed demands like that.

Nor was I proposing any such thing.  But that doesn't make "we don't
want hints" an accurate statement.  Despite the impression that OP
went away with, the real situation is a lot more nuanced than that,
and the statement on the Todo list gives the wrong impression, IMHO.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 02/10/2011 10:45 AM, Kevin Grittner wrote:

> Even there I would tend to think that the sort of "do it this way"
> hints that people seem to initially want wouldn't be good; it should
> be a way to override the costing factor which the optimizer gets
> wrong, so it can do its usual excellent job of evaluating plans with
> accurate costs.

You know... that's an interesting approach. We already do that with
functions by allowing users to specify the estimated cost, rows
returned, and even override config settings. It's an inexact science at
best, but it might help the optimizer out.

Really... how difficult would it be to add that syntax to the JOIN
statement, for example?

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com

______________________________________________

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email

On 02/10/2011 11:01 AM, Tom Lane wrote:

> But I'm not interested in implementing Oracle-like hints just because
> Oracle has them, which I think was basically what the OP wanted.

Hilariously, I'm not so sure that's what the OP wanted. Several of us
pointed him to EnterpriseDB and their Oracle-style syntax, and the only
thing he said about that was to use it as further evidence that
PostgreSQL should implement them. I'm very tempted to say he wanted
something for free, and was angry he couldn't get it.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com

______________________________________________

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email

On Thu, Feb 10, 2011 at 12:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> Robert Haas <robertmhaas@gmail.com> wrote:
>>> I don't know exactly what the right solution is off the top of my
>>> head, but digging in our heels is not it.
>
>> Well, I'm comfortable digging in my heels against doing *lame* hints
>> just because "it's what all the other kids are doing," which I think
>> is the only thing which would have satisfied the OP on this thread.
>
> Right.  If someone comes up with a design that avoids the serious
> pitfalls of traditional hinting schemes, that'd be great.  But I'm
> not interested in implementing Oracle-like hints just because Oracle
> has them, which I think was basically what the OP wanted.  I haven't
> seen a hinting scheme that didn't suck (and that includes the aspects
> of our own current behavior that are hint-like).  I don't say that
> there can't be one.
>
> I believe that the FAQ entry is meant to answer people who come along
> and say "oh, this is easily solved, just do what $PRODUCT does".  The
> generic answer to that is "no, it's not that easy".  But maybe the FAQ
> should be rephrased to be more like "we don't want traditional hints
> because of problems X, Y, and Z.  If you have an idea that avoids those
> problems, let us know."

That's closer to where I think the community is on this issue, for sure.

Frankly, I think we should also have some much better documentation
about how to fix problems in the optimizer.  Before the OP went off on
a rant, he actually showed up at a webinar I did looking for advice on
how to fix queries in PG, which wasn't exactly the topic of the
webinar, so he didn't get his answer.  But the only way you're going
to find out about a lot of the tricks that we rely on is to read the
mailing lists, and that's below our usual standard of documentation.
Sure, it's a bunch of ugly hacks, but they're useful when you're being
eaten by a crocodile, and the need for them isn't limited to people
who have time to spend all day reading pgsql-whatever.

I also think that we have enough knowledge between us to identify the
areas where some better hints, or hint-ish mechanisms, would actually
be useful.  I feel like I have a pretty good idea where the bodies are
buried, and what some of the solutions might look like.  But I'm not
sure I want to open that can of worms while we're trying to close out
this CommitFest.  In fact I'm pretty sure I don't.  But I would like
to change the Todo text to say something less misleading.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Shaun Thomas <sthomas@peak6.com> wrote:

> how difficult would it be to add that syntax to the JOIN
> statement, for example?

Something like this syntax?:

JOIN WITH (correlation_factor=0.3)

Where 1.0 might mean that for each value on the left there was only
one distinct value on the right, and 0.0 would mean that they were
entirely independent?  (Just as an off-the-cuff example -- I'm not
at all sure that this makes sense, let alone is the best thing to
specify.  I'm trying to get at *syntax* here, not particular knobs.)

-Kevin

Robert Haas <robertmhaas@gmail.com> wrote:

>> maybe the FAQ should be rephrased to be more like "we don't want
>> traditional hints because of problems X, Y, and Z.  If you have
>> an idea that avoids those problems, let us know."
>
> That's closer to where I think the community is on this issue

That sounds pretty good to me.

-Kevin

On 02/10/2011 11:21 AM, Kevin Grittner wrote:

> Something like this syntax?:
>
> JOIN WITH (correlation_factor=0.3)

I was thinking more:

JOIN foo_tab USING (foo_id) WITH (COST=50)

or something, to exploit the hooks that already exist for functions, for
example. But it's still an interesting concept. Tell the optimizer what
you want and how the data is really related in cases where it's wrong,
and let it figure out the best path.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com

______________________________________________

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email

Shaun Thomas <sthomas@peak6.com> wrote:

> I was thinking more:
>
> JOIN foo_tab USING (foo_id) WITH (COST=50)

The problem I have with that syntax is that it would be hard to read
when you have some nested set of joins or a (SELECT) in the JOIN
instead of simple table name.  For me, at least, it would "get lost"
less easily if it were right next to the JOIN keyword.

The problem with a COST factor is that it's not obvious to me what
it would apply to:
 - each row on the left?
 - each row on the right?
 - each row in the result of the JOIN step?
 - the entire step?

How would it scale based on other criteria which affected the number
of rows on either side of the join?

If I'm understanding the problem correctly, the part the optimizer
gets wrong (because we don't yet have statistics to support a better
assumption) is assuming that selection criteria on opposite sides of
a join affect entirely independent sets of what would be in the
result without the criteria.  To use an oft-cited example, when one
table is selected by zip code and the other by city, that's a bad
assumption about the correlation, leading to bad estimates, leading
to bad costing, leading to bad plans.  The OP wanted to override
step 4, a COST setting would try to override step 3, but I think we
would want to override step 1 (until we get statistics which let us
compute that accurately).

-Kevin

On Fri, Feb 4, 2011 at 8:50 PM, Mark Mielke <mark@mark.mielke.cc> wrote:
> On 02/04/2011 10:41 AM, Tom Lane wrote:
>>
>> 1. Autovacuum fires when the stats collector's insert/update/delete
>> counts have reached appropriate thresholds.  Those counts are
>> accumulated from messages sent by backends at transaction commit or
>> rollback, so they take no account of what's been done by transactions
>> still in progress.
>>
>> 2. Only live rows are included in the stats computed by ANALYZE.
>> (IIRC it uses SnapshotNow to decide whether rows are live.)
>>
>> Although the stats collector does track an estimate of the number of
>> dead rows for the benefit of autovacuum, this isn't used by planning.
>> Table bloat is accounted for only in terms of growth of the physical
>> size of the table in blocks.
>
> Thanks, Tom.
>
> Does this un-analyzed "bloat" not impact queries? I guess the worst case
> here is if autovaccum is disabled for some reason and 99% of the table is
> dead rows. If I understand the above correctly, I think analyze might
> generate a bad plan under this scenario, thinking that a value is unique,
> using the index - but every tuple in the index has the same value and each
> has to be looked up in the table to see if it is visible?

It sounds like you're describing something like a one-row table with a
unique index on one of its column, getting updates that can't be made
HOT, and not getting vacuumed.  That scenario does suck - I had a test
case I was using it a while back that generated something similar -
but I'm not sure how much it's worth worrying about the plan, because
either an index scan or a sequential scan is going to be awful.

To put that another way, I've founded that the optimizer copes pretty
well with adjusting plans as tables get bloated - mostly by using
index scans rather than sequential scans.  It's possible there is some
improvement still to be had there, but I would be a lot more
interested in fixing the bloat, at least based on my own experiences.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Shaun Thomas wrote:
> Hilariously, I'm not so sure that's what the OP wanted.

Someone to blame as a scapegoat for why his badly planned project had
failed.  I've done several Oracle conversions before, and never met
someone who was so resistent to doing the right things for such a
conversion.  You have to relatively flexible in your thinking to work
with the good and away from the bad parts of PostgreSQL for such a
project to succeed.  I didn't hear a whole lot of "flexible" in that
discussion.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

robertmhaas@gmail.com (Robert Haas) writes:
> On Thu, Feb 10, 2011 at 11:45 AM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov> wrote:
>> Well, I'm comfortable digging in my heels against doing *lame* hints
>> just because "it's what all the other kids are doing," which I think
>> is the only thing which would have satisfied the OP on this thread.
>> From both on-list posts and ones exchanged off-list with me, it
>> seems he was stubbornly resistant to properly tuning the server to
>> see if any problems remained, or posting particular problems to see
>> how they would be most effectively handled in PostgreSQL.  We
>> obviously can't be drawn into dumb approaches because of
>> ill-informed demands like that.
>
> Nor was I proposing any such thing.  But that doesn't make "we don't
> want hints" an accurate statement.  Despite the impression that OP
> went away with, the real situation is a lot more nuanced than that,
> and the statement on the Todo list gives the wrong impression, IMHO.

I have added the following comment to the ToDo:

   We are not interested to implement hints in ways they are commonly
   implemented on other databases, and proposals based on "because
   they've got them" will not be welcomed.  If you have an idea that
   avoids the problems that have been observed with other hint systems,
   that could lead to valuable discussion.

That seems to me to characterize the nuance.
--
let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];;
http://www3.sympatico.ca/cbbrowne/languages.html
If only women came with pull-down menus and online help.

Greg Smith <greg@2ndquadrant.com> wrote:
> Shaun Thomas wrote:
>> Hilariously, I'm not so sure that's what the OP wanted.
>
> Someone to blame as a scapegoat for why his badly planned project
> had failed.  I've done several Oracle conversions before, and
> never met someone who was so resistent to doing the right things
> for such a conversion.  You have to relatively flexible in your
> thinking to work with the good and away from the bad parts of
> PostgreSQL for such a project to succeed.  I didn't hear a whole
> lot of "flexible" in that discussion.

I was thinking along the same lines, but couldn't find the words to
put it so politely, so I held back.  Still biting my tongue, but I
appreciate your milder summary.

-Kevin

On 2/10/11 9:21 AM, Kevin Grittner wrote:
> Shaun Thomas<sthomas@peak6.com>  wrote:
>
>> how difficult would it be to add that syntax to the JOIN
>> statement, for example?
>
> Something like this syntax?:
>
> JOIN WITH (correlation_factor=0.3)
>
> Where 1.0 might mean that for each value on the left there was only
> one distinct value on the right, and 0.0 would mean that they were
> entirely independent?  (Just as an off-the-cuff example -- I'm not
> at all sure that this makes sense, let alone is the best thing to
> specify.  I'm trying to get at *syntax* here, not particular knobs.)

There are two types of problems:

1. The optimizer is imperfect and makes a sub-optimal choice.

2. There is theoretical reasons why it's hard for the optimizer. For example, in a table with 50 columns, there is a
staggeringnumber of possible correlations.  An optimizer can't possibly figure this out, but a human might know them
fromthe start.  The City/Postal-code correlation is a good example. 

For #1, Postgres should never offer any sort of hint mechanism.  As many have pointed out, it's far better to spend the
timefixing the optimizer than adding hacks. 

For #2, it might make sense to give a designer a way to tell Postgres stuff that it couldn't possibly figure out. But
...not until the problem is clearly defined. 

What should happen is that someone writes with an example query, and the community realizes that no amount of
clevernessfrom Postgres could ever solve it (for solid theoretical reasons). Only then, when the problem is clearly
defined,should we talk about solutions and SQL extensions. 

Craig

On 4 February 2011 04:46, Josh Berkus <josh@agliodbs.com> wrote:
> "Optimizer hints are used to work around problems in the optimizer and
> introduce upgrade and maintenance issues. We would rather have the
> problems reported and fixed. We have discussed a more sophisticated
> system of per-class cost adjustment instead, but a specification remains
> to be developed."

I have no clue about how hints works in Oracle ... I've never been
working "enterprise level" on anything else than Postgres.  Anyway,
today I just came over an interesting problem in our production
database today - and I think it would be a benefit to be able to
explicitly tell the planner what index to use (the dev team is adding
redundant attributes and more indexes to solve the problem - which
worries me, because we will run into serious problems as soon as there
won't be enough memory for all the frequently-used indexes).

We have users and transactions, and we have transaction types.  The
transaction table is huge.  The users are able to interactively check
their transaction listings online, and they have some simple filter
options available as well.  Slightly simplified, the queries done
looks like this:

   select * from account_transaction where account_id=? order by
created desc limit 25;

   select * from account_transaction where trans_type_id in ( ...
long, hard-coded list ...) and account_id=? order by created desc
limit 25;

and we have indexes on:

   account_transaction(account_id, created)

   account_transaction(account_id, trans_type_id, created)

(At this point, someone would probably suggest to make three
single-key indexes and use bitmap index scan ... well, pulling 25 rows
from the end of an index may be orders of magnitude faster than doing
bitmap index mapping on huge indexes)

For the second query, the planner would chose the first index - and
maybe it makes sense - most of our customers have between 10-30% of
the transactions from the long list of transaction types, slim indexes
are good and by average the slimmer index would probably do the job a
bit faster.  The problem is with the corner cases - for some of our
extreme customers thousands of transaction index tuples may need to be
scanned before 25 rows with the correct transaction type is pulled
out, and if the index happens to be on disk, it may take tens of
seconds to pull out the answer.  Tens of seconds of waiting leads to
frustration, it is a lot nowadays in an interactive session.  Also, I
haven't really checked it up, but it may very well be that this is
exactly the kind of customers we want to retain.

To summarize, there are two things the planner doesn't know - it
doesn't know that there exists such corner cases where the real cost
is far larger than the estimated cost, and it doesn't know that it's
more important to keep the worst-case cost on a reasonable level than
to minimize the average cost.  In the ideal world postgres would have
sufficiently good statistics to know that for user #77777 it is better
to chose the second index, but I suppose it would be easier if I was
able to explicitly hide the account_transaction(account_id, created)
index for this query.  Well, I know of one way to do it ... but I
suppose it's not a good idea to put "drop index foo; select ...;
rollback;" into production ;-)

Tobias Brox <tobixen@gmail.com> writes:
> I have no clue about how hints works in Oracle ... I've never been
> working "enterprise level" on anything else than Postgres.  Anyway,
> today I just came over an interesting problem in our production
> database today - and I think it would be a benefit to be able to
> explicitly tell the planner what index to use (the dev team is adding
> redundant attributes and more indexes to solve the problem - which
> worries me, because we will run into serious problems as soon as there
> won't be enough memory for all the frequently-used indexes).

> We have users and transactions, and we have transaction types.  The
> transaction table is huge.  The users are able to interactively check
> their transaction listings online, and they have some simple filter
> options available as well.  Slightly simplified, the queries done
> looks like this:

>    select * from account_transaction where account_id=? order by
> created desc limit 25;

>    select * from account_transaction where trans_type_id in ( ...
> long, hard-coded list ...) and account_id=? order by created desc
> limit 25;

> and we have indexes on:

>    account_transaction(account_id, created)

>    account_transaction(account_id, trans_type_id, created)

Well, in this case the optimizer *is* smarter than you are, and the
reason is that it remembers the correct rules for when indexes are
useful.  That second index is of no value for either query, because
"in" doesn't work the way you're hoping.

I understand the larger point you're trying to make, but this example
also nicely illustrates the point being made on the other side, that
"force the optimizer to use the index I think it should use" isn't a
very good solution.

            regards, tom lane

2011/2/11 Віталій Тимчишин <tivv00@gmail.com>:
> If the list is hard-coded, you can create partial index  on
> account_transaction(account_id, created desc) where trans_type_id in ( ...
> long, hard-coded list ...)

My idea as well, though it looks ugly and it would be a maintenance
head-ache (upgrading the index as new transaction types are added
would mean "costly" write locks on the table, and we can't rely on
manual processes to get it right ... we might need to set up scripts
to either upgrade the index or alert us if the index needs upgrading).

11.02.11 11:29, Tobias Brox написав(ла):
> 2011/2/11 Віталій Тимчишин<tivv00@gmail.com>:
>> If the list is hard-coded, you can create partial index  on
>> account_transaction(account_id, created desc) where trans_type_id in ( ...
>> long, hard-coded list ...)
> My idea as well, though it looks ugly and it would be a maintenance
> head-ache (upgrading the index as new transaction types are added
> would mean "costly" write locks on the table,
Create new one concurrently.
>   and we can't rely on
> manual processes to get it right ... we might need to set up scripts
> to either upgrade the index or alert us if the index needs upgrading).
Yep. Another option could be to add query rewrite as

select  * from (
select * from account_transaction where trans_type_id =type1 and
account_id=? order by created desc limit 25 union all
select * from account_transaction where trans_type_id =type2 and
account_id=? order by created desc limit 25 union all
...
union all
select * from account_transaction where trans_type_id =typeN and
account_id=? order by created desc limit 25
) a
order by created desc limit 25

This will allow to use three-column index in the way it can be used for
such query. Yet if N is large query will look ugly. And I am not sure if
optimizer is smart enough for not to fetch 25*N rows.


Best regards, Vitalii Tymchyshyn

2011/2/11 Vitalii Tymchyshyn <tivv00@gmail.com>:
>> My idea as well, though it looks ugly and it would be a maintenance
>> head-ache (upgrading the index as new transaction types are added
>> would mean "costly" write locks on the table,
>
> Create new one concurrently.

Concurrently?  Are there any ways to add large indexes without
blocking inserts to the table for the time it takes to create the
index?

> Yep. Another option could be to add query rewrite as
>
> select  * from (
> select * from account_transaction where trans_type_id =type1 and
> account_id=? order by created desc limit 25 union all
> select * from account_transaction where trans_type_id =type2 and
> account_id=? order by created desc limit 25 union all
> ...
> union all
> select * from account_transaction where trans_type_id =typeN and
> account_id=? order by created desc limit 25
> ) a
> order by created desc limit 25

I actually considered that.  For the test case given it works very
fast.  Not sure if it would work universally ... it scales well when
having extreme amounts of transactions outside the given transaction
list (the case we have problems with now), but it wouldn't scale if
some user has an extreme amount of transactions within the list.
However,  I think our "extreme amount of transactions"-problem is
mostly limited to the transaction types outside the list.

On 02/11/2011 12:26 PM, Tobias Brox wrote:
> 2011/2/11 Vitalii Tymchyshyn<tivv00@gmail.com>:
>>> My idea as well, though it looks ugly and it would be a maintenance
>>> head-ache (upgrading the index as new transaction types are added
>>> would mean "costly" write locks on the table,
>>
>> Create new one concurrently.
>
> Concurrently?  Are there any ways to add large indexes without
> blocking inserts to the table for the time it takes to create the
> index?

yep, AFAIR since 8.2
see: http://www.postgresql.org/docs/8.2/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

[cut]

Andrea

>    select * from account_transaction where trans_type_id in ( ...
> long, hard-coded list ...) and account_id=? order by created desc
> limit 25;

You could use an index on (account_id, created, trans_type), in
replacement of your index on (account_id, created). This will not prevent
the "Index Scan Backwards", but at least, index rows with trans_type not
matching the WHERE clause will not generate any heap access...

On Thu, Feb 10, 2011 at 9:25 AM, Chris Browne <cbbrowne@acm.org> wrote:
> robertmhaas@gmail.com (Robert Haas) writes:
>> On Thu, Feb 10, 2011 at 11:45 AM, Kevin Grittner
>> <Kevin.Grittner@wicourts.gov> wrote:
>>> Well, I'm comfortable digging in my heels against doing *lame* hints
>>> just because "it's what all the other kids are doing," which I think
>>> is the only thing which would have satisfied the OP on this thread.
>>> From both on-list posts and ones exchanged off-list with me, it
>>> seems he was stubbornly resistant to properly tuning the server to
>>> see if any problems remained, or posting particular problems to see
>>> how they would be most effectively handled in PostgreSQL.  We
>>> obviously can't be drawn into dumb approaches because of
>>> ill-informed demands like that.
>>
>> Nor was I proposing any such thing.  But that doesn't make "we don't
>> want hints" an accurate statement.  Despite the impression that OP
>> went away with, the real situation is a lot more nuanced than that,
>> and the statement on the Todo list gives the wrong impression, IMHO.
>
> I have added the following comment to the ToDo:
>
>   We are not interested to implement hints in ways they are commonly
>   implemented on other databases, and proposals based on "because
>   they've got them" will not be welcomed.  If you have an idea that
>   avoids the problems that have been observed with other hint systems,
>   that could lead to valuable discussion.
>
> That seems to me to characterize the nuance.


Where exactly are the problems with other systems noted? Most other
systems have this option so saying "They have problems" is a giant cop
out.


--
Rob Wultsch
wultsch@gmail.com

I've wordsmithed Chris's changes some, and then spun off a completely
separate page for Hints discussion, since the NotToDo item was becoming
too long.

> Something like this syntax?:
>
> JOIN WITH (correlation_factor=0.3)

Please, NO!

This is exactly the kind of hint that I regard as a last resort if we
run out of implementation alternatives.  Any hint which gets coded into
the actual queries becomes a *massive* maintenance and upgrade headache
thereafter.  If we're implementing a hint alternative, we should look at
stuff in this priority order:

1. Useful tuning of additional cost parameters by GUC (i.e.
cursor_tuple_fraction)
2. Modifying cost parameters on database *objects* (i.e. "ndistinct=500")
3. Adding new parameters to modify on database objects (i.e.
"distribution=normal(1.5,17)","new_rows=0.1")
4. Query hints (if all of the above fails to give fixes for some tested
problem)

> Where exactly are the problems with other systems noted? Most other
> systems have this option so saying "They have problems" is a giant cop
> out.

I've put my list down:
http://wiki.postgresql.org/wiki/OptimizerHintsDiscussion#Problems_with_existing_Hint_stystems

--
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com

On Sun, Feb 13, 2011 at 3:29 PM, Josh Berkus <josh@agliodbs.com> wrote:
> I've wordsmithed Chris's changes some, and then spun off a completely
> separate page for Hints discussion, since the NotToDo item was becoming
> too long.
>
>> Something like this syntax?:
>>
>> JOIN WITH (correlation_factor=0.3)
>
> Please, NO!
>
> This is exactly the kind of hint that I regard as a last resort if we
> run out of implementation alternatives.  Any hint which gets coded into
> the actual queries becomes a *massive* maintenance and upgrade headache
> thereafter.  If we're implementing a hint alternative, we should look at
> stuff in this priority order:
>
> 1. Useful tuning of additional cost parameters by GUC (i.e.
> cursor_tuple_fraction)
> 2. Modifying cost parameters on database *objects* (i.e. "ndistinct=500")
> 3. Adding new parameters to modify on database objects (i.e.
> "distribution=normal(1.5,17)","new_rows=0.1")
> 4. Query hints (if all of the above fails to give fixes for some tested
> problem)

I fail to see how 1 through 3 can tell the planner the correlation
between two fields in two separate tables.

On Sun, Feb 13, 2011 at 10:49 PM, Josh Berkus <josh@agliodbs.com> wrote:
>
>> I fail to see how 1 through 3 can tell the planner the correlation
>> between two fields in two separate tables.
>
> CREATE CORRELATION_ESTIMATE ( table1.colA ) = ( table2.colB ) IS 0.3
>
> ... and then it fixes the correlation for *every* query in the database, not
> just that one.  And is easy to fix if the correlation changes.

I like that.  Even better, could we setup some kind of simple command
to tell analyze to collect stats for the two columns together?

Kevin Grittner wrote:
> Shaun Thomas <sthomas@peak6.com> wrote:
>
> > how difficult would it be to add that syntax to the JOIN
> > statement, for example?
>
> Something like this syntax?:
>
> JOIN WITH (correlation_factor=0.3)
>
> Where 1.0 might mean that for each value on the left there was only
> one distinct value on the right, and 0.0 would mean that they were
> entirely independent?  (Just as an off-the-cuff example -- I'm not
> at all sure that this makes sense, let alone is the best thing to
> specify.  I'm trying to get at *syntax* here, not particular knobs.)

I am not excited about the idea of putting these correlations in
queries.  What would be more intesting would be for analyze to build a
correlation coeffficent matrix showing how columns are correlated:

    a   b   c
    a   1   .4  0
    b   .1  1   -.3
    c   .2  .3  1

and those correlations could be used to weigh how the single-column
statistics should be combined.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

On Wed, Feb 16, 2011 at 4:22 PM, Bruce Momjian <bruce@momjian.us> wrote:
> I am not excited about the idea of putting these correlations in
> queries.  What would be more intesting would be for analyze to build a
> correlation coeffficent matrix showing how columns are correlated:
>
>        a   b   c
>    a   1   .4  0
>    b   .1  1   -.3
>    c   .2  .3  1
>
> and those correlations could be used to weigh how the single-column
> statistics should be combined.

If you can make it work, I'll take it... it's (much) easier said than
done, though.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi. I have the idea: hints joined to function. For example instead of

WHERE table1.field1=table2.field2
write:
WHERE specificeq(table1.field1,table2.field2)

and hints add to declaration of specificeq function.

2011/2/23, Robert Haas <robertmhaas@gmail.com>:
> On Wed, Feb 16, 2011 at 4:22 PM, Bruce Momjian <bruce@momjian.us> wrote:
>> I am not excited about the idea of putting these correlations in
>> queries.  What would be more intesting would be for analyze to build a
>> correlation coeffficent matrix showing how columns are correlated:
>>
>>        a   b   c
>>    a   1   .4  0
>>    b   .1  1   -.3
>>    c   .2  .3  1
>>
>> and those correlations could be used to weigh how the single-column
>> statistics should be combined.
>
> If you can make it work, I'll take it... it's (much) easier said than
> done, though.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>


--
------------
pasman

On Thu, Feb 10, 2011 at 7:32 PM, Craig James <craig_james@emolecules.com> wrote:
> On 2/10/11 9:21 AM, Kevin Grittner wrote:
>>
>> Shaun Thomas<sthomas@peak6.com>  wrote:
>>
>>> how difficult would it be to add that syntax to the JOIN
>>> statement, for example?
>>
>> Something like this syntax?:
>>
>> JOIN WITH (correlation_factor=0.3)
>>
>> Where 1.0 might mean that for each value on the left there was only
>> one distinct value on the right, and 0.0 would mean that they were
>> entirely independent?  (Just as an off-the-cuff example -- I'm not
>> at all sure that this makes sense, let alone is the best thing to
>> specify.  I'm trying to get at *syntax* here, not particular knobs.)
>
> There are two types of problems:
>
> 1. The optimizer is imperfect and makes a sub-optimal choice.
>
> 2. There is theoretical reasons why it's hard for the optimizer. For
> example, in a table with 50 columns, there is a staggering number of
> possible correlations.  An optimizer can't possibly figure this out, but a
> human might know them from the start.  The City/Postal-code correlation is a
> good example.
>
> For #1, Postgres should never offer any sort of hint mechanism.  As many
> have pointed out, it's far better to spend the time fixing the optimizer
> than adding hacks.
>
> For #2, it might make sense to give a designer a way to tell Postgres stuff
> that it couldn't possibly figure out. But ... not until the problem is
> clearly defined.
>
> What should happen is that someone writes with an example query, and the
> community realizes that no amount of cleverness from Postgres could ever
> solve it (for solid theoretical reasons). Only then, when the problem is
> clearly defined, should we talk about solutions and SQL extensions.

I don't have one such query handy.  However, I think your posting is a
good starting point for a discussion how to figure out what we need
and how a good solution could look like.  For example, one thing I
dislike about hints is that they go into the query.  There are a few
drawbacks of this approach

- Applications need to be changed to benefit which is not always possible.
- One important class of such applications are those that use OR
mappers - hinting then would have to be buried in OR mapper code or
configuration.
- Hints in the query work only for exactly that query (this might be
an advantage depending on point of view).

I think the solution should rather be to tell Postgres what "it
couldn't possibly figure out".  I imagine that could be some form of
description of the distribution of data in columns and / or
correlations between columns.  Advantage would be that the optimizer
gets additional input which it can use (i.e. the usage can change
between releases), the information is separate from queries (more like
meta data for tables) and thus all queries using a particular table
which was augmented with this meta data would benefit.  Usage of this
meta data could be controlled by a flag per session (as well as
globally) so it would be relatively easy to find out whether this meta
data has become obsolete (because data changed or a new release of the
database is in use).

Kind regards

robert


--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/