Message-ID: <470CE918.4060608@dalibo.com>
Date: Wed, 10 Oct 2007 17:00:40 +0200
From: =?ISO-8859-1?Q?C=E9dric_Villemain?= <cedric.villemain@dalibo.com>
User-Agent: Mozilla-Thunderbird 2.0.0.4 (X11/20070828)
MIME-Version: 1.0
To: theo@flame.co.za
CC: pgsql-performance@postgresql.org
Subject: Re: Performance problems with prepared statements
References: <1192027540.2495.3.camel@localhost.localdomain>
In-Reply-To: <1192027540.2495.3.camel@localhost.localdomain>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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