Re: [PERFORM] Hints proposal - Mailing list pgsql-hackers

From Tom Lane
Subject Re: [PERFORM] Hints proposal
Date
Msg-id 10866.1160667752@sss.pgh.pa.us
Whole thread Raw
Responses Re: [PERFORM] Hints proposal  ("Merlin Moncure" <mmoncure@gmail.com>)
Re: [PERFORM] Hints proposal  ("Jim C. Nasby" <jim@nasby.net>)
Re: [PERFORM] Hints proposal  (Csaba Nagy <nagy@ecircle-ag.com>)
List pgsql-hackers
[ This is off-topic for -performance, please continue the thread in
-hackers ]

"Jim C. Nasby" <jim@nasby.net> writes:
> These hints would outright force the planner to do things a certain way.
> ... FROM table /* ACCESS {SEQSCAN | [[NO] BITMAP] INDEX index_name} */

This proposal seems to deliberately ignore every point that has been
made *against* doing things that way.  It doesn't separate the hints
from the queries, it doesn't focus on fixing the statistical or cost
misestimates that are at the heart of the issue, and it takes no account
of the problem of hints being obsoleted by system improvements.

> It would also be useful to allow tweaking of planner cost estimates.
> This would take the general form of
> node operator value

This is at least focusing on the right sort of thing, although I still
find it completely misguided to be attaching hints like this to
individual queries.

What I would like to see is information *stored in a system catalog*
that affects the planner's cost estimates.  As an example, the DBA might
know that a particular table is touched sufficiently often that it's
likely to remain RAM-resident, in which case reducing the page fetch
cost estimates for just that table would make sense.  (BTW, this is
something the planner could in principle know, but we're unlikely to
do it anytime soon, for a number of reasons including a desire for plan
stability.)  The other general category of thing I think we need is a
way to override selectivity estimates for particular forms of WHERE
clauses.

            regards, tom lane

pgsql-hackers by date:

Previous
From: "Hiroshi Saito"
Date:
Subject: Re: [PATCHES] warning: "min" redefined of qsort.
Next
From: Weslee Bilodeau
Date:
Subject: Getting the type Oid in a CREATE TYPE output function ..