Re: [PERFORM] Hints proposal

From: Tom Lane
Subject: Re: [PERFORM] Hints proposal
Date: ,
Msg-id: 10866.1160667752@sss.pgh.pa.us
(view: Whole thread, Raw)
Responses: Re: [PERFORM] Hints proposal  ("Merlin Moncure")
Re: [PERFORM] Hints proposal  ("Jim C. Nasby")
Re: [PERFORM] Hints proposal  (Csaba Nagy)
List: pgsql-hackers

Tree view

Re: [PERFORM] Hints proposal  (Tom Lane, )
 Re: [PERFORM] Hints proposal  ("Merlin Moncure", )
 Re: [PERFORM] Hints proposal  ("Jim C. Nasby", )
  Re: [PERFORM] Hints proposal  (Tom Lane, )
  Re: [PERFORM] Hints proposal  (Josh Berkus, )
   Re: [PERFORM] Hints proposal  ("Jim C. Nasby", )
    Re: [PERFORM] Hints proposal  (Josh Berkus, )
     Re: [PERFORM] Hints proposal  ("Bucky Jordan", )
      Re: [PERFORM] Hints proposal  (Alvaro Herrera, )
       Re: [PERFORM] Hints proposal  (Jeff Davis, )
        Re: [PERFORM] Hints proposal  ("Bucky Jordan", )
         Re: [PERFORM] Hints proposal  (Jeff Davis, )
          Re: [PERFORM] Hints proposal  ("Jim C. Nasby", )
           Re: [PERFORM] Hints proposal  ("Joshua D. Drake", )
            Re: [PERFORM] Hints proposal  (Andrew Dunstan, )
           Re: [PERFORM] Hints proposal  (David Fetter, )
           Re: [PERFORM] Hints proposal  (Jeff Davis, )
           Re: [PERFORM] Hints proposal  (Stefan Kaltenbrunner, )
     Re: [PERFORM] Hints proposal  ("Jim C. Nasby", )
      Re: [PERFORM] Hints proposal  (Tom Lane, )
      Re: [PERFORM] Hints proposal  ("Bucky Jordan", )
      Re: [PERFORM] Hints proposal  (Josh Berkus, )
       Re: [PERFORM] Hints proposal  ("Jim C. Nasby", )
        Re: [PERFORM] Hints proposal  (Alvaro Herrera, )
        Re: [PERFORM] Hints proposal  (Tom Lane, )
     Re: [PERFORM] Hints proposal  (Gregory Stark, )
      Re: [PERFORM] Hints proposal  (Robert Treat, )
   Re: [PERFORM] Hints proposal  ("Zeugswetter Andreas ADI SD", )
    Re: [PERFORM] Hints proposal  (Josh Berkus, )
  Re: [PERFORM] Hints proposal  (Andrew Sullivan, )
   Re: [PERFORM] Hints proposal  ("Merlin Moncure", )
    Re: [PERFORM] Hints proposal  (Andrew Sullivan, )
   Re: [PERFORM] Hints proposal  ("Zeugswetter Andreas ADI SD", )
    Re: [PERFORM] Hints proposal  (Andrew Sullivan, )
     Re: [PERFORM] Hints proposal  (Csaba Nagy, )
      Re: [PERFORM] Hints proposal  (Josh Berkus, )
     Re: [PERFORM] Hints proposal  ("Zeugswetter Andreas ADI SD", )
 Re: [PERFORM] Hints proposal  (Csaba Nagy, )
  Re: [PERFORM] Hints proposal  (Josh Berkus, )
   Re: [PERFORM] Hints proposal  (Csaba Nagy, )
  Re: [PERFORM] Hints proposal  (Tom Lane, )

[ This is off-topic for -performance, please continue the thread in
-hackers ]

"Jim C. Nasby" <> 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:

From: Bruce Momjian
Date:
Subject: Re: create temp table .. on commit delete rows
From: Alvaro Herrera
Date:
Subject: Re: [PERFORM] Hints proposal