| Lists: | pgsql-hackers |
|---|
| From: | pasman pasmański <pasman(dot)p(at)gmail(dot)com> |
|---|---|
| To: | pgsql-hackers(at)postgresql(dot)org |
| Subject: | Re: function(contants) evaluated for every row |
| Date: | 2010-11-25 09:57:26 |
| Message-ID: | AANLkTi=9YJuaB7-qto9fTN74wak8FJA59P1wrSCn9-zg@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
>I've seen this as well be a performance issue, in particular with partitioned tables.
>Out of habit I now write functions that always cache the value of the function in
>a variable and use the variable in the actual query to avoid this particular "gotcha".
subquery may be used to cache constants:
SELECT a_date
FROM test
WHERE a_date=(SELECT current_date);
"Seq Scan on test1 (cost=0.01..10.76 rows=5 width=4)"
" Filter: (a_date = $0)"
" InitPlan 1 (returns $0)"
" -> Result (cost=0.00..0.01 rows=1 width=0)"
------------
pasman
| From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
|---|---|
| To: | pasman pasmański <pasman(dot)p(at)gmail(dot)com> |
| Cc: | pgsql-hackers(at)postgresql(dot)org |
| Subject: | Re: function(contants) evaluated for every row |
| Date: | 2010-11-27 11:15:20 |
| Message-ID: | AANLkTi=bW4urEkAUpjvLv1=7yBGs=o-iLvYWNiCCvcxH@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
2010/11/25 pasman pasmański <pasman(dot)p(at)gmail(dot)com>:
>>I've seen this as well be a performance issue, in particular with partitioned tables.
>>Out of habit I now write functions that always cache the value of the function in
>>a variable and use the variable in the actual query to avoid this particular "gotcha".
>
> subquery may be used to cache constants:
>
> SELECT a_date
> FROM test
> WHERE a_date=(SELECT current_date);
>
>
> "Seq Scan on test1 (cost=0.01..10.76 rows=5 width=4)"
> " Filter: (a_date = $0)"
> " InitPlan 1 (returns $0)"
> " -> Result (cost=0.00..0.01 rows=1 width=0)"
Interesting. So we pull a subquery out and treat it as an initplan,
but not a stable function? Hmm...
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
| From: | Bruce Momjian <bruce(at)momjian(dot)us> |
|---|---|
| To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
| Cc: | pasman pasmański <pasman(dot)p(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org |
| Subject: | Re: function(contants) evaluated for every row |
| Date: | 2011-03-11 00:32:51 |
| Message-ID: | 201103110032.p2B0WpZ04921@momjian.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
Robert Haas wrote:
> 2010/11/25 pasman pasma?ski <pasman(dot)p(at)gmail(dot)com>:
> >>I've seen this as well be a performance issue, in particular with partitioned tables.
> >>Out of habit I now write functions that always cache the value of the function in
> >>a variable and use the variable in the actual query to avoid this particular "gotcha".
> >
> > subquery may be used to cache constants:
> >
> > SELECT a_date
> > ?FROM test
> > ?WHERE a_date=(SELECT current_date);
> >
> >
> > "Seq Scan on test1 ?(cost=0.01..10.76 rows=5 width=4)"
> > " ?Filter: (a_date = $0)"
> > " ?InitPlan 1 (returns $0)"
> > " ? ?-> ?Result ?(cost=0.00..0.01 rows=1 width=0)"
>
> Interesting. So we pull a subquery out and treat it as an initplan,
> but not a stable function? Hmm...
I assume this is _not_ a TODO.
--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
| From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
|---|---|
| To: | Bruce Momjian <bruce(at)momjian(dot)us> |
| Cc: | pasman pasmański <pasman(dot)p(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org |
| Subject: | Re: function(contants) evaluated for every row |
| Date: | 2011-03-11 03:18:30 |
| Message-ID: | AANLkTik+K6HBc+V7cEypTUSDW0nsLW+a6XmQhtfb4ATS@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
On Thu, Mar 10, 2011 at 7:32 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Robert Haas wrote:
>> 2010/11/25 pasman pasma?ski <pasman(dot)p(at)gmail(dot)com>:
>> >>I've seen this as well be a performance issue, in particular with partitioned tables.
>> >>Out of habit I now write functions that always cache the value of the function in
>> >>a variable and use the variable in the actual query to avoid this particular "gotcha".
>> >
>> > subquery may be used to cache constants:
>> >
>> > SELECT a_date
>> > ?FROM test
>> > ?WHERE a_date=(SELECT current_date);
>> >
>> >
>> > "Seq Scan on test1 ?(cost=0.01..10.76 rows=5 width=4)"
>> > " ?Filter: (a_date = $0)"
>> > " ?InitPlan 1 (returns $0)"
>> > " ? ?-> ?Result ?(cost=0.00..0.01 rows=1 width=0)"
>>
>> Interesting. So we pull a subquery out and treat it as an initplan,
>> but not a stable function? Hmm...
>
> I assume this is _not_ a TODO.
Dunno, not sure.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company