The planner knows that that particular date range is quite selective so it doesn't have to BitmapAnd two indexes together.
The problem is that a prepared statement asks the db to plan the query without knowing anything about the parameters. I think functions behave in exactly the same way. Its kind of a pain but you can do your query with dynamic sql like on here: http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN On Mon, Feb 22, 2010 at 2:58 PM, Joel Jacobson <j...@gluefinance.com> wrote: > 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 ) > > >
