Re: plpgsql plan cache

db=# \d FlagValueAccountingTransactions
Table
"public.flagvalueaccountingtransactions"
Column | Type
| Modifiers
---------------------+--------------------------
+
--------------------------------------------------------------------------
flagvalueid | integer | not null
eventid | integer | not null
transactionid | integer | not null
recorddate | timestamp with time zone | not null
debitaccountnumber | integer | not null
creditaccountnumber | integer | not null
debitaccountname | character varying | not null
creditaccountname | character varying | not null
amount | numeric | not null
currency | character(3) | not null
seqid | integer | not null default
nextval('seqflagvalueaccountingtransactions'::regclass)
undone | smallint |
undoneseqid | integer |
Indexes:
"flagvalueaccountingtransactions_pkey" PRIMARY KEY, btree (seqid)
"index_flagvalueaccountingtransactions_eventid" btree (eventid)
"index_flagvalueaccountingtransactions_flagvalueid" btree
(flagvalueid)
"index_flagvalueaccountingtransactions_recorddate" btree
(recorddate)

db=# EXPLAIN ANALYZE SELECT SUM(Amount) FROM
FlagValueAccountingTransactions WHERE FlagValueID = 182903 AND
(RecordDate >= '2008-10-21' AND RecordDate < '2008-10-22') AND
CreditAccountName = 'CLIENT_BALANCES' AND Currency = 'SEK';

QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1291.74..1291.75 rows=1 width=7) (actual
time=1.812..1.812 rows=1 loops=1)
-> Index Scan using
index_flagvalueaccountingtransactions_recorddate on
flagvalueaccountingtransactions (cost=0.00..1291.68 rows=25 width=7)
(actual time=1.055..1.807 rows=1 loops=1)
Index Cond: ((recorddate >= '2008-10-21
00:00:00+02'::timestamp with time zone) AND (recorddate < '2008-10-22
00:00:00+02'::timestamp with time zone))
Filter: ((flagvalueid = 182903) AND
((creditaccountname)::text = 'CLIENT_BALANCES'::text) AND (currency =
'SEK'::bpchar))
Total runtime: 1.847 ms
(5 rows)

db=# PREPARE myplan (integer,date,date,varchar,char(3)) AS SELECT
SUM(Amount) FROM FlagValueAccountingTransactions WHERE FlagValueID =
$1 AND RecordDate >= $2 AND RecordDate < $3 AND DebitAccountName = $4
AND Currency = $5;PREPARE
PREPARE

db=# EXPLAIN ANALYZE EXECUTE
myplan(182903,'2008-10-21','2008-10-22','CLIENT_BALANCES','SEK');

QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3932.75..3932.76 rows=1 width=7) (actual
time=175.792..175.792 rows=1 loops=1)
-> Bitmap Heap Scan on flagvalueaccountingtransactions
(cost=2283.91..3932.74 rows=1 width=7) (actual time=175.747..175.767
rows=4 loops=1)
Recheck Cond: ((recorddate >= $2) AND (recorddate < $3) AND
(flagvalueid = $1))
Filter: (((debitaccountname)::text = ($4)::text) AND
(currency = $5))
-> BitmapAnd (cost=2283.91..2283.91 rows=582 width=0)
(actual time=175.714..175.714 rows=0 loops=1)
-> Bitmap Index Scan on
index_flagvalueaccountingtransactions_recorddate (cost=0.00..395.97
rows=21536 width=0) (actual time=1.158..1.158 rows=3432 loops=1)
Index Cond: ((recorddate >= $2) AND (recorddate
< $3))
-> Bitmap Index Scan on
index_flagvalueaccountingtransactions_flagvalueid (cost=0.00..1887.69
rows=116409 width=0) (actual time=174.132..174.132 rows=1338824
loops=1) Index Cond: (flagvalueid = $1)
Total runtime: 175.879 ms
(10 rows)

Hm, it is strange the query planner is using two different strategies
for the same query?

On Feb 22, 2010, at 8:42 PM, Pierre C wrote:

>
>> I cannot understand why the index is not being used when in the
>> plpgsql function?
>> I even tried to make a test function containing nothing more than
>> the single query. Still the index is not being used.
>> When running the same query in the sql prompt, the index is in use
>> though.
>
> Please post the following :
>
> - EXPLAIN ANALYZE your query directly in psql
> - PREPARE testq AS your query
> - EXPLAIN ANALYZE EXECUTE testq( your parameters )