Thank you for explaining! Now I understand, makes perfect sense! :-) 2010/2/22 Nikolas Everett <nik9000@xxxxxxxxx>: > 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 <joel@xxxxxxxxxxxxxxx> 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 ) >> > > -- Best regards, Joel Jacobson Glue Finance E: jj@xxxxxxxxxxxxxxx T: +46 70 360 38 01 Postal address: Glue Finance AB Box 549 114 11 Stockholm Sweden Visiting address: Glue Finance AB Birger Jarlsgatan 14 114 34 Stockholm Sweden -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
