From: Joel Jacobson <joel@gluefinance.com>
To: Nikolas Everett <nik9000@gmail.com>
Cc: Pierre C <lists@peufeu.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: plpgsql plan cache
Date: Mon, 22 Feb 2010 22:47:15 +0100
Message-ID: <8bdec0841002221347m1c4556a4xb670877aab429a13@mail.gmail.com> (raw)
In-Reply-To: <d4e11e981002221323l78eb946cv23dcf7c54aae5822@mail.gmail.com>
References: <38E2265E-B38E-4EBD-BB87-23B46EF4FD1C@gluefinance.com>
<op.u8jnpjikeorkce@localhost>
<8AD3F6B6-BCCF-46E1-B255-9EB8E761653B@gluefinance.com>
<d4e11e981002221323l78eb946cv23dcf7c54aae5822@mail.gmail.com>
Thank you for explaining!
Now I understand, makes perfect sense! :-)
2010/2/22 Nikolas Everett <nik9000@gmail.com>:
> 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@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 )
>>
>
>
--
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: joel@gluefinance.com, nik9000@gmail.com, lists@peufeu.com
Subject: Re: plpgsql plan cache
In-Reply-To: <8bdec0841002221347m1c4556a4xb670877aab429a13@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox