Re: query planning different in plpgsql?

On 10/23/09 8:38 AM, "Michal J.Kubski" <michal(dot)kubski(at)cdt(dot)pl> wrote:

>
>
>
>
> Hi,
>
>
>
> Is there any way to get the query plan of the query run in the stored
>
> procedure?
>
> I am running the following one and it takes 10 minutes in the procedure
>
> when it is pretty fast standalone.
>
>
>
> Any ideas would be welcome!
>
>

If your query is
SELECT field, field2 FROM table1 WHERE field3 = 'xxx' AND field4 = 'yyy'

And you want to test what the planner will do without the knowledge of the
exact values 'xxx' and 'yyy', you can prepare a statement:

#PREPARE foo() AS SELECT field, field2 FROM table1 WHERE field3 = $1 AND
field4 = $2;

#EXPLAIN execute foo('xxx', 'yyy');

If field3 and field4 don't have unique indexes, the plan might differ. It
will most likely differ if 'xxx' or 'yyy' is a very common value in the
table and the table is not tiny.