Re: plpgsql plan cache

[Nikolas Everett <nik9000@xxxxxxxxx>]

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