Thread: plpgsql plan cache

Hi,

I am trying to make a select query in my plpgsql function to use an
index allowing an index scan instead of a seq scan.

When running the query in the sql prompt,  it works fine, but
apparently the index is not used for the same query in the plpgsql
function.

The problem is not the data types of the parameters to the function or
the query, they are identical.

When I tried using EXECUTE in the plpgsql function, the index is being
used.

I thought the query planner must have made a bad decision when I
created the function the first time.

I therefore tried to drop the function, disconnect from the sql
client, reconnect (to get a new session), create the function again.
The function still runs slow though.

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.

Is there a way to someone clear the entire query cache or even better
for a particular plpgsql function?

I'm greatful for any ideas.

Best regards,

Joel Jacobson

> 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 )

Joel Jacobson <joel@gluefinance.com> writes:
> Hm, it is strange the query planner is using two different strategies
> for the same query?

They're not the same query.  One plan is generic for any value of the
parameters, the other is chosen for specific values of those parameters.
In particular, the unparameterized query depends very strongly on the
knowledge that not many rows will meet the RecordDate range constraint.
If you picked dates that were further apart you'd probably get something
that looked more like the other plan.

            regards, tom lane

Actually, planner was smart in using a bitmap index scan in the prepared
query. Suppose you later EXECUTE that canned plan with a date range which
covers say half of the table : the indexscan would be a pretty bad choice
since it would have to access half the rows in the table in index order,
which is potentially random disk IO. Bitmap Index Scan is slower in your
high-selectivity case, but it can withstand much more abuse on the
parameters.

PG supports the quite clever syntax of EXECUTE 'blah' USING params, you
don't even need to mess with quoting.

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