Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 7A41D634DD9 for ; Mon, 22 Feb 2010 17:30:25 -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 39361-05 for ; Mon, 22 Feb 2010 21:30:14 +0000 (UTC) X-Greylist: domain auto-whitelisted 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 50F24633F9A for ; Mon, 22 Feb 2010 17:24:18 -0400 (AST) Received: by ewy6 with SMTP id 6so4079882ewy.34 for ; Mon, 22 Feb 2010 13:24:16 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:message-id:subject:to:cc:content-type; bh=t36xyWv6bdVzXMxl1XvVAozsYJeFR03AZkAcCIbMWBY=; b=CkWrGwvwhMIO6bOVfxbeHYQ27iNiWlFVTMB8sBHZnQioI8nNQGuXjWT5IgGAxLec+R cq2oS3ukjavmSDrSKVXRlyJGkMRoiLfTHzVlX6Y2YEwlR5MPoasBVj8h+gXSJa6AYc3g XyxvVuAB4JM4goeAq9HK+Yqt8vPdytQ0KTLyk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; b=b8yxaTnQomDeszRK9y0Csl1tXQo2lE1o+AMqKLNIIlgx/kh4NXbTXtddqwZanljaJX pFKy3YJKk+s4Uq6cy0TY/+Xv8mLQCTzK1GmqyPAnP+QjdX+YbLTepnoBjuk8FAry/imp JXFHv10ZsyuCoL4kRiQuIS/yIN0HSnngBJFtU= MIME-Version: 1.0 Received: by 10.216.89.195 with SMTP id c45mr2578725wef.38.1266873856185; Mon, 22 Feb 2010 13:24:16 -0800 (PST) In-Reply-To: <8AD3F6B6-BCCF-46E1-B255-9EB8E761653B@gluefinance.com> References: <38E2265E-B38E-4EBD-BB87-23B46EF4FD1C@gluefinance.com> <8AD3F6B6-BCCF-46E1-B255-9EB8E761653B@gluefinance.com> From: Nikolas Everett Date: Mon, 22 Feb 2010 16:23:56 -0500 Message-ID: Subject: Re: plpgsql plan cache To: Joel Jacobson Cc: Pierre C , pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0016e6d99d462c574d0480370eaf X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.714 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_FONT_FACE_BAD=0.884, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201002/304 X-Sequence-Number: 37725 --0016e6d99d462c574d0480370eaf Content-Type: text/plain; charset=ISO-8859-1 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 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 ) > > > --0016e6d99d462c574d0480370eaf Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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. =A0I think functions behave in exact= ly the same way. =A0Its kind of a pain but you can do your query with dynam= ic sql like on here: =A0http://www.post= gresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECU= TING-DYN

On Mon, Feb 22, 2010 at 2:58 PM, Joel Jacobs= on <joel@gluef= inance.com> wrote:
db=3D# \d FlagValueAccoun= tingTransactions
=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Table "public.flagvalu= eaccountingtransactions"
=A0=A0 =A0 =A0 Column =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 Type = =A0 =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0Modifiers =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0=A0
---------------------+--------------------------+-----------------= ---------------------------------------------------------
=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<= /font>
=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| not null
=A0debitaccountname =A0 =A0| character varying =A0 =A0 =A0 =A0| no= t null
=A0creditaccountname =A0 | character var= ying =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('seqflagvalueaccountingtrans= actions'::regclass)
=A0undone =A0 =A0 =A0 =A0 =A0 =A0 =A0| smallint =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 |=A0
=A0undoneseqid =A0 =A0 =A0= =A0 | integer =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|=A0
Indexes:
=A0=A0 =A0"flagvalueacco= untingtransactions_pkey" PRIMARY KEY, btree (seqid)
=A0=A0 =A0"index_flagvalueaccountingtransactions_eventid"= ; btree (eventid)
=A0=A0 =A0"index_flagval= ueaccountingtransactions_flagvalueid" btree (flagvalueid)
=A0=A0 =A0"index_flagvalueaccountingtransactions_recorddate&q= uot; btree (recorddate)

db=3D# EXPLAIN ANALYZE SELECT SUM(Amount) FROM FlagVal= ueAccountingTransactions WHERE FlagValueID =3D 182903 AND (RecordDate >= =3D '2008-10-21' AND RecordDate < '2008-10-22') AND Cred= itAccountName =3D 'CLIENT_BALANCES' AND Currency =3D 'SEK';=

=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 QUERY PLAN =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0=A0
----------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------
=A0Aggregate =A0(cost= =3D1291.74..1291.75 rows=3D1 width=3D7) (actual time=3D1.812..1.812 rows=3D= 1 loops=3D1)
=A0=A0 -> =A0Index Scan using index_flagvalueaccountingtransactions= _recorddate on flagvalueaccountingtransactions =A0(cost=3D0.00..1291.68 row= s=3D25 width=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 with time zone))
=A0=A0 =A0 =A0 =A0 Filter: ((flagvalueid =3D 182903) AND ((creditaccou= ntname)::text =3D 'CLIENT_BALANCES'::text) AND (currency =3D 'S= EK'::bpchar))
=A0Total runtime: 1.847 ms
(5 rows)

db=3D# PREPARE myplan (integer,date,date,varchar,char(3)) AS SELE= CT SUM(Amount) FROM FlagValueAccountingTransactions WHERE FlagValueID =3D $= 1 AND RecordDate >=3D $2 AND RecordDate < $3 AND DebitAccountName =A0= =3D $4 AND Currency =3D $5;PREPARE
PREPARE

db=3D#=A0EXPLAIN ANALYZE EXECUTE mypl= an(182903,'2008-10-21','2008-10-22','CLIENT_BALANCES= 9;,'SEK');

=A0=A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= QUERY PLAN =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=A0
----------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------
=A0Aggregate =A0(cost=3D3= 932.75..3932.76 rows=3D1 width=3D7) (actual time=3D175.792..175.792 rows=3D= 1 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: ((recor= ddate >=3D $2) AND (recorddate < $3) AND (flagvalueid =3D $1))
=A0=A0 =A0 =A0 =A0 Filter: (((debitaccountname)::text =3D ($4)::text) = AND (currency =3D $5))
=A0=A0 =A0 =A0 =A0 -> =A0BitmapAnd =A0(= cost=3D2283.91..2283.91 rows=3D582 width=3D0) (actual time=3D175.714..175.7= 14 rows=3D0 loops=3D1)
=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Bi= tmap Index Scan on index_flagvalueaccountingtransactions_recorddate =A0(cos= t=3D0.00..395.97 rows=3D21536 width=3D0) (actual time=3D1.158..1.158 rows= =3D3432 loops=3D1)
=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: ((recorddate &g= t;=3D $2) AND (recorddate < $3))
=A0=A0 =A0 =A0 =A0 =A0 =A0 = =A0 -> =A0Bitmap Index Scan on index_flagvalueaccountingtransactions_fla= gvalueid =A0(cost=3D0.00..1887.69 rows=3D116409 width=3D0) (actual time=3D1= 74.132..174.132 rows=3D1338824 loops=3D1) =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 Index Cond: (flagvalueid =3D $1)
=A0Total runtime: 175.879 ms
(10 rows)

<= div>

Hm, it is strange the query p= lanner is using two different strategies for the same query?



On Feb 22, 2010, at 8:= 42 PM, Pierre C wrote:


I cannot u= nderstand 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 bein= g used.
When running the same query in the s= ql prompt, the index is in use though.

Please post the = following :

- EXPLAIN ANALYZE your query directly in psql
- PREPA= RE testq AS your query
- EXPLAIN ANALYZE EXECUTE testq( your parameters )


--0016e6d99d462c574d0480370eaf--