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:
|
