Hicham G. Elmongui wrote:
> Is there a way to bypass the optimizer and to specify a plan to be executed?
>
> Something like:
>
>
>
> Limit
> -> Nested Loop
> -> Nested Loop
> -> Seq Scan on tab00 t00
> -> Index Scan using tab03_pkey on tab03 t03
> Index Cond: ("outer".id = t03.id)
> -> Index Scan using tab01_pkey on tab01 t01
> Index Cond: ("outer".id = t01.id)

No, we feel that is of limited value. If the optimizer isn't doing
things properly, we will fix it.

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

> On Thu, 2005-02-10 at 14:37 -0500, Bruce Momjian wrote:
>> No, we feel that is of limited value. If the optimizer isn't doing
>> things properly, we will fix it.
>
> I agree that improving the optimizer is the right answer for normal
> usage, so I can't get excited about query-level plan hints, but I can
> see the capability to instruct the planner being useful in an academic
> context.
>

I think that is sort of arrogant. Look at Oracle, you can give the planner
hints in the form of comments.

The idea that constructing a planner that will always do the best job is
like creating a program that can predict the weather. There are too many
subtle variations in datasets that are impossible to really evalute. I
posted a message last week called "One Big trend vs multiple smaller
trends." and you'll see what I mean.

Yea, on a simple data organization, you could make a great planner, but
someone who has studied the nature of their data can almost always toss
their hands up in frustration because the planner isn't working right.

I have had multiple issues with the inability to guide the planner on its
decisions. I'll give a couple examples:

A music database where the artist name is "Various Artists," given any
normal database of music recordings, "Various Artists" will be *THE* most
popular artist, usually close to almost half the data. Most of the time
I've had to turn off sequential scans for these queries. (I filter out
"various artists") Being able to say:

select * from cdtitles where artist = 'foo' /* index scan
ctitles_artist_ndx */ ;

Would be helpful as disabling sequential scan isn't always the right think
either.

The whole "Query optimizer 8.0.1 (and 8.0)" series of posts show a
different problem.

It all comes down to that the planner *can not* be perfect, and thus will
always be lacking in some respect. This is because you can not anticipate
every physical data storage pattern, therefore, the analyzer will not
correctly characterize them, and the planner will not create an optimal
plan.

Allowing the user to suggest alternate query strategies is a good idea.





---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

> pgsql@mohawksoft.com writes:
>> I think that is sort of arrogant. Look at Oracle, you can give the
>> planner
>> hints in the form of comments.
>
> Arrogant or not, that's the general view of the people who work on the
> planner.
>
> The real issue is not so much whether the planner will always get things
> right --- it won't, and no one imagines that it will ever be perfect.
> The real issue is that we have limited manpower, and designing and
> implementing a useful hint facility is a nontrivial project. (Not to
> mention that maintaining such a thing in the face of frequent,
> fundamental changes to the underlying planner and executor capabilities
> would be an outright nightmare.)
>
> The people who are actually doing the work think their time is more
> usefully spent on improving the planner's intelligence than on devising
> ways to override it.
>

I know I come on strong, and I know I'm probably irritating in many ways,
however, I have been a PostgreSQL user since just after it was named
postgreSQL from Postgres95. I've seen a lot of changes, and almost all of
them have been quite good.

I have over 10 years of using it on various projects. While I have not
been lucky enough to get a gig in which I could contribute more, I do try
to contribute and sometimes it is quite difficult.

The one thing that I think you guys miss is actually using PostgreSQL in
some projects where the company and the deliverables don't give a rat's
ass about whether you use PostgreSQL or Oracle or something else. Over the
years I have beat my head against the walls suggesting features, most of
which eventually have come to PostgreSQL, but every one was a fight.

I have some well founded opinions about PostgreSQL hard earned from real
world situations. Agree or not, I have experience with this database and I
have hit many of its short comings.

One consistent problem is the planner not being able to handle this or
that scenario. At this stage, the *best* way to improve the planner is to
add the ability to place hints in the plan. It *is* good enough for 90% of
the types of queries you would ever want to do. I am dubious that you can
get it demonstrably better in the last 10% or so without making it worse.

Simple hints would go a HUGE way to improving the last 10%. Many of the
"Why doesn't PostgreSQL use my index" questions would go away. Most of the
time Tom spends looking at people's pg_stats info would drop. It would
actually save time.

As a PostgreSQL user, I can tell you with 100% confidence, if I had this
tool, I could do my job easier. I can also tell you that while I have
genuine appreciation for the current quality of the planner, I still would
like to be able to tailor queries specifically to test various approaches
for performance reasons.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Although this is all true, consider that adding hints will mean that the
Pg developers *never* get bug reports to drive the optimizer improvement
process. This will have the effect of stagnating its development. I
think this would be a bad thing :-)

As an aside note that DB2 UDB does not let you hint its optimizer
either...I have heard it argued (by a IBM acquaintance of mine) that
their optimizer is better than that other database's whose name begins
with O, precisely because of this (He is biased of coarse, but it is an
interesting point).

regards

Mark



pgsql@mohawksoft.com wrote:
> One consistent problem is the planner not being able to handle this or
> that scenario. At this stage, the *best* way to improve the planner is to
> add the ability to place hints in the plan. It *is* good enough for 90% of
> the types of queries you would ever want to do. I am dubious that you can
> get it demonstrably better in the last 10% or so without making it worse.
>
> Simple hints would go a HUGE way to improving the last 10%. Many of the
> "Why doesn't PostgreSQL use my index" questions would go away. Most of the
> time Tom spends looking at people's pg_stats info would drop. It would
> actually save time.
>
> As a PostgreSQL user, I can tell you with 100% confidence, if I had this
> tool, I could do my job easier. I can also tell you that while I have
> genuine appreciation for the current quality of the planner, I still would
> like to be able to tailor queries specifically to test various approaches
> for performance reasons.



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

pgsql@mohawksoft.com writes:
> I think that is sort of arrogant. Look at Oracle, you can give the planner
> hints in the form of comments.

Arrogant or not, that's the general view of the people who work on the
planner.

The real issue is not so much whether the planner will always get things
right --- it won't, and no one imagines that it will ever be perfect.
The real issue is that we have limited manpower, and designing and
implementing a useful hint facility is a nontrivial project. (Not to
mention that maintaining such a thing in the face of frequent,
fundamental changes to the underlying planner and executor capabilities
would be an outright nightmare.)

The people who are actually doing the work think their time is more
usefully spent on improving the planner's intelligence than on devising
ways to override it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match