Re: Performance problems with prepared statements

Theo Kramer a écrit :
> Hi
>
> I have been having some serious performance issues when using prepared
> statements which I can not re-produce when using a direct statement. Let
> me try to explain
>
> The query does an order by in descending order on several columns for
> which an index exists.
>
> The explain output as follows
>
> rascal=# explain SELECT oid, * FROM calllog
> WHERE calllog_mainteng = '124         '
> AND calllog_phase = 8
> AND calllog_self < 366942
> OR calllog_mainteng = '124         '
> AND calllog_phase < 8
> ORDER BY calllog_mainteng DESC,
>  calllog_phase DESC,
>  calllog_self DESC limit 25;
>                                                QUERY PLAN
> ---------------------------------------------------------------------------------------------------------
>  Limit  (cost=0.00..111.62 rows=25 width=2164)
>    ->  Index Scan Backward using calllog_rmc_idx on calllog
> (cost=0.00..53475.22 rows=11977 width=2164)
>          Index Cond: (calllog_mainteng = '124         '::bpchar)
>          Filter: (((calllog_phase = 8) AND (calllog_self < 366942)) OR
> (calllog_phase < 8))
>
> When running the query directly from psql it returns the required rows
> in less than 100 milli-seconds.
>
> However, when using a prepared statement from my C application on the
> above query and executing it the query duration is as follows
>
> SELECT oid, * FROM calllog
> WHERE calllog_mainteng = '124         '
> AND calllog_phase = 8
> AND calllog_self < 366942
> OR calllog_mainteng = '124         '
> AND calllog_phase < 8
> ORDER BY calllog_mainteng DESC,
>  calllog_phase DESC,
>  calllog_self DESC limit 25
> Row[s] = 25, Duration = 435409.474 ms
>
> The index as per the explain is defined as follows
>
> "calllog_rmc_idx" UNIQUE, btree (calllog_mainteng, calllog_phase,
> calllog_self)
>
> VACUUM and all those good things done
>
> Version of PostgreSQL 8.1 and 8.2
>
> enable_seqscan = off
> enable_sort = off
>
> Any advice/suggestions/thoughts much appreciated
>
Reading the manual, you can learn that prepared statement can (not)
follow the same plan as direct query:
the plan is make before pg know the value of the variable.

See 'Notes' http://www.postgresql.org/docs/8.2/interactive/sql-prepare.html