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

Search Discussions

  • Tom Lane at Nov 8, 2007 at 3:43 pm

    "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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedNov 8, '07 at 11:50a
activeNov 8, '07 at 3:43p
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Sergey Moroz: 1 post Tom Lane: 1 post

People

Translate

site design / logo © 2022 Grokbase