Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 89D2E632D98 for ; Mon, 22 Feb 2010 18:18:17 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 47839-10 for ; Mon, 22 Feb 2010 22:18:06 +0000 (UTC) X-Greylist: delayed 00:30:49.685066 by SQLgrey-1.7.6 Received: from mail-ew0-f214.google.com (mail-ew0-f214.google.com [209.85.219.214]) by mail.postgresql.org (Postfix) with ESMTP id 945D16326D3 for ; Mon, 22 Feb 2010 18:18:06 -0400 (AST) Received: by ewy6 with SMTP id 6so4143044ewy.34 for ; Mon, 22 Feb 2010 14:18:05 -0800 (PST) MIME-Version: 1.0 Received: by 10.216.166.75 with SMTP id f53mr1016556wel.126.1266875235214; Mon, 22 Feb 2010 13:47:15 -0800 (PST) In-Reply-To: References: <38E2265E-B38E-4EBD-BB87-23B46EF4FD1C@gluefinance.com> <8AD3F6B6-BCCF-46E1-B255-9EB8E761653B@gluefinance.com> Date: Mon, 22 Feb 2010 22:47:15 +0100 Message-ID: <8bdec0841002221347m1c4556a4xb670877aab429a13@mail.gmail.com> Subject: Re: plpgsql plan cache From: Joel Jacobson To: Nikolas Everett Cc: Pierre C , pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201002/306 X-Sequence-Number: 37727 Thank you for explaining! Now I understand, makes perfect sense! :-) 2010/2/22 Nikolas Everett : > The planner knows that that particular date range is quite selective so i= t > 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. =A0I think functions behav= e in > exactly the same way. =A0Its kind of a pain but you can do your query wit= h > dynamic sql like on here: > =A0http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPG= SQL-STATEMENTS-EXECUTING-DYN > > On Mon, Feb 22, 2010 at 2:58 PM, Joel Jacobson wro= te: >> >> db=3D# \d FlagValueAccountingTransactions >> =A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0Table >> "public.flagvalueaccountingtransactions" >> =A0=A0 =A0 =A0 Column =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 Type =A0 =A0 = =A0 =A0 =A0 | >> =A0 =A0 =A0 =A0Modifiers >> >> ---------------------+--------------------------+-----------------------= --------------------------------------------------- >> =A0flagvalueid =A0 =A0 =A0 =A0 | integer =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0| not null >> =A0eventid =A0 =A0 =A0 =A0 =A0 =A0 | integer =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0| not null >> =A0transactionid =A0 =A0 =A0 | integer =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0| not null >> =A0recorddate =A0 =A0 =A0 =A0 =A0| timestamp with time zone | not null >> =A0debitaccountnumber =A0| integer =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| = not null >> =A0creditaccountnumber | integer =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| no= t null >> =A0debitaccountname =A0 =A0| character varying =A0 =A0 =A0 =A0| not null >> =A0creditaccountname =A0 | character varying =A0 =A0 =A0 =A0| not null >> =A0amount =A0 =A0 =A0 =A0 =A0 =A0 =A0| numeric =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0| not null >> =A0currency =A0 =A0 =A0 =A0 =A0 =A0| character(3) =A0 =A0 =A0 =A0 =A0 = =A0 | not null >> =A0seqid =A0 =A0 =A0 =A0 =A0 =A0 =A0 | integer =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0| not null default >> nextval('seqflagvalueaccountingtransactions'::regclass) >> =A0undone =A0 =A0 =A0 =A0 =A0 =A0 =A0| smallint =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 | >> =A0undoneseqid =A0 =A0 =A0 =A0 | integer =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0| >> Indexes: >> =A0=A0 =A0"flagvalueaccountingtransactions_pkey" PRIMARY KEY, btree (seq= id) >> =A0=A0 =A0"index_flagvalueaccountingtransactions_eventid" btree (eventid= ) >> =A0=A0 =A0"index_flagvalueaccountingtransactions_flagvalueid" btree >> (flagvalueid) >> =A0=A0 =A0"index_flagvalueaccountingtransactions_recorddate" btree (reco= rddate) >> db=3D# EXPLAIN ANALYZE SELECT SUM(Amount) FROM >> FlagValueAccountingTransactions WHERE FlagValueID =3D 182903 AND (Record= Date >> >=3D '2008-10-21' AND RecordDate < '2008-10-22') AND CreditAccountName = =3D >> 'CLIENT_BALANCES' AND Currency =3D 'SEK'; >> >> =A0 =A0 =A0 =A0 =A0 =A0 =A0 QUERY PLAN >> >> >> ------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------- >> =A0Aggregate =A0(cost=3D1291.74..1291.75 rows=3D1 width=3D7) (actual >> time=3D1.812..1.812 rows=3D1 loops=3D1) >> =A0=A0 -> =A0Index Scan using index_flagvalueaccountingtransactions_reco= rddate >> on flagvalueaccountingtransactions =A0(cost=3D0.00..1291.68 rows=3D25 wi= dth=3D7) >> (actual time=3D1.055..1.807 rows=3D1 loops=3D1) >> =A0=A0 =A0 =A0 =A0 Index Cond: ((recorddate >=3D '2008-10-21 00:00:00+02= '::timestamp >> with time zone) AND (recorddate < '2008-10-22 00:00:00+02'::timestamp wi= th >> time zone)) >> =A0=A0 =A0 =A0 =A0 Filter: ((flagvalueid =3D 182903) AND ((creditaccount= name)::text =3D >> 'CLIENT_BALANCES'::text) AND (currency =3D 'SEK'::bpchar)) >> =A0Total runtime: 1.847 ms >> (5 rows) >> db=3D# PREPARE myplan (integer,date,date,varchar,char(3)) AS SELECT >> SUM(Amount) FROM FlagValueAccountingTransactions WHERE FlagValueID =3D $= 1 AND >> RecordDate >=3D $2 AND RecordDate < $3 AND DebitAccountName =A0=3D $4 AN= D Currency >> =3D $5;PREPARE >> PREPARE >> db=3D#=A0EXPLAIN ANALYZE EXECUTE >> myplan(182903,'2008-10-21','2008-10-22','CLIENT_BALANCES','SEK'); >> >> =A0 =A0 =A0 =A0 =A0 =A0 QUERY PLAN >> >> >> ------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------- >> =A0Aggregate =A0(cost=3D3932.75..3932.76 rows=3D1 width=3D7) (actual >> time=3D175.792..175.792 rows=3D1 loops=3D1) >> =A0=A0 -> =A0Bitmap Heap Scan on flagvalueaccountingtransactions >> =A0(cost=3D2283.91..3932.74 rows=3D1 width=3D7) (actual time=3D175.747..= 175.767 rows=3D4 >> loops=3D1) >> =A0=A0 =A0 =A0 =A0 Recheck Cond: ((recorddate >=3D $2) AND (recorddate <= $3) AND >> (flagvalueid =3D $1)) >> =A0=A0 =A0 =A0 =A0 Filter: (((debitaccountname)::text =3D ($4)::text) AN= D (currency =3D >> $5)) >> =A0=A0 =A0 =A0 =A0 -> =A0BitmapAnd =A0(cost=3D2283.91..2283.91 rows=3D58= 2 width=3D0) (actual >> time=3D175.714..175.714 rows=3D0 loops=3D1) >> =A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Bitmap Index Scan on >> index_flagvalueaccountingtransactions_recorddate =A0(cost=3D0.00..395.97 >> rows=3D21536 width=3D0) (actual time=3D1.158..1.158 rows=3D3432 loops=3D= 1) >> =A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: ((recorddate >=3D= $2) AND (recorddate < >> $3)) >> =A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Bitmap Index Scan on >> index_flagvalueaccountingtransactions_flagvalueid =A0(cost=3D0.00..1887.= 69 >> rows=3D116409 width=3D0) (actual time=3D174.132..174.132 rows=3D1338824 = loops=3D1) >> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: (flagvalueid =3D $1) >> =A0Total runtime: 175.879 ms >> (10 rows) >> >> >> Hm, it is strange the query planner is using two different strategies fo= r >> 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 thoug= h. >> >> Please post the following : >> >> - EXPLAIN ANALYZE your query directly in psql >> - PREPARE testq AS your query >> - EXPLAIN ANALYZE EXECUTE testq( your parameters ) >> > > --=20 Best regards, Joel Jacobson Glue Finance E: jj@gluefinance.com 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