Re: plpgsql plan cache

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