Thread: PL/PgSQL Index Usage with Trigger Variables
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
On Tue, Jan 18, 2005 at 10:53:40AM -0600, Thomas F.O'Connell wrote: > 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? Start a new session and try this: SET debug_print_plan TO on; SET client_min_messages TO debug1; <some statement that invokes the trigger> I did some tests in 7.4.6 and 8.0.0 and an UPDATE statement like the one above did appear to be using an index from a trigger. Note that the plan for the UPDATE is logged only the first time it's executed in a session, presumably because subsequent calls to the function use a cached plan. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
It looks like the indexes are in fact used when trigger variables are referenced in where clauses in PL/pgSQL. Thanks for the idea of investigating the plans in the logs for this info. Follow-up question: are indexes used in dynamically executed queries? Rather than SEQSCAN or INDEXSCAN in the DETAIL item, I see RESULT followed by a large amount of unreadable (by me) output. -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
On Wed, Jan 19, 2005 at 01:46:33AM -0600, Thomas F.O'Connell wrote: > Follow-up question: are indexes used in dynamically executed queries? > > Rather than SEQSCAN or INDEXSCAN in the DETAIL item, I see RESULT > followed by a large amount of unreadable (by me) output. Are you sure you're looking at the right DETAIL? Adding some RAISE INFO or RAISE DEBUG statements can help you pinpoint which log output belongs to which part of the function. I just wrote a test function that used EXECUTE to do an UPDATE and a SELECT loop and saw INDEXSCAN in the relevant DETAIL sections. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
I think I see what was happening. I was looking at the output of the SELECT that is used for opening a cursor. Got it. Thanks for your help. It's kind of a meta-select in the printed version of a plan if the cursor being opened is a SELECT. -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 On Jan 19, 2005, at 3:36 AM, Michael Fuhr wrote: > On Wed, Jan 19, 2005 at 01:46:33AM -0600, Thomas F.O'Connell wrote: > >> Follow-up question: are indexes used in dynamically executed queries? >> >> Rather than SEQSCAN or INDEXSCAN in the DETAIL item, I see RESULT >> followed by a large amount of unreadable (by me) output. > > Are you sure you're looking at the right DETAIL? Adding some > RAISE INFO or RAISE DEBUG statements can help you pinpoint which > log output belongs to which part of the function. > > I just wrote a test function that used EXECUTE to do an UPDATE and > a SELECT loop and saw INDEXSCAN in the relevant DETAIL sections. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend