Re: Hints - experiences from other rdbms - Mailing list pgsql-performance

From Willem Leenen
Subject Re: Hints - experiences from other rdbms
Date
Msg-id DUB104-W33099C0518BEC1488C794D8F540@phx.gbl
Whole thread Raw
In response to Re: Hints (was Poor performance using CTE)  (Heikki Linnakangas <hlinnakangas@vmware.com>)
List pgsql-performance


>
> Rather than telling the planner what to do or not to do, I'd much rather
> have hints that give the planner more information about the tables and
> quals involved in the query. A typical source of bad plans is when the
> planner gets its cost estimates wrong. So rather than telling the
> planner to use a nested loop join for "a INNER JOIN b ON a.id = b.id",
> the user could tell the planner that there are only 10 rows that match
> the "a.id = b.id" qual.



Instead of gathering statistics for all possible joins ( and join orders) , in Oracle there is a functionality that can be switched on where the optimizer is given cardinality feedback for the chosen plans, so it can choose another plan if the same statement comes around.

Secondly, there is functionality to insert a hint into an SQL statement. That's very good for COTS apps where the statement can't be altered. Now I know that there's relatively not much COTS for the Postgresql, ( hence arguments like 'we should not implement hints so we're forcing people to solve the underlying problem' ), but as Postgresql will replace oracle in the lower end of the market, this functionality is usefull.


pgsql-performance by date:

Previous
From: Andreas Kretschmer
Date:
Subject: Re: Hints (was Poor performance using CTE)
Next
From: Andrew Dunstan
Date:
Subject: Re: Poor performance using CTE