From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Bruce Momjian <bruce@momjian.us>
Cc: Jim C. Nasby <jim@nasby.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Hints proposal
Date: Thu, 12 Oct 2006 16:55:17 +0100
Message-ID: <452E6565.4010400@enterprisedb.com> (raw)
In-Reply-To: <200610121519.k9CFJA416544@momjian.us>
References: <200610121519.k9CFJA416544@momjian.us>
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.
Because DB2 doesn't have MVCC, an accidental table scan is very serious,
because with stricter isolation levels that keeps the whole table locked.
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
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.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
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: heikki@enterprisedb.com, bruce@momjian.us, jim@nasby.net
Subject: Re: Hints proposal
In-Reply-To: <452E6565.4010400@enterprisedb.com>
* 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