PL/PgSQL Index Usage with Trigger Variables

From: Thomas F(dot)O'Connell <tfo(at)sitening(dot)com>
To: PgSql General <pgsql-general(at)postgresql(dot)org>
Subject: PL/PgSQL Index Usage with Trigger Variables
Date: 2005-01-18 16:53:40
Message-ID: 8106D11D-6971-11D9-90FF-000D93AE0944@sitening.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I've got a question about how PL/PgSQL passes things to the planner
based on statement logging.

E.g., I have a statement like this in PL/PgSQL:

UPDATE mytable SET mybigintcol = somevalue WHERE mybigintcol =
NEW.myotherbigintcol;

This shows up in the logs with the NEW variable converted to unquoted
constant data. Is PL/PgSQL smart enough to help the planner figure out
that it can use an index on mybigintcol based on the fact that the type
of myotherbigintcol can be discerned from the record of NEW? Or is type
information not available as the record variable is interpolated and
passed to the planner?

As in, should I rewrite the above statement as:

UPDATE mytable SET mybigintcol = somevalue WHERE mybigintcol = ''''''
|| NEW.myotherbigintcol || '''''';

in order to help the planner understand that it can use an index?

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Klugmann 2005-01-18 17:12:54 Re: Problem getting sql statement logging to work
Previous Message Michael Fuhr 2005-01-18 16:51:27 Re: Problem getting sql statement logging to work