Thread: Hinting the planner

Hello all.

I have some tables that contain exactly 1 row and that I use for searches with
JOIN.

Does it make any sense to hint the planner about this?
If so, how can I send such hints to it?

--
Reg me, please!

Reg Me Please wrote:
> Hello all.
>
> I have some tables that contain exactly 1 row and that I use for searches with
> JOIN.
>
> Does it make any sense to hint the planner about this?
> If so, how can I send such hints to it?
>

You don't give hints to the planner.

The planner does use statistics gathered from an ANALYZE to generate the
best plan, so if you haven't analyzed your db you will not likely get
the best plans.

see
http://www.postgresql.org/docs/8.2/interactive/sql-analyze.html

and
http://www.postgresql.org/docs/8.2/interactive/sql-vacuum.html

also
http://www.postgresql.org/docs/8.2/interactive/maintenance.html

can explain these tasks in more detail.

If this is insufficient then you can also look at your config file.



--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

Not the planner but you can hint the query as in this example
select /*+ ordered use_hash(code1)*/ * from table_1;

Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.


> Date: Wed, 2 Jan 2008 00:15:14 +1030
> From: pgsql@Sheeky.Biz
> To: regmeplease@gmail.com
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Hinting the planner
>
> Reg Me Please wrote:
> > Hello all.
> >
> > I have some tables that contain exactly 1 row and that I use for searches with
> > JOIN.
> >
> > Does it make any sense to hint the planner about this?
> > If so, how can I send such hints to it?
> >
>
> You don't give hints to the planner.
>
> The planner does use statistics gathered from an ANALYZE to generate the
> best plan, so if you haven't analyzed your db you will not likely get
> the best plans.
>
> see
> http://www.postgresql.org/docs/8.2/interactive/sql-analyze.html
>
> and
> http://www.postgresql.org/docs/8.2/interactive/sql-vacuum.html
>
> also
> http://www.postgresql.org/docs/8.2/interactive/maintenance.html
>
> can explain these tasks in more detail.
>
> If this is insufficient then you can also look at your config file.
>
>
>
> --
>
> Shane Ambler
> pgSQL (at) Sheeky (dot) Biz
>
> Get Sheeky @ http://Sheeky.Biz
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/

---

i’m is proud to present Cause Effect, a series about real people making a difference. Learn more

On Jan 1, 2008 10:27 AM, Martin Gainty <mgainty@hotmail.com> wrote:
>
>  Not the planner but you can hint the query as in this example
>  select /*+ ordered use_hash(code1)*/ * from table_1;

no, that's an oraclism.

mgainty@hotmail.com (Martin Gainty) writes:
> Not the planner but you can hint the query as in this example
> select /*+ ordered use_hash(code1)*/ * from table_1;

That might have some effect with Oracle; is there some reason why you
think this would be expected to have any effect on a PostgreSQL query?
--
select 'cbbrowne' || '@' || 'acm.org';
http://www3.sympatico.ca/cbbrowne/x.html
Rules of the Evil Overlord #178. "If  I have the  hero cornered and am
about to finish him off and he says "Look out behind you!!" I will not
laugh and  say "You  don't expect me  to fall  for that old  trick, do
you?" Instead I will take a step to the side and half turn. That way I
can still  keep my  weapon trained on  the hero,  I can scan  the area
behind me, and  if anything was heading for me it  will now be heading
for him." <http://www.eviloverlord.com/>