Skip site navigation (1) Skip section navigation (2)

Re: plpgsql plan cache

From: Nikolas Everett <nik9000(at)gmail(dot)com>
To: Joel Jacobson <joel(at)gluefinance(dot)com>
Cc: Pierre C <lists(at)peufeu(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: plpgsql plan cache
Date: 2010-02-22 21:23:56
Message-ID: d4e11e981002221323l78eb946cv23dcf7c54aae5822@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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 )
>
>
>

In response to

Responses

pgsql-performance by date

Next:From: Joel JacobsonDate: 2010-02-22 21:47:15
Subject: Re: plpgsql plan cache
Previous:From: Tom LaneDate: 2010-02-22 21:20:58
Subject: Re: plpgsql plan cache

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group