From: Cédric Villemain <cedric.villemain@dalibo.com>
To: theo@flame.co.za
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problems with prepared statements
Date: Wed, 10 Oct 2007 17:00:40 +0200
Message-ID: <470CE918.4060608@dalibo.com> (raw)
In-Reply-To: <1192027540.2495.3.camel@localhost.localdomain>
References: <1192027540.2495.3.camel@localhost.localdomain>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cedric.villemain@dalibo.com, theo@flame.co.za
Subject: Re: Performance problems with prepared statements
In-Reply-To: <470CE918.4060608@dalibo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox