1
See below two results returned from a table where I store sqls query execution plan. I use POSTGRESQL I need to capture only the string cost=399301.55 of the result below. The word cost always comes at the beginning, but it doesn’t always come at same position, so it is not possible to simply substring (...). I think one must find the initial and final position every time, to be able to extract only the cost value. If anyone can help me, the expected result for the example below is:
select (.... )
cost=399301
----------------- example string -----------------------
"Sort (cost=399301.55..399301.57 rows=6 width=36)"
" Sort Key: l_returnflag, l_linestatus"
" -> HashAggregate (cost=399301.21..399301.48 rows=6 width=36)"
" -> Seq Scan on h_lineitem (cost=0.00..250095.98 rows=5968209 width=36)"
" Filter: (l_shipdate <= (to_date('1998/12/01'::text, 'YYYY/MM/DD'::text) - '10 days'::interval day))"
---------------- another example string --------------------
"Aggregate (cost=7922058.70..7922058.71 rows=1 width=16)"
" -> Hash Join (cost=1899763.92..7922058.69 rows=1 width=16)"
" Hash Cond: (h_lineitem.l_partkey = h_part.p_partkey)"
" Join Filter: (((h_part.p_brand = 'Brand#13'::bpchar) AND (h_part.p_container = ANY ('{"SM CASE","SM BOX","SM PACK","SM PKG"}'::bpchar[])) AND (h_lineitem.l_quantity >= 4::double precision) AND (h_lineitem.l_quantity <= 14::double precision) AND (h_ (...)"
" -> Seq Scan on h_lineitem (cost=0.00..235156.84 rows=211094 width=32)"
" Filter: ((l_shipmode = ANY ('{AIR,"AIR REG"}'::bpchar[])) AND (l_shipinstruct = 'DELIVER IN PERSON'::bpchar))"
" -> Hash (cost=1183158.46..1183158.46 rows=35278997 width=33)"
" -> Seq Scan on h_part (cost=0.00..1183158.46 rows=35278997 width=33)"
" Filter: (p_size >= 1)"
Use combinations of functions such as POSITION and SUBSTRING, I would think of a user function to treat this , the second step and review , if possible, the https://www.postgresql.org/docs/9.1/static/functions-string.ht ml
– Motta
Forget the template comment ....
– Motta
http://www.postgresonline.com/journal/archives/171-Explain-Plans-PostgreSQL-9.0-Text,-JSON,-XML,-YAML-Part-1-You-Choose.html
– Motta
Ola Mota, I was thinking of doing a function even first by taking the cost= position and the upper limit. And after delimiting the start and end take the value using substring ( from for) .. SELECT POSITION ('.. ' IN 'Aggregate (cost=27726324.40.. 27726324.41 Rows = '); SELECT POSITION ('Rows' IN 'Aggregate.. But it occurred to me to use regular expression, this almost right, I’m only getting the value before the .. (two points) and actually wanted to take the value after the .. (two points) see... select substring('Aggregate (cost=27726324.40.. 27726324.41 Rows =' from '[0-9]+.? [0-9]{2}') ;
– Neto
I don’t know Postgreesql but does this table or other does not already have separate data ?
– Motta
In Oracle I know you have a table called Plantable that has information divided by field, but in postgresql I know an extension that does this pg_stats_plan, but I can not use it for project and version reasons. Unfortunately I have to get that way.
– Neto