Re: plpgsql plan cache

Thank you for explaining!

Now I understand, makes perfect sense! :-)

2010/2/22 Nikolas Everett <nik9000(at)gmail(dot)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(at)gluefinance(dot)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(at)gluefinance(dot)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