Обсуждение: Strange variable behaviour when using it in limit clause in plpgsql stored procedure

I tested performance of my query with limit clause inside plpgsql procedure. 2 slightly different situations:

1. Sql with limit clause and literal variable (for example 'select field1 from table1 limit 100')
2. The same sql with limit clause and pgplsql variable  (for example 'select field1 from table1 limit vilimit'). vilimit defined in declare section.

At first I compared execution plans. they were absolutely equal!
But in fact first procedure was 10 times!!!! faster then the second! What's the problem?!?!
Note: tested sql was complex enough. I didn't test this case on simple query like 'select field1 from table1 limit 100'.

--
Sincerely,
Sergey Moroz

"Sergey Moroz" <smo@mgcp.com> writes:
> I tested performance of my query with limit clause inside plpgsql procedure.
> 2 slightly different situations:

> 1. Sql with limit clause and literal variable (for example 'select field1
> from table1 limit 100')
> 2. The same sql with limit clause and pgplsql variable  (for example 'select
> field1 from table1 limit vilimit'). vilimit defined in declare section.

> At first I compared execution plans. they were absolutely equal!
> But in fact first procedure was 10 times!!!! faster then the second!

Exactly what did you do to conclude that the execution plans were the
same?  I would not expect the planner to choose the same plan in these
two cases, at least not if 100 is just a small fraction of the total
estimated query output.

            regards, tom lane