Thread: hints in Postgres?
Hi list, I need to know if there is anything like hints of Oracle in Postgres..otherwise..I wish to find a way to force a query plan to use the indexes or tell the optimizer things like "optimize based in statistics", "I want to define the order of the a join" , "optimize based on a execution plan that I consider the best" ... thanks. _________________________________________________________________ Las mejores tiendas, los precios mas bajos, entregas en todo el mundo, YupiMSN Compras: http://latam.msn.com/compras/
hello maybe http://www.gtsm.com/oscon2003/toc.html http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html bye Pavel On Thu, 11 Dec 2003, sandra ruiz wrote: > Hi list, > > I need to know if there is anything like hints of Oracle in > Postgres..otherwise..I wish to find a way to force a query plan to use the > indexes or tell the optimizer things like "optimize based in statistics", "I > want to define the order of the a join" , "optimize based on a execution > plan that I consider the best" ... > > thanks. > > _________________________________________________________________ > Las mejores tiendas, los precios mas bajos, entregas en todo el mundo, > YupiMSN Compras: http://latam.msn.com/compras/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >
Quoth mileruiz@hotmail.com ("sandra ruiz"):
> I need to know if there is anything like hints of Oracle in
> Postgres..otherwise..I wish to find a way to force a query plan to use
> the indexes or tell the optimizer things like "optimize based in
> statistics", "I want to define the order of the a join" , "optimize
> based on a execution plan that I consider the best" ...
It is commonly considered a MISFEATURE of Oracle that it forces you to
tweak all of those sorts of 'knobs.'
The approach taken with PostgreSQL is to use problems discovered to
try to improve the quality of the query optimizer. It is usually
clever enough to do a good job, and if it can be improved to
automatically notice that "better" plan, then that is a better thing
than imposing the burden of tuning each query on you.
Tom Lane is "Doctor Optimization," and if you look at past discussion
threads of this sort, you'll see that he tends to rather strongly
oppose the introduction of "hints."
--
select 'aa454' || '@' || 'freenet.carleton.ca';
http://www3.sympatico.ca/cbbrowne/linux.html
As of next Monday, COMSAT will be flushed in favor of a string and two tin
cans. Please update your software.
On Thu, Dec 11, 2003 at 11:00:19 -0500, sandra ruiz <mileruiz@hotmail.com> wrote: > Hi list, > > I need to know if there is anything like hints of Oracle in > Postgres..otherwise..I wish to find a way to force a query plan to use the > indexes or tell the optimizer things like "optimize based in statistics", > "I want to define the order of the a join" , "optimize based on a execution > plan that I consider the best" ... There are a few things you can do. You can explicitly fix the join order using INNER JOIN (in 7.4 you have to set a GUC variable for this to force join order). You can disable specific plan types (though sequential just becomes very expensive as sometimes there is no other way to do things). You can set tuning values to properly express the relative cost of things like CPU time, sequential disk reads and random disk reads. These are done by setting GUC variables either in the postgres config file or using SET commands. They are per backend so some queries can be done using one set of values while others going on at the same time use different values.