0

While executing the postgresql explan analyze for a ML query in MADLib, i'm getting output like below. query & query plan

[Query]

EXPLAIN (VERBOSE, ANALYZE) SELECT COUNT(linregr.linregr_predict) FROM( SELECT madlib.linregr_predict(ARRAY[c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27,c28], ARRAY[f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,f25,f26,f27,f28]) FROM higgs_50_linregr_model_coef, higgs_1k_test WHERE higgs_1k_test.f1 >0.7) AS linregr;

[Query Plan]

Aggregate (cost=19158.81..19158.82 rows=1 width=8) (actual time=4.607..4.610 rows=1 loops=1)

-> Nested Loop (cost=0.00..1497.81 rows=117740 width=224) (actual time=0.056..1.827 rows=204 loops=1)

     ->  Seq Scan on higgs_50_linregr_model_coef  (cost=0.00..15.80 rows=580 width=112) (actual time=0.017..0.019 rows=1 loops=1)

     ->  Materialize  (cost=0.00..10.77 rows=203 width=112) (actual time=0.028..1.388 rows=204 loops=1)

           ->  Seq Scan on higgs_1k_test  (cost=0.00..9.75 rows=203 width=112) (actual time=0.018..0.531 rows=204 loops=1)
                 Filter: (f1 > '0.7'::double precision)
                 Rows Removed by Filter: 96

Planning Time: 0.624 ms Execution Time: 4.826 ms

It seems that the ML operation of query is not included in the overall query plan.

I have a wonder that it is right and if so, how postgresql uses the madlib query while executing?

Ji-Hoon
  • 1
  • 1
  • Please post text as text, not as images of text. – jjanes Jul 20 '21 at 01:41
  • Use `VERBOSE` to see the function call. Use the `TEXT` format. – Laurenz Albe Jul 20 '21 at 05:02
  • First & foremost, please DO NOT use images for query\statements etc. it becomes difficult for other to repo or test your code. Secondly utilise these for more granular info: EXPLAIN ANALYZE VERBOSE statement. you can also refer to this for more info: https://www.postgresql.org/docs/13/sql-explain.html – Raj Verma Jul 20 '21 at 08:18
  • @jjanes Sorry for image. This is the query statement. EXPLAIN (format yaml) SELECT COUNT(linregr.linregr_predict) FROM( SELECT madlib.linregr_predict(ARRAY[c1,…,c28], ARRAY[f1,…,f28]) FROM higgs_50_linregr_model_coef, higgs_1k_test WHERE higgs_1k_test.f1 > 0.7 ) AS linregr; – Ji-Hoon Jul 23 '21 at 02:03
  • @Raj Verma Even when using the EXPLAIN VERBOSE to see the query plan. It doesn't have information about madlib.linear_predict operation. – Ji-Hoon Jul 23 '21 at 02:06
  • Not sure what is the objective but the select does show in your plan on the bottom node with the seq scan and the one above that node shows the first table being called in the select. – Raj Verma Aug 03 '21 at 04:45

0 Answers0