Hi,
Thanks for your reply.
I find the problem in a distributed database based on Postgres (Greenplum). In distributed database
there may be distributed tables:
every single node only contain subpart of the data and combine them all will get the full data
I think it may also be a problem for Postgres's parallel computing.
1. What postgres planner do for parallel scan a table and then join a generate_series() function scan?
2. What postgres planner do for parallel scan a table and then join a generate_series() function scan with a volatile filter?
Thus running the SQL in the above case, since generate_series functions can can be taken as the same every where,
And generate_series join generate_series also have this property: the data is complete in every single node. This property
is very helpful in a distributed join: A distributed table join generate_series function can just join in every local node and then
gather the result back to a single node.
But things are different when there are volatile functions: volatile functions may be in where clause, targetlist and somewhere.
That is why I come up with the above case and ask here.
To be honest, I do not care the push down so much. It is not normal usage to writing volatile functions in where clause.
I just find it lose the property.
Best,
Zhenghua Lyu
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Friday, July 10, 2020 10:10 PM
To: Zhenghua Lyu <zlyu@vmware.com>
Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
Subject: Re: distribute_restrictinfo_to_rels if restrictinfo contains volatile functions
Zhenghua Lyu <zlyu@vmware.com> writes:
> The where clause is "pushed down to the x,y" because it only references these two relations.
Yeah. I agree that it's somewhat unprincipled, but changing it doesn't
seem like a great idea. There are a lot of users out there who aren't
terribly careful about marking their UDFs as non-volatile, but would be
unhappy if the optimizer suddenly crippled their queries because of
being picky about this.
Also, we specifically document that order of evaluation in WHERE clauses
is not guaranteed, so I feel no need to make promises about how often
volatile functions there will be evaluated. (Volatiles in SELECT lists
are a different story.)
This behavior has stood for a couple of decades with few user complaints,
so why are you concerned about changing it?
regards, tom lane