pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql plan cache
7+ messages / 4 participants
[nested] [flat]
* plpgsql plan cache
@ 2010-02-22 19:26 Joel Jacobson <joel@gluefinance.com>
0 siblings, 1 reply; 7+ messages in thread
From: Joel Jacobson @ 2010-02-22 19:26 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: plpgsql plan cache
@ 2010-02-22 19:42 Pierre C <lists@peufeu.com>
parent: Joel Jacobson <joel@gluefinance.com>
0 siblings, 1 reply; 7+ messages in thread
From: Pierre C @ 2010-02-22 19:42 UTC (permalink / raw)
To: Joel Jacobson <joel@gluefinance.com>; pgsql-performance
> 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 )
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: plpgsql plan cache
@ 2010-02-22 19:58 Joel Jacobson <joel@gluefinance.com>
parent: Pierre C <lists@peufeu.com>
0 siblings, 2 replies; 7+ messages in thread
From: Joel Jacobson @ 2010-02-22 19:58 UTC (permalink / raw)
To: Pierre C <lists@peufeu.com>; +Cc: pgsql-performance
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 )
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: plpgsql plan cache
@ 2010-02-22 21:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: Joel Jacobson <joel@gluefinance.com>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2010-02-22 21:20 UTC (permalink / raw)
To: Joel Jacobson <joel@gluefinance.com>; +Cc: Pierre C <lists@peufeu.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: plpgsql plan cache
@ 2010-02-22 21:23 Nikolas Everett <nik9000@gmail.com>
parent: Joel Jacobson <joel@gluefinance.com>
1 sibling, 1 reply; 7+ messages in thread
From: Nikolas Everett @ 2010-02-22 21:23 UTC (permalink / raw)
To: Joel Jacobson <joel@gluefinance.com>; +Cc: Pierre C <lists@peufeu.com>; 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@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 )
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: plpgsql plan cache
@ 2010-02-22 21:47 Joel Jacobson <joel@gluefinance.com>
parent: Nikolas Everett <nik9000@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Joel Jacobson @ 2010-02-22 21:47 UTC (permalink / raw)
To: Nikolas Everett <nik9000@gmail.com>; +Cc: Pierre C <lists@peufeu.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: plpgsql plan cache
@ 2010-02-22 22:15 Pierre C <lists@peufeu.com>
parent: Joel Jacobson <joel@gluefinance.com>
0 siblings, 0 replies; 7+ messages in thread
From: Pierre C @ 2010-02-22 22:15 UTC (permalink / raw)
To: Joel Jacobson <joel@gluefinance.com>; Nikolas Everett <nik9000@gmail.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2010-02-22 22:15 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-22 19:26 plpgsql plan cache Joel Jacobson <joel@gluefinance.com>
2010-02-22 19:42 ` Pierre C <lists@peufeu.com>
2010-02-22 19:58 ` Joel Jacobson <joel@gluefinance.com>
2010-02-22 21:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-02-22 21:23 ` Nikolas Everett <nik9000@gmail.com>
2010-02-22 21:47 ` Joel Jacobson <joel@gluefinance.com>
2010-02-22 22:15 ` Pierre C <lists@peufeu.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox