Re: [PERFORM] plpgsql plan cache
Thank you for explaining!
Now I understand, makes perfect sense! :-)
2010/2/22 Nikolas Everett :
> 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 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 us
Re: [PERFORM] plpgsql plan cache
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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 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 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?
>
>
Re: [PERFORM] plpgsql plan cache
Joel Jacobson 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] plpgsql plan cache
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 )
Re: [PERFORM] plpgsql plan cache
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 ) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
