Quantcast

Re: [HACKERS] [PERFORM] Slow count(*) again...

classic Classic list List threaded Threaded
156 messages Options
123456 ... 8
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [HACKERS] [PERFORM] Slow count(*) again...

Chris Browne
[hidden email] (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.

--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [HACKERS] [PERFORM] Slow count(*) again...

Tom Lane-2
In reply to this post by Robert Haas
Robert Haas <[hidden email]> 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

--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [HACKERS] [PERFORM] Slow count(*) again...

Greg Smith-21
In reply to this post by Mladen Gogala-4
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    [hidden email]   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books


--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [HACKERS] [PERFORM] Slow count(*) again...

Mladen Gogala-4
In reply to this post by Chris Browne
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




--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [HACKERS] [PERFORM] Slow count(*) again...

Ross Reedstrom
In reply to this post by Chris Browne
On Thu, Feb 03, 2011 at 12:44:23PM -0500, Chris Browne wrote:

> [hidden email] (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.                                 [hidden email]
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

--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [HACKERS] [PERFORM] Slow count(*) again...

david@lang.hm
In reply to this post by Robert Haas
On Thu, 3 Feb 2011, Robert Haas wrote:

> On Thu, Feb 3, 2011 at 5:11 AM,  <[hidden email]> 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

--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [HACKERS] [PERFORM] Slow count(*) again...

Mladen Gogala-4
In reply to this post by Shaun Thomas
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




--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [HACKERS] [PERFORM] Slow count(*) again...

Chris Browne
[hidden email] (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/>

--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [HACKERS] [PERFORM] Slow count(*) again...

Kevin Grittner
In reply to this post by Mladen Gogala-4
Mladen Gogala <[hidden email]> 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

--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [HACKERS] [PERFORM] Slow count(*) again...

Shaun Thomas
In reply to this post by Mladen Gogala-4
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
[hidden email]

______________________________________________

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

--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [HACKERS] [PERFORM] Slow count(*) again...

Mladen Gogala-4
In reply to this post by Chris Browne
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




--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [HACKERS] [PERFORM] Slow count(*) again...

Mark Kirkwood-2
In reply to this post by Mladen Gogala-4
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

--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [HACKERS] [PERFORM] Slow count(*) again...

Mladen Gogala-4
In reply to this post by Shaun Thomas
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




--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [HACKERS] [PERFORM] Slow count(*) again...

Ben Chobot
In reply to this post by Mladen Gogala-4
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 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.

As always, you should use the tool you consider best for the job. If you think MySQL as both a product and a community has a better chance of giving you what you want, then you should use MySQL.
--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [HACKERS] [PERFORM] Slow count(*) again...

Pavel Stehule
In reply to this post by Mladen Gogala-4
2011/2/3 Mladen Gogala <[hidden 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?
>

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 ([hidden email])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [HACKERS] [PERFORM] Slow count(*) again...

Josh Berkus
In reply to this post by Chris Browne
On 2/3/11 1:18 PM, Chris Browne wrote:
> [hidden email] (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

--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [HACKERS] [PERFORM] Slow count(*) again...

Maciek Sakrejda
In reply to this post by Mladen Gogala-4
> 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

--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [HACKERS] [PERFORM] Slow count(*) again...

Pavel Stehule
In reply to this post by Mladen Gogala-4
> 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 ([hidden email])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [HACKERS] [PERFORM] Slow count(*) again...

Mladen Gogala-4
In reply to this post by Josh Berkus
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




--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [HACKERS] [PERFORM] Slow count(*) again...

Mladen Gogala-4
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




--
Sent via pgsql-performance mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
123456 ... 8
Loading...