Thread: How the Planner in PGStrom differs from PostgreSQL?
I am reading through Postgres and PGStrom. Regarding the planning factors, I need some clarifications. Can u help me with that? Planner in Postgres checks for different scan and join methods, and then find the cheapest one and creates a query plan tree. While going for same thing in GPU, the checks should also be made for, whether it is device executable or not and the query plan tree from Postgres has been updated. How this planning in GPU actually works for? How to determine device executables? What are the factors considered for the planner in GPU? For example, in gpujoin.c, the function responsible for this plan is pgstrom_post_planner_gpujoin. What is the work of this function? What is this actually doing? It is updating some target list. What are they? It is checking for pgstrom_device_expression(tle->expr) i.e., for device executables. What are the tasks covered under this? Best regards -- View this message in context: http://postgresql.nabble.com/How-the-Planner-in-PGStrom-differs-from-PostgreSQL-tp5929724.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Sorry, I got the question a week before from the sender but couldn't respond timely. > I am reading through Postgres and PGStrom. Regarding the planning factors, > I need some clarifications. Can u help me with that? > > Planner in Postgres checks for different scan and join methods, and then > find the cheapest one and creates a query plan tree. While going for same > thing in GPU, the checks should also be made for, whether it is device > executable or not and the query plan tree from Postgres has been updated. > > How this planning in GPU actually works for? How to determine device > executables? What are the factors considered for the planner in GPU? > PostgreSQL v9.5 newly adds custom-scan interface that allows extensions to add its own path prior to selection of the cheapest path. What PG-Strom generally does is (1) check whether the scan qualifier can be executable on GPU device, (2) if yes, construct a CustomScanPath with estimated cost value, (3) then, add_path() to register the CustomScanPath node. If cost of CustomScanPath is cheaper than others, it shall be chosen. Its logic is same with a case when planner choose either of SeqScan or IndexScan. > For example, in gpujoin.c, the function responsible for this plan is > pgstrom_post_planner_gpujoin. What is the work of this function? What is > this actually doing? It is updating some target list. What are they? It > is checking for pgstrom_device_expression(tle->expr) i.e., for device > executables. What are the tasks covered under this? > That is an ad-hoc surgical operation on the plan-tree once constructed. It shall be removed in the v9.6 based implementation because of planner infrastructure improvement. In v9.5, we cannot attach general expression node on the target-list of the plan-node under the create_plan_recurse(), thus, PG-Strom cannot determine how device-side projection works. So, it hooks planner_hook to adjust the plan tree. For example, if final target list contains something like (x * y - z), it is more reasonable to reference the result of calculated result in GPU rather than returning 3 attributed from GPU and calculating on CPU. Unlike your expectation, pgstrom_post_planner_gpujoin() does not perform primary role on GPU path construction. I'd like to follow the code path from the gpujoin_add_join_path(). Best regards, > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Mark Anns > Sent: Thursday, November 10, 2016 4:12 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] How the Planner in PGStrom differs from PostgreSQL? > > I am reading through Postgres and PGStrom. Regarding the planning factors, > I need some clarifications. Can u help me with that? > > Planner in Postgres checks for different scan and join methods, and then > find the cheapest one and creates a query plan tree. While going for same > thing in GPU, the checks should also be made for, whether it is device > executable or not and the query plan tree from Postgres has been updated. > > How this planning in GPU actually works for? How to determine device > executables? What are the factors considered for the planner in GPU? > > For example, in gpujoin.c, the function responsible for this plan is > pgstrom_post_planner_gpujoin. What is the work of this function? What is > this actually doing? It is updating some target list. What are they? It > is checking for pgstrom_device_expression(tle->expr) i.e., for device > executables. What are the tasks covered under this? > > Best regards > > > > -- > View this message in context: > http://postgresql.nabble.com/How-the-Planner-in-PGStrom-differs-from-P > ostgreSQL-tp5929724.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make > changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Thank you so much for your kind reply. I am just curious about this planning factors in GPU. There can be more than one appropriate paths in query plan tree. How the decision for particular path has been made considering those planning factors? -- View this message in context: http://postgresql.nabble.com/How-the-Planner-in-PGStrom-differs-from-PostgreSQL-tp5929724p5930354.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 11/14/2016 9:25 PM, Mark Anns wrote: > I am just curious about this planning factors in GPU. > > There can be more than one appropriate paths in query plan tree. How the > decision for particular path has been made considering those planning > factors? the postgresql planner considers a number of alternate execution plans, assigns each step of each plan a estimated cost and for each plan sums up those costs, the plan with the lowest cost wins. the documentation on EXPLAIN [1] [2] goes into the basics of planning. Chapter 67 [3] goes into some more detail. [1] https://www.postgresql.org/docs/current/static/using-explain.html [2] https://www.postgresql.org/docs/current/static/planner-stats.html [3] https://www.postgresql.org/docs/current/static/row-estimation-examples.html -- john r pierce, recycling bits in santa cruz
Thank you so much for your references. How the planning factors of PGStrom differs from planning factos of PostgreSQL? -- View this message in context: http://postgresql.nabble.com/How-the-Planner-in-PGStrom-differs-from-PostgreSQL-tp5929724p5930356.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 11/14/2016 9:43 PM, Mark Anns wrote: > Thank you so much for your references. > > How the planning factors of PGStrom differs from planning factos of > PostgreSQL? you should probably ask the pgStrom folks this, before your queries, I'd never even heard of it. pgsql-general is primarily for discussions relating to the core database server. -- john r pierce, recycling bits in santa cruz
Mark Anns wrote: > How the planning factors of PGStrom differs from planning factos of > PostgreSQL? I don't know what exactly you mean by a "planning factor". What PGStrom does is estimate the cost of the GPU operations and attach these costs to a custom scan node which is part of a query plan. Many different plans are generated; some will involve PGStrom, some will not. The optimizer then chooses among these plans the one with the lowest total cost. Yours, Laurenz Albe
Yeah I think Kouhei Kaigai is one of the Contributors. So expecting his reply. And thanks for your kind responses -- View this message in context: http://postgresql.nabble.com/How-the-Planner-in-PGStrom-differs-from-PostgreSQL-tp5929724p5930373.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Thanks for your response. But that planning for a query execution in GPU is different from planning a query execution in CPU right? Even considering cost calculation, cost for executing a query in GPU is different from cost for executing a query in CPU. How this cost calculation for GPU occurs? -- View this message in context: http://postgresql.nabble.com/How-the-Planner-in-PGStrom-differs-from-PostgreSQL-tp5929724p5930376.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Can u explain this statement "check whether the scan qualifier can be executable on GPU device" What are the scan qualifiers? How to determine whether they are device executable or not? -- View this message in context: http://postgresql.nabble.com/How-the-Planner-in-PGStrom-differs-from-PostgreSQL-tp5929724p5930781.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Can u explain this statement "check whether the scan qualifier can be executable on GPU device" What are the scan qualifiers? How to determine whether they are device executable or not? The cost estimates are entirely based on number of rows and type of scan. Then it will be same for both CPU and GPU. How the decision can be made for cheaper one comparing CPU and GPU estimates? -- View this message in context: http://postgresql.nabble.com/How-the-Planner-in-PGStrom-differs-from-PostgreSQL-tp5929724p5930783.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Thu, Nov 17, 2016 at 7:09 PM, Mark Anns <aishwaryaanns@gmail.com> wrote: > Can u explain this statement "check whether the scan qualifier can > be executable on GPU device" > > What are the scan qualifiers? > > How to determine whether they are device executable or not? > > The cost estimates are entirely based on number of rows and type of scan. > Then it will be same for both CPU and GPU. How the decision can be made for > cheaper one comparing CPU and GPU estimates? There is a parameter (call it a "factor" if you will) called cpu_tuple_cost (section 19.7.2 Planner Cost Constants in the PostgreSQL documentation; link at the bottom), which "sets the planner's estimate of the cost of processing each row during a query" as the description on that page says. With that as the unit of cost of processing rows using the CPU and considering any differences in the processing capabilities between CPU and GPU, the optimizer code will cost the portion of plan that will be processed by the GPU (typically a table scan or a join) as some fraction of the cost of executing the same portion of the plan using the traditional CPU processing. (Kaigai-san will be better able to explain and correct if the above rough sketch is not exactly accurate) Thanks, Amit [1] https://www.postgresql.org/docs/9.6/static/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS
"fraction of the cost of executing the same portion of the plan using the traditional CPU processing" Can u explain about this fraction in detail? I need the clarifications for query plan tree also. Executing a query in CPU is different from executing the same in GPU. So the plan also differs. How it differs? -- View this message in context: http://postgresql.nabble.com/How-the-Planner-in-PGStrom-differs-from-PostgreSQL-tp5929724p5930903.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
> Can u explain this statement "check whether the scan qualifier can be
> executable on GPU device"
>
> What are the scan qualifiers?
>
SELECT * FROM my_table WHERE x > 20 AND memo LIKE '%abc%';
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
This is scan qualifier.
> How to determine whether they are device executable or not?
>
If all the function (or function on behalf of operator) are
available to transform GPU source code. Please see codegen.c.
> The cost estimates are entirely based on number of rows and type of scan.
> Then it will be same for both CPU and GPU. How the decision can be made
> for cheaper one comparing CPU and GPU estimates?
>
If Scan path has any scan qualifier, its cost to evaluate depends on
the device type. PG-Strom assumes GPU has larger startup cost but
less cost per tuple. So, GpuScan path is tend to be choosen if number
of rows are relatively large.
Thanks,
--
The PG-Strom Project / NEC OSS Promotion Center
KaiGai Kohei <kaigai@ak.jp.nec.com>
> On Thu, Nov 17, 2016 at 7:09 PM, Mark Anns <aishwaryaanns@gmail.com> wrote: > > Can u explain this statement "check whether the scan qualifier can be > > executable on GPU device" > > > > What are the scan qualifiers? > > > > How to determine whether they are device executable or not? > > > > The cost estimates are entirely based on number of rows and type of scan. > > Then it will be same for both CPU and GPU. How the decision can be > > made for cheaper one comparing CPU and GPU estimates? > > There is a parameter (call it a "factor" if you will) called cpu_tuple_cost > (section 19.7.2 Planner Cost Constants in the PostgreSQL documentation; > link at the bottom), which "sets the planner's estimate of the cost of > processing each row during a query" > as the description on that page says. > > With that as the unit of cost of processing rows using the CPU and considering > any differences in the processing capabilities between CPU and GPU, the > optimizer code will cost the portion of plan that will be processed by the > GPU (typically a table scan or a join) as some fraction of the cost of > executing the same portion of the plan using the traditional CPU processing. > > (Kaigai-san will be better able to explain and correct if the above rough > sketch is not exactly accurate) > It is right introduction. PG-Strom assumes GPU can run functions/operators within scan qualifier more effectively than CPU, but has more startup cost (const) and extra data copy via PCI-E bus (another factor based on width x num rows). These factor makes differences in the cost of individual scan/join paths, then planner will choose the appropriate one. Thanks, ---- PG-Strom Project / NEC OSS Promotion Center KaiGai Kohei <kaigai@ak.jp.nec.com>
What are the functions (for example) are available/not available to get transformed to GPU source code? What is the factor value u consider to get multiplied with actual cost for CPU? For example, default cpu_tuple_cost is 0.01. Consider, for example, if the cost=0.00..458.00 for seq scan, how can it be multiplied to get the cost for GPU? considering any one gpu card. Is there any documentation regarding these details in GPU? -- View this message in context: http://postgresql.nabble.com/How-the-Planner-in-PGStrom-differs-from-PostgreSQL-tp5929724p5931271.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.