Re: plpgsql plan cache - Mailing list pgsql-performance
| From | Joel Jacobson |
|---|---|
| Subject | Re: plpgsql plan cache |
| Date | |
| Msg-id | 8AD3F6B6-BCCF-46E1-B255-9EB8E761653B@gluefinance.com Whole thread Raw |
| In response to | Re: plpgsql plan cache ("Pierre C" <lists@peufeu.com>) |
| Responses |
Re: plpgsql plan cache
Re: plpgsql plan cache |
| List | pgsql-performance |
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 )
pgsql-performance by date: