0

I have hundreds of tables with same structure in a Postgresql-11 db, and I need to perform a same statistics on each of them.

Since the real statistics logic is very complex, in order to simplify my question in here, let's say I need to calculate MAX/MIN/MEAN/STDDEV of each.

I don't want to edit individual SQLs for each single table one by one, since if so I would code hundreds lines of very similary SQL, with an only difference at the table name.

So I used dynamic SQL in a function to do the calculation for one table a time, like this:

DROP FUNCTION IF EXISTS get_features_for;
CREATE OR REPLACE FUNCTION get_features_for(
    IN  table_name VARCHAR,
    OUT result_ RECORD ) LANGUAGE 'plpgsql'
AS $func$

DECLARE sql_string VARCHAR :=
    format('
        SELECT ''%s'' table_name,     -- just for validating
            MAX(feature) max_feature,
            MIN(feature) min_feature,
            AVG(feature) avg_feature,
            STDDEV(feature) std_feature
        FROM "%s";',
        table_name, table_name );

BEGIN
  EXECUTE sql_string INTO result_;
END
$func$;

And then call it with every table names in the db, within a query, like this:

SELECT tbs."table_name", get_features_for( tbs."table_name" )
FROM information_schema."tables" tbs
WHERE tbs.table_schema = 'public' AND tbs.table_type = 'BASE TABLE'
  AND tbs."table_name" LIKE 'analy%'
ORDER BY tbs."table_name";

It works, but the results are similar to the followings:

"table_name", "get_features_for"

"analy_001", "(analy_001,-3,2,0,2.5)"

"analy_002", "(analy_002,-3,2,-1,2)"

"analy_003", "(analy_003,-3,2,0,2)"

You can see that all the result columns have been wrapped into a single string column, and let me can NOT use them conveniently.

I want them to be similar to:

table_name, max_feature, min_feature, avg_feature, std_feature

"analy_001", "analy_001", -3, 2, 0, 2.5

"analy_002", "analy_002", -3, 2, -1, 2

"analy_003", "analy_003", -3, 2, 0, 2

How should I get that?

BTW, I'm using pgAdmin.

Thanks!!!

2 Answers 2

1

The return type of the function is a composite type. The most efficient way to get the individual columns would be a lateral join:

SELECT tbs.table_name, f.*
FROM information_schema.tables tbs
   CROSS JOIN LATERAL get_features_for(tbs.table_name) AS f
WHERE tbs.table_schema = 'public'
  AND tbs.table_type = 'BASE TABLE'
  AND tbs.table_name LIKE 'analy%'
ORDER BY tbs."table_name";

Avoid * and spell out the actual column names in your real query.

1
  • I appreciate it! You are good man!
    – Leon
    Apr 27, 2023 at 8:50
0

You could also do the following - it appears to be quite performant (It makes extensive use of quick string functions, no regexes - all of the code below is available on the fiddle here):

CREATE TABLE test
(
  analysis TEXT NOT NULL
);

Populate:

INSERT INTO test VALUES 
('"analy_001", "(analy_001,-3,2,0,2.5)"'),
('"analy_002", "(analy_002,-3,2,-1,2)"'),
('"analy_003", "(analy_003,-3,2,0,2)"');

and then we run:

SELECT 
  SPLIT_PART(analysis, '", "(', 2)  -- do as much of the  work here as possible!
FROM test;

Result:

split_part
analy_001,-3,2,0,2.5)"
analy_002,-3,2,-1,2)"
analy_003,-3,2,0,2)"

And then:

SELECT
  REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', '')
FROM test_;

Result:

replace
analy_001,-3,2,0,2.5
analy_002,-3,2,-1,2
analy_003,-3,2,0,2

Finally, we run:

SELECT
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 1) AS table_name,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 2) AS min_f,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 3) AS max_f,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 4) AS avg_f,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 5) AS std_f
FROM
  test_;

Result:

table_name  min_f max_f avg_f std_f
 analy_001     -3     2     0   2.5
 analy_002     -3     2    -1     2
 analy_003     -3     2     0     2

Of course, you'll have to feed in your values from your original SQL, but it appears to be quite performant:

SET enable_seqscan = OFF;

and then:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, TIMING)
SELECT
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 1) AS table_name,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 2) AS min_feature,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 3) AS max_feature,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 4) AS avg_feature,
  SPLIT_PART(REPLACE(SPLIT_PART(analysis, '", "(', 2), ')"', ''), ',', 5) AS std_feature
FROM
  test_;

Result:

QUERY PLAN
Bitmap Heap Scan on public.test_  (cost=15.29..89.89 rows=1360 width=160) (actual time=0.028..0.034 rows=3 loops=1)
  Output: split_part(replace(split_part(analysis, '", "('::text, 2), ')"'::text, ''::text), ','::text, 1), split_part(replace(split_part(analysis, '", "('::text, 2), ')"'::text, ''::text), ','::text, 2), split_part(replace(split_part(analysis, '", "('::text, 2), ')"'::text, ''::text), ','::text, 3), split_part(replace(split_part(analysis, '", "('::text, 2), ')"'::text, ''::text), ','::text, 4), split_part(replace(split_part(analysis, '", "('::text, 2), ')"'::text, ''::text), ','::text, 5)
  Heap Blocks: exact=1
  Buffers: shared hit=2
  ->  Bitmap Index Scan on t_ix  (cost=0.00..14.95 rows=1360 width=0) (actual time=0.013..0.013 rows=3 loops=1)
        Buffers: shared hit=1
Planning Time: 0.164 ms
Execution Time: 0.100 ms

A brief diversion about SET enable_seqscan = OFF;.

  • This doesn't actually disable sequential table scans, it just makes them very expensive - see discussion below.

  • Do not do this on production systems, or at least don't do it globally. You could, if and only if you fully understand any consequences, do it on a case-by-case, query-by-query basis, but it's not to be recommended. Today's query hints are tomorrow's bugs - use with caution.

  • The reason I'm doing it here is to force the optimiser to choose the index over a sequential scan. Without enable_seqscan = OFF, the very small sample tables here would cause the optimiser to automatically choose a sequential scan. With a large number of records on a production system, this should not be a problem.

From the documentation here:

enable_seqscan (boolean)

Enables or disables the query planner's use of sequential scan plan types. It is impossible to suppress sequential scans entirely(*), but turning this variable off discourages the planner from using one if there are other methods available. The default is on.

(*) Emphasis mine

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.