From: Jim C. Nasby <jim@nasby.net>
To: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Hints proposal
Date: Thu, 12 Oct 2006 11:53:27 -0500
Message-ID: <20061012165326.GI28647@nasby.net> (raw)
In-Reply-To: <452E6565.4010400@enterprisedb.com>
References: <200610121519.k9CFJA416544@momjian.us>
<452E6565.4010400@enterprisedb.com>
On Thu, Oct 12, 2006 at 04:55:17PM +0100, Heikki Linnakangas wrote:
> Bruce Momjian wrote:
> >Because DB2 doesn't like hints, and the fact that they have gotten to a
> >point where they feel they do not need them, I feel we too can get to a
> >point where we don't need them either. The question is whether we can
> >get there quickly enough for our userbase.
>
> In all fairness, when I used to work with DB2 we often had to rewrite
> queries to persuade the planner to choose a different plan. Often it was
> more of an issue of plan stability; a query would suddenly become
> horribly slow in production because a table had grown slowly to the
> point that it chose a different plan than before. Then we had to modify
> the query again, or manually set the statistics. In extreme cases we had
> to split a query to multiple parts and use temporary tables and move
> logic to the application to get a query to perform consistently and fast
> enough. I really really missed hints.
Oracle has an interesting way to deal with this, in that you can store a
plan that the optimizer generates and tell it to always use it for that
query. There's some other management tools built on top of that. I don't
know how commonly it's used, though...
Also, on the DB2 argument... I'm wondering what happens when people end
up with a query that they can't get to execute the way it should? Is the
planner *that* good that it never happens? Do you have to wait for a
fixpack when it does happen? I'm all for having a super-smart planner,
but I'm highly doubtful it will always know exactly what to do.
> That said, I really don't like the idea of hints like "use index X"
> embedded in a query. I do like the idea of hints that give the planner
> more information about the data. I don't have a concrete proposal, but
Which is part of the problem... there's nothing to indicate we'll have
support for these improved hints anytime soon, especially if a number of
them depend on plan invalidation.
> here's some examples of hints I'd like to see:
>
> "table X sometimes has millions of records and sometimes it's empty"
> "Expression (table.foo = table2.bar * 2) has selectivity 0.99"
> "if foo.bar = 5 then foo.field2 IS NULL"
> "Column X is unique"
> "function foobar() always returns either 1 or 2, and it returns 2 90% of
> the time."
> "if it's Monday, then table NEW_ORDERS has a cardinality of 100000,
> otherwise 10."
>
> BTW: Do we make use of CHECK constraints in the planner? In DB2, that
> was one nice and clean way of hinting the planner about things. If I
> remember correctly, you could even define CHECK constraints that weren't
> actually checked at run-time, but were used by the planner.
I think you're right... and it is an elegant way to hint the planner.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jim@nasby.net, heikki@enterprisedb.com, bruce@momjian.us
Subject: Re: Hints proposal
In-Reply-To: <20061012165326.GI28647@nasby.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox