pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedfunction not called if part of aggregate
7+ messages / 5 participants
[nested] [flat]
* function not called if part of aggregate
@ 2006-06-11 17:18 Craig A. James <cjames@modgraph-usa.com>
2006-06-11 17:31 ` Re: function not called if part of aggregate Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-06-11 17:39 ` Re: function not called if part of aggregate Tom Lane <tgl@sss.pgh.pa.us>
2006-06-11 17:48 ` Re: function not called if part of aggregate Jim C. Nasby <jnasby@pervasive.com>
2006-06-11 18:00 ` Re: function not called if part of aggregate Greg Stark <gsstark@mit.edu>
0 siblings, 4 replies; 7+ messages in thread
From: Craig A. James @ 2006-06-11 17:18 UTC (permalink / raw)
To: pgsql-performance
My application has a function, call it "foo()", that requires initialization from a table of about 800 values. Rather than build these values into the C code, it seemed like a good idea to put them on a PG table and create a second function, call it "foo_init()", which is called for each value, like this:
select foo_init(value) from foo_init_table order by value_id;
This works well, but it requires me to actually retrieve the function's value 800 times. So I thought I'd be clever:
select count(1) from (select foo_init(value) from foo_init_table order by value_id) as foo;
And indeed, it count() returns 800, as expected. But my function foo_init() never gets called! Apparently the optimizer figures out that foo_init() must return one value for each row, so it doesn't bother to actually call the function.
db=> explain select count(1) from (select foo_init(value) from foo_init_table order by db_no) as foo;
query plan
----------------------------------------------------------------------------------------------------
aggregate (cost=69.95..69.95 rows=1 width=0)
-> Subquery Scan foo (cost=0.00..67.93 rows=806 width=0)
-> Index Scan using foo_init_table_pkey on foo_init_table (cost=0.00..59.87 rows=806 width=30)
This doesn't seem right to me -- how can the optimizer possibly know that a function doesn't have a side effect, as in my case? Functions could do all sorts of things, such as logging activity, filling in other tables, etc, etc.
Am I missing something here?
Thanks,
Craig
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: function not called if part of aggregate
2006-06-11 17:18 function not called if part of aggregate Craig A. James <cjames@modgraph-usa.com>
@ 2006-06-11 17:31 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
3 siblings, 0 replies; 7+ messages in thread
From: Steinar H. Gunderson @ 2006-06-11 17:31 UTC (permalink / raw)
To: pgsql-performance
On Sun, Jun 11, 2006 at 10:18:20AM -0700, Craig A. James wrote:
> This works well, but it requires me to actually retrieve the function's
> value 800 times.
Is this actually a problem?
> So I thought I'd be clever:
>
> select count(1) from (select foo_init(value) from foo_init_table order by
> value_id) as foo;
Why not just count(foo_init(value))?
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: function not called if part of aggregate
2006-06-11 17:18 function not called if part of aggregate Craig A. James <cjames@modgraph-usa.com>
@ 2006-06-11 17:39 ` Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2006-06-11 17:39 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance
"Craig A. James" <cjames@modgraph-usa.com> writes:
> select count(1) from (select foo_init(value) from foo_init_table order by value_id) as foo;
> And indeed, it count() returns 800, as expected. But my function foo_init() never gets called!
Really? With the ORDER BY in there, it does get called, in my
experiments. What PG version is this exactly?
However, the short answer to your question is that PG does not guarantee
to evaluate parts of the query not needed to determine the result. You
could do something like
select count(x) from (select foo_init(value) as x from foo_init_table order by value_id) as foo;
to ensure that foo_init() must be evaluated.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: function not called if part of aggregate
2006-06-11 17:18 function not called if part of aggregate Craig A. James <cjames@modgraph-usa.com>
@ 2006-06-11 17:48 ` Jim C. Nasby <jnasby@pervasive.com>
3 siblings, 0 replies; 7+ messages in thread
From: Jim C. Nasby @ 2006-06-11 17:48 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance
On Sun, Jun 11, 2006 at 10:18:20AM -0700, Craig A. James wrote:
> This doesn't seem right to me -- how can the optimizer possibly know that a
> function doesn't have a side effect, as in my case? Functions could do all
> sorts of things, such as logging activity, filling in other tables, etc,
> etc.
>
> Am I missing something here?
Read about function stability in the docs.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: function not called if part of aggregate
2006-06-11 17:18 function not called if part of aggregate Craig A. James <cjames@modgraph-usa.com>
@ 2006-06-11 18:00 ` Greg Stark <gsstark@mit.edu>
2006-06-13 06:26 ` Re: function not called if part of aggregate Craig A. James <cjames@modgraph-usa.com>
3 siblings, 1 reply; 7+ messages in thread
From: Greg Stark @ 2006-06-11 18:00 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance
"Craig A. James" <cjames@modgraph-usa.com> writes:
> This doesn't seem right to me -- how can the optimizer possibly know that a
> function doesn't have a side effect, as in my case? Functions could do all
> sorts of things, such as logging activity, filling in other tables, etc, etc.
The optimizer can know this if the user tells it so by marking the function
IMMUTABLE. If the function is marked VOLATILE then the optimizer can know it
might have side effects.
However that's not enough to explain what you've shown. How about you show the
actual query and actual plan you're working with? The plan you've shown can't
result from the query you sent.
--
greg
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: function not called if part of aggregate
2006-06-11 17:18 function not called if part of aggregate Craig A. James <cjames@modgraph-usa.com>
2006-06-11 18:00 ` Re: function not called if part of aggregate Greg Stark <gsstark@mit.edu>
@ 2006-06-13 06:26 ` Craig A. James <cjames@modgraph-usa.com>
2006-06-13 14:36 ` Re: function not called if part of aggregate Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Craig A. James @ 2006-06-13 06:26 UTC (permalink / raw)
To: pgsql-performance
Greg Stark wrote:
> However that's not enough to explain what you've shown. How about you show the
> actual query and actual plan you're working with? The plan you've shown can't
> result from the query you sent.
Mea culpa, sort of. But ... in fact, the plan I sent *was* from query I sent, with the table/column names changed for clarity. This time I'll send the plan "raw". (This is PG 8.0.1.)
chm=> explain select count(1) from (select normalize_add_salt(smiles) from
chm(> salt_smiles order by db_no) as foo;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Aggregate (cost=69.95..69.95 rows=1 width=0)
-> Subquery Scan foo (cost=0.00..67.93 rows=806 width=0)
-> Index Scan using salt_smiles_pkey on salt_smiles (cost=0.00..59.87 rows=806 width=30)
(3 rows)
As pointed out by Tom and others, this query DOES in fact call the normalize_add_salt() function.
Now here's the weird part. (And where my original posting went wrong -- sorry for the error! I got the two queries mixed up.)
I originally had a more complex query, the purpose being to guarantee that the function was called on the strings in the order specified. (More on this below.) Here is the original query I used:
chm=> explain select count(1) from (select normalize_add_salt(smiles)
chm(> from (select smiles from salt_smiles order by db_no) as foo) as bar;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Aggregate (cost=67.94..67.94 rows=1 width=0)
-> Subquery Scan foo (cost=0.00..65.92 rows=806 width=0)
-> Index Scan using salt_smiles_pkey on salt_smiles (cost=0.00..57.86 rows=806 width=30)
(3 rows)
Notice that the plans are essentially identical, yet in this one the function does NOT get called. I proved this by brute force, inserting "char **p = NULL; *p = "foo";" into the C code to guarantee a segmentation violation if the function gets called. In the first case it does SIGSEGV, and in the second case it does not.
Now the reason for this more-complex query with an additional subselect is that the SMILES (which, by the way, are a lexical way of representing chemical structures - see www.daylight.com), must be passed to the function in a particular order (hence the ORDER BY). In retrospect I realize the optimizer apparently flattens this query anyway (hence the identical plans, above).
But the weird thing is that, in spite of flattening, which would appear to make the queries equivalent, the function gets called in one case, and not in the other.
Steinar H. Gunderson asked:
>> select count(1) from (select foo_init(value) from foo_init_table order by
>> value_id) as foo;
> Why not just count(foo_init(value))?
Because the SMILES must be processed in a specific order, hence the more complex queries.
The simple answer to this whole problem is what Steinar wrote:
>>This works well, but it requires me to actually retrieve the function's
>>value 800 times.
>
> Is this actually a problem?
No, it's just a nuisance. It occurs to me that in spite of the ORDER BY expression, Postgres is free to evaluate the function first, THEN sort the results, which means the SMILES would be processed in random order anyway. I.e. my ORDER BY clause is useless for the intended purpose.
So the only way I can see to get this right is to pull the SMILES into my application with the ORDER BY to ensure I have them in the correct order, then send them back one at a time via a "select normalize_add_salt(smiles)", meaning I'll retrieve 800 strings and then send them back.
I just thought there ought to be a way to do this all on the PG server instead of sending all these strings back and forth. I'd like to say to Postgres, "Just do it this way, OK?" But the optimizer can't be turned off, so I guess I have to do it the slow way. The good news is that this is just an initialization step, after which I typically process thousands of molecules, so the extra overhead won't kill me.
Thanks to all for your help.
Craig
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: function not called if part of aggregate
2006-06-11 17:18 function not called if part of aggregate Craig A. James <cjames@modgraph-usa.com>
2006-06-11 18:00 ` Re: function not called if part of aggregate Greg Stark <gsstark@mit.edu>
2006-06-13 06:26 ` Re: function not called if part of aggregate Craig A. James <cjames@modgraph-usa.com>
@ 2006-06-13 14:36 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2006-06-13 14:36 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance
"Craig A. James" <cjames@modgraph-usa.com> writes:
> But the weird thing is that, in spite of flattening, which would appear to make the queries equivalent, the function gets called in one case, and not in the other.
No, nothing particularly weird about it. ORDER BY in a subselect
acts as an "optimization fence" that prevents flattening. An
un-flattened subquery will evaluate all its output columns whether the
parent query reads them or not. (This is not set in stone mind you,
but in the current planner implementation it's hard to avoid, because
such a sub-query gets planned before we've figured out which columns
the parent wants to reference.) The cases in which you had the function
in a subquery without ORDER BY were flattenable, and in that case the
planner threw the function expression away as being unreferenced.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-06-13 14:36 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-11 17:18 function not called if part of aggregate Craig A. James <cjames@modgraph-usa.com>
2006-06-11 17:31 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2006-06-11 17:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-11 17:48 ` Jim C. Nasby <jnasby@pervasive.com>
2006-06-11 18:00 ` Greg Stark <gsstark@mit.edu>
2006-06-13 06:26 ` Craig A. James <cjames@modgraph-usa.com>
2006-06-13 14:36 ` Tom Lane <tgl@sss.pgh.pa.us>
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