Rewrite recursive CTEs to improve query performance-AnalyticDB(AnalyticDB)-阿里云帮助中心

AnalyticDB for PostgreSQL has limited support for recursive common table expressions (CTEs). In distributed environments, the WorkTableScan operator used in recursive CTE execution is subject to two constraints: motions are prohibited, and the temporary working table must appear at the leftmost side of the JOIN clause. As a result, when a recursive CTE scans a large table across many compute nodes, GPORCA (the Orca optimizer) cannot generate an optimized plan — the query falls back to the Postgres-based planner, which broadcasts the entire joined table to all compute nodes. Query performance degrades severely.

To work around these constraints, rewrite recursive CTEs using PL/pgSQL functions and temporary tables. Because AnalyticDB for PostgreSQL does not apply the same limits to temporary table execution plans, GPORCA can optimize each loop iteration independently — producing a better plan at each step.

Limitations

Keep the following constraints in mind when working with recursive CTEs in AnalyticDB for PostgreSQL:

Motions are prohibited within recursive CTE execution plans.
The WorkTableScan operator must appear at the leftmost side of the JOIN clause.
GPORCA does not support recursive CTEs. Queries that use recursive CTEs automatically fall back to the Postgres-based planner.
Broadcast Motion causes performance degradation. When the Postgres-based planner handles a recursive CTE, it broadcasts the joined table to all compute nodes. On large tables with many compute nodes, this makes queries extremely slow.

How the rewrite works

The rewrite replaces a recursive CTE with a PL/pgSQL function that iterates over a temporary table:

Insert the non-recursive base case into a temporary table at level 1.
On each iteration, join the rows at the current level in the temporary table with the source table to find children, then insert them at the next level.
Track row counts before and after each insert. When no new rows are added (curr_count = prev_count), exit the loop.
Return the full temporary table as the result.

Important

Always include the loop termination check (IF curr_count = prev_count THEN EXIT). Without it, the loop runs until the query times out or you cancel it manually.

Rewrite examples

The examples below use a city table that stores a two-level hierarchy: provinces and their subordinate cities.

Prepare test data

CREATE TABLE city(id varchar(4), pid varchar(4), name varchar(10), gdp int);
INSERT INTO city VALUES('33', NULL, 'Zhejiang Province', 20134);
INSERT INTO city VALUES('3301', '33', 'Hangzhou', 5112);
INSERT INTO city VALUES('3302', '33', 'Ningbo', 3992);
INSERT INTO city VALUES('3303', '33', 'Wenzhou', 2125);
INSERT INTO city VALUES('3304', '33', 'Jiaxing', 1688);
INSERT INTO city VALUES('3306', '33', 'Shaoxing', 1852);
INSERT INTO city VALUES('3305', '33', 'Huzhou', 964);
INSERT INTO city VALUES('3307', '33', 'Jinhua', 1445);
INSERT INTO city VALUES('3308', '33', 'Quzhou', 507);
INSERT INTO city VALUES('3309', '33', 'Zhoushan', 491);
INSERT INTO city VALUES('3310', '33', 'Taizhou', 1486);
INSERT INTO city VALUES('3311', '33', 'Lishui', 472);
INSERT INTO city VALUES('32', NULL, 'Jiangsu Province', 30862);
INSERT INTO city VALUES('3201', '32', 'Nanjing', 4359);
INSERT INTO city VALUES('3202', '32', 'Wuxi', 3584);
INSERT INTO city VALUES('3203', '32', 'Xuzhou', 2118);
INSERT INTO city VALUES('3204', '32', 'Changzhou', 2269);
INSERT INTO city VALUES('3205', '32', 'Suzhou', 5548);
INSERT INTO city VALUES('3206', '32', 'Nantong', 2982);
INSERT INTO city VALUES('3207', '32', 'Lianyungang', 976);
INSERT INTO city VALUES('3208', '32', 'Huai''an', 1257);
INSERT INTO city VALUES('3209', '32', 'Yancheng', 1796);
INSERT INTO city VALUES('3210', '32', 'Yangzhou', 1868);
INSERT INTO city VALUES('3211', '32', 'Zhenjiang', 1372);
INSERT INTO city VALUES('3212', '32', 'Taizhou', 1752);
INSERT INTO city VALUES('3213', '32', 'Suqian', 981);

UNION ALL scenario

Use UNION ALL when duplicate rows are not a concern.

Original recursive CTE:

The following query retrieves the GDP of a province and all its subordinate cities, ordered by GDP descending.

WITH RECURSIVE CTE AS
(
    SELECT id, CAST(name AS varchar(100)), gdp FROM city WHERE name = 'Zhejiang Province'
    UNION ALL
    SELECT son.id, CAST(parent.name || '>' || son.name AS varchar(100)), son.gdp AS name
    FROM city son INNER JOIN CTE parent ON son.pid = parent.id
)
SELECT id, name, gdp FROM CTE ORDER BY gdp DESC;

Execution plan of the original CTE:

Add EXPLAIN before the query to inspect the execution plan. The plan shows a Broadcast Motion that copies all city data to every compute node. On large tables, this is the primary source of performance degradation. The optimizer falls back to the Postgres-based planner.

                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=13568.80..13971.85 rows=28451 width=242)
   Merge Key: city.gdp
   ->  Sort  (cost=13568.80..13592.51 rows=9484 width=242)
         Sort Key: city.gdp DESC
         ->  Recursive Union  (cost=0.00..12942.36 rows=9484 width=242)
               ->  Seq Scan on city  (cost=0.00..155.67 rows=10 width=242)
                     Filter: ((name)::text = 'Zhejiang Province'::text)
               ->  Hash Join  (cost=885.67..1259.70 rows=947 width=242)
                     Hash Cond: ((parent.id)::text = (son.pid)::text)
                     ->  WorkTable Scan on CTE parent  (cost=0.00..1.95 rows=97 width=238)
                     ->  Hash  (cost=520.67..520.67 rows=29200 width=82)
                           ->  Broadcast Motion 3:3  (slice2; segments: 3)  (cost=0.00..520.67 rows=29200 width=82)
                                 ->  Seq Scan on city son  (cost=0.00..131.33 rows=9733 width=82)
 Optimizer: Postgres-based planner
(14 rows)

Rewritten PL/pgSQL function:

The rewrite uses a temporary table with an extra level field to track iteration depth. GPORCA generates a different, optimized plan for each loop iteration.

CREATE OR REPLACE FUNCTION city_gdp(
    target_name varchar(10)
) RETURNS TABLE(
    id varchar(4),
    name varchar(100),
    gdp int
) AS $$
DECLARE prev_count INT := 0;
        curr_count INT := 0;
        curr_level INT := 1;
BEGIN
-- The temporary table mirrors the CTE output, plus a level field for iteration tracking.
CREATE TEMP TABLE temp_result(
    id varchar(4),
    name varchar(100),
    gdp int,
    level int
) ON COMMIT DROP DISTRIBUTED BY(id);

-- Step 1: Insert the non-recursive base case at level 1.
INSERT INTO temp_result
SELECT
    parent.id,
    CAST(parent.name AS varchar(100)),
    parent.gdp,
    1 AS level
FROM city parent
WHERE parent.name = target_name;

prev_count := (SELECT COUNT(*) FROM temp_result);

LOOP
-- (Optional) Run ANALYZE before each insert so GPORCA has up-to-date statistics.
ANALYZE temp_result;

-- Step 2: Join the current level against the source table to find children.
INSERT INTO temp_result
SELECT
    son.id,
    CAST(parent.name || '>' || son.name AS varchar(100)),
    son.gdp,
    parent.level + 1 AS level
FROM city son
INNER JOIN temp_result parent ON parent.id = son.pid
WHERE parent.level = curr_level;

curr_count := (SELECT COUNT(*) FROM temp_result);

-- Step 3: Exit when no new rows were added.
IF curr_count = prev_count THEN EXIT;
END IF;

prev_count := curr_count;
curr_level := curr_level + 1;
END LOOP;

-- Step 4: Return all results.
RETURN QUERY
SELECT
    CTE.id,
    CTE.name,
    CTE.gdp
FROM temp_result CTE
ORDER BY gdp DESC;
END;
$$ LANGUAGE plpgsql;

Execution plans of the rewritten function:

GPORCA generates a distinct plan for each loop iteration. In the first loop, both city and temp_result are redistributed using Redistribute Motion. In the second loop, GPORCA switches join sides based on updated statistics.

-- First loop
LOG:  Insert on temp_result  (cost=0.00..862.04 rows=1 width=24)
  ->  Result  (cost=0.00..0.00 rows=0 width=0)
        ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..862.00 rows=1 width=28)
              Hash Key: city.id
              ->  Hash Join  (cost=0.00..862.00 rows=1 width=34)
                    Hash Cond: ((city.pid)::text = (temp_result_1.id)::text)
                    ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.00 rows=1 width=28)
                          Hash Key: city.pid
                          ->  Seq Scan on city  (cost=0.00..431.00 rows=1 width=28)
                    ->  Hash  (cost=431.00..431.00 rows=1 width=17)
                          ->  Seq Scan on temp_result temp_result_1  (cost=0.00..431.00 rows=1 width=17)
                                Filter: (level = 1)
Optimizer: GPORCA

-- Second loop
LOG:  Insert on temp_result  (cost=0.00..862.04 rows=1 width=24)
  ->  Result  (cost=0.00..0.00 rows=0 width=0)
        ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..862.00 rows=1 width=28)
              Hash Key: city.id
              ->  Hash Join  (cost=0.00..862.00 rows=1 width=43)
                    Hash Cond: ((temp_result_1.id)::text = (city.pid)::text)
                    ->  Seq Scan on temp_result temp_result_1  (cost=0.00..431.00 rows=5 width=27)
                          Filter: (level = 2)
                    ->  Hash  (cost=431.00..431.00 rows=1 width=28)
                          ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.00 rows=1 width=28)
                                Hash Key: city.pid
                                ->  Seq Scan on city  (cost=0.00..431.00 rows=1 width=28)
Optimizer: GPORCA

The plans above are not fully optimized because ANALYZE has not been run on the city table itself. The key point is that GPORCA generates a distinct plan per iteration, adapting to the data seen so far. For details on the ANALYZE statement, see Manually collect statistics.

Query result:

SELECT * FROM city_gdp('Zhejiang Province');

id    name                          gdp
33    Zhejiang Province             20134
3301  Zhejiang Province > Hangzhou  5112
3302  Zhejiang Province > Ningbo    3992
3303  Zhejiang Province > Wenzhou   2125
3306  Zhejiang Province > Shaoxing  1852
3304  Zhejiang Province > Jiaxing   1688
3310  Zhejiang Province > Taizhou   1486
3307  Zhejiang Province > Jinhua    1445
3305  Zhejiang Province > Huzhou    964
3308  Zhejiang Province > Quzhou    507
3309  Zhejiang Province > Zhoushan  491
3311  Zhejiang Province > Lishui    472

UNION scenario (deduplication)

Use UNION instead of UNION ALL when you need to deduplicate rows and prevent infinite recursion caused by cycles in the data.

Simulate duplicate data:

INSERT INTO city VALUES('1111', '1111', 'Virtual City', 1000);
INSERT INTO city VALUES('1111', '1111', 'Virtual City', 1000);

Original recursive CTE with UNION:

WITH RECURSIVE CTE AS
(
    SELECT id, gdp FROM city WHERE name = 'Virtual City'
    UNION
    SELECT son.id, son.gdp AS name FROM city son INNER JOIN CTE parent ON son.pid = parent.id
)
SELECT id, gdp FROM CTE ORDER BY gdp DESC;

Rewritten PL/pgSQL function:

For UNION semantics, create a unique index on the temporary table and use INSERT ... ON CONFLICT DO NOTHING to skip duplicate rows. This is the only structural difference from the UNION ALL version.

CREATE OR REPLACE FUNCTION city_gdp(
    target_name varchar(10)
) RETURNS TABLE(
    id varchar(4),
    gdp int
) AS $$
DECLARE prev_count INT := 0;
        curr_count INT := 0;
        curr_level INT := 1;
BEGIN
CREATE TEMP TABLE temp_result(
    id varchar(4),
    gdp int,
    level int
) ON COMMIT DROP DISTRIBUTED BY(id);

-- UNION difference: create a unique index to enforce deduplication.
CREATE UNIQUE INDEX ON temp_result(id, gdp);

-- Non-recursive base case: use GROUP BY to deduplicate before inserting.
INSERT INTO temp_result
SELECT
    parent.id,
    parent.gdp,
    1 AS level
FROM city parent
WHERE parent.name = target_name
GROUP BY 1, 2;

prev_count := (SELECT COUNT(*) FROM temp_result);

LOOP
ANALYZE temp_result;

-- Recursive part: GROUP BY and ON CONFLICT DO NOTHING handle deduplication.
INSERT INTO temp_result
SELECT
    son.id,
    son.gdp,
    parent.level + 1 AS level
FROM city son
INNER JOIN temp_result parent ON parent.id = son.pid
WHERE parent.level = curr_level
GROUP BY 1, 2, 3
ON CONFLICT DO NOTHING;

curr_count := (SELECT COUNT(*) FROM temp_result);

IF curr_count = prev_count THEN EXIT;
END IF;

prev_count := curr_count;
curr_level := curr_level + 1;
END LOOP;

RETURN QUERY
SELECT
    CTE.id,
    CTE.gdp
FROM temp_result CTE ORDER BY gdp DESC;
END;
$$ LANGUAGE plpgsql;

After creating the function, run SELECT * FROM city_gdp('Virtual City'). The result matches what the original UNION recursive CTE returns.

Performance comparison

The following test measures execution time on a larger dataset: a test_table with 5,000,000 rows distributed across 2 compute nodes.

Prepare test data

CREATE TABLE test_table(
        id varchar(100),
        parent_id varchar(100),
        float1 float,
        float2 float,
        varchar1 varchar(100),
        varchar2 varchar(100)) DISTRIBUTED BY (id);
INSERT INTO test_table VALUES('1-CCCCCCCCCCCCCCCCCCCC', '', 1.01, 1.01, 'AAAAAAAAAAAAAAAAAAAA', 'BBBBBBBBBBBBBBBBBBBB');
INSERT INTO test_table SELECT i || '-CCCCCCCCCCCCCCCCCCCC', '1-CCCCCCCCCCCCCCCCCCCC', 1.01, 1.01, 'AAAAAAAAAAAAAAAAAAAA', 'BBBBBBBBBBBBBBBBBBBB' FROM generate_series(2, 10000) i;
INSERT INTO test_table SELECT i || '-CCCCCCCCCCCCCCCCCCCC', 'test2-CCCCCCCCCCCCCCCCCCCC', 1.01, 1.01, 'AAAAAAAAAAAAAAAAAAAA', 'BBBBBBBBBBBBBBBBBBBB' FROM generate_series(10001, 5000000) i;

Original recursive CTE

WITH RECURSIVE CTE AS (
    SELECT
        parent.id,
        parent.parent_id,
        CAST(parent.id AS varchar(4000)) id_seq,
        parent.float1,
        parent.float2,
        parent.varchar1,
        parent.varchar2
    FROM test_table parent
    WHERE parent.id IN ('1-CCCCCCCCCCCCCCCCCCCC')
    UNION ALL
    SELECT
        son.id,
        son.parent_id,
        CAST(CTE.id_seq || '>' || son.id AS varchar(4000)) id_seq,
        son.float1,
        son.float2,
        son.varchar1,
        son.varchar2
    FROM test_table son
    INNER JOIN CTE ON CTE.id = son.parent_id
) SELECT * FROM CTE;

Execution plan (with EXPLAIN ANALYZE):

                                                                              QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..4008018.27 rows=50000002 width=1404) (actual time=1698.654..1733.896 rows=10000 loops=1)
   ->  Recursive Union  (cost=0.00..3258018.24 rows=25000001 width=628) (actual time=0.062..1694.406 rows=10000 loops=1)
         ->  Seq Scan on test_table parent  (cost=0.00..42563.00 rows=1 width=628) (actual time=0.058..80.130 rows=1 loops=1)
               Filter: ((id)::text = '1-CCCCCCCCCCCCCCCCCCCC'::text)
               Rows Removed by Filter: 2499158
         ->  Hash Join  (cost=194565.00..271545.52 rows=2500000 width=628) (actual time=792.825..806.534 rows=5000 loops=2)
               Hash Cond: ((cte.id)::text = (son.parent_id)::text)
               Extra Text: (seg1)   Hash chain length 1666666.7 avg, 4990000 max, using 3 of 8388608 buckets.
               ->  WorkTable Scan on cte  (cost=0.00..0.20 rows=10 width=734) (actual time=0.002..0.415 rows=5000 loops=2)
               ->  Hash  (cost=111313.00..111313.00 rows=5000000 width=112) (actual time=1591.270..1591.270 rows=5000000 loops=1)
                     Buckets: 8388608  Batches: 1  Memory Usage: 0kB
                     ->  Broadcast Motion 2:2  (slice2; segments: 2)  (cost=0.00..111313.00 rows=5000000 width=112) (actual time=0.138..359.646 rows=5000000 loops=1)
                           ->  Seq Scan on test_table son  (cost=0.00..36313.00 rows=2500000 width=112) (actual time=0.081..179.119 rows=2500841 loops=1)
 Optimizer: Postgres-based planner
 Planning Time: 0.523 ms
   (slice0)    Executor memory: 581K bytes.
   (slice1)    Executor memory: 1224280K bytes avg x 2 workers, 1225892K bytes max (seg1).  Work_mem: 1223292K bytes max.
   (slice2)    Executor memory: 672K bytes avg x 2 workers, 672K bytes max (seg0).
 Memory used:  8388608kB
 Query Id: 5832811209844029710
 Execution Time: 1773.102 ms
(21 rows)

Rewritten PL/pgSQL function

Before running the function, increase the statement memory to 8 GB:

SET statement_mem TO '8GB';

CREATE OR REPLACE FUNCTION rewrite_query(
    parent_id_arr character varying []
) RETURNS TABLE(
    id varchar(100),
    parent_id varchar(100),
    id_seq varchar(4000),
    float1 float,
    float2 float2,
    varchar1 varchar(100),
    varchar2 varchar(100)
) AS $$
DECLARE prev_count INT := 0;
        curr_count INT := 0;
        curr_level INT := 1;
BEGIN
CREATE TEMP TABLE temp_result(
    id varchar(100),
    parent_id varchar(100),
    id_seq varchar(4000),
    float1 float,
    float2 float2,
    varchar1 varchar(100),
    varchar2 varchar(100),
    level int
) ON COMMIT DROP DISTRIBUTED BY(id);

INSERT INTO temp_result
SELECT
    parent.id,
    parent.parent_id,
    CAST(parent.id AS varchar(4000)) id_seq,
    parent.float1,
    parent.float2,
    parent.varchar1,
    parent.varchar2,
    1 AS level
FROM test_table parent
WHERE parent.id = ANY(parent_id_arr);

prev_count := (SELECT COUNT(*) FROM temp_result);

LOOP
ANALYZE temp_result;

INSERT INTO temp_result
SELECT
    son.id,
    son.parent_id,
    CAST(temp_result.id_seq || '>' || son.id AS varchar(4000)) id_seq,
    son.float1,
    son.float2,
    son.varchar1,
    son.varchar2,
    temp_result.level + 1 AS level
FROM test_table son
    INNER JOIN temp_result ON temp_result.id = son.parent_id
WHERE temp_result.level = curr_level;

curr_count := (SELECT COUNT(*) FROM temp_result);

IF curr_count = prev_count THEN EXIT;
END IF;

prev_count := curr_count;
curr_level := curr_level + 1;
END LOOP;

RETURN QUERY
SELECT
    CTE.id,
    CTE.parent_id,
    CTE.id_seq,
    CTE.float1,
    CTE.float2,
    CTE.varchar1,
    CTE.varchar2
FROM temp_result CTE;
END;
$$ LANGUAGE plpgsql;

Execution plan (with EXPLAIN ANALYZE):

explain analyze SELECT * FROM rewrite_query(array['1-CCCCCCCCCCCCCCCCCCCC']);
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Function Scan on rewrite_query  (cost=0.25..10.25 rows=1000 width=170) (actual time=875.001..875.652 rows=10000 loops=1)
 Optimizer: Postgres-based planner
 Planning Time: 0.040 ms
   (slice0)    Executor memory: 2644K bytes.  Work_mem: 2785K bytes max.
 Memory used:  1048576kB
 Query Id: 338320616919474816
 Execution Time: 875.896 ms
(7 rows)

Results

Query	Execution time	Memory used
Original recursive CTE	1,773 ms	1,224 MB avg x 2 workers (~2.4 GB executor memory)
Rewritten PL/pgSQL function	876 ms	~1 GB (2.8 MB work_mem)
Improvement	~2x faster	~2x less executor memory

The rewritten function runs in about half the time and uses a fraction of the memory, because GPORCA optimizes each iteration without broadcasting the entire table.

Production case

A production workload with more compute nodes and a much larger dataset showed a more dramatic improvement: execution time dropped from 186 seconds to under 2 seconds after the rewrite.

Original recursive CTE

WITH RECURSIVE CTE AS (
    SELECT
        parent.id,
        parent.parent_id,
        CAST(parent.parent_id AS varchar(4000)) id_seq,
        parent.float1,
        parent.float2,
        parent.varchar1,
        parent.varchar2
    FROM test_table parent
    WHERE
        parent.parent_id IN ('test_id1','test_id2')
    UNION ALL
    SELECT
        son.id,
        son.parent_id,
        CAST(CTE.id_seq || '>' || son.parent_id AS varchar(4000)) id_seq,
        son.float1,
        son.float2,
        son.varchar1,
        son.varchar2
    FROM test_table son
    INNER JOIN CTE ON CTE.id = son.parent_id
)
SELECT
    m.varchar1,
    m.varchar3,
    CTE.parent_id,
    CTE.id,
    split_part(CTE.id_seq, '>', 1) id_1,
    CTE.float1,
    SUM(CTE.float2) sum_float2
FROM CTE
INNER JOIN other_table m ON m.varchar1 = CTE.varchar1
GROUP BY
    m.varchar1,
    m.varchar3,
    CTE.parent_id,
    CTE.id,
    id_1,
    CTE.float1
ORDER BY 7 DESC;

Rewritten PL/pgSQL function

CREATE OR REPLACE FUNCTION rewrite_query(
    parent_id_arr character varying []
) RETURNS TABLE(
    varchar1 varchar(100),
    varchar3 varchar(100),
    id varchar(100),
    parent_id varchar(100),
    id_1 text,
    float1 float,
    sum_float2 float
) AS $$
DECLARE prev_count INT := 0;
        curr_count INT := 0;
        curr_level INT := 1;
BEGIN
CREATE TEMP TABLE temp_result(
    id varchar(100),
    parent_id varchar(100),
    id_seq varchar(4000),
    float1 float,
    float2 float2,
    varchar1 varchar(100),
    varchar2 varchar(100),
    level int
) ON COMMIT DROP DISTRIBUTED BY(id);

INSERT INTO temp_result
SELECT
    parent.id,
    parent.parent_id,
    CAST(parent.parent_id AS varchar(4000)) id_seq,
    parent.float1,
    parent.float2,
    parent.varchar1,
    parent.varchar2,
    1 AS level
FROM test_table parent
WHERE parent.parent_id = ANY(parent_id_arr);

prev_count := (SELECT COUNT(*) FROM temp_result);

LOOP
ANALYZE temp_result;

INSERT INTO temp_result
SELECT
    son.id,
    son.parent_id,
    CAST(temp_result.id_seq || '>' || son.parent_id as varchar(4000)) id_seq,
    son.float1,
    son.float2,
    son.varchar1,
    son.varchar2,
    temp_result.level + 1 AS level
FROM test_table son
INNER JOIN temp_result ON temp_result.id = son.parent_id
WHERE temp_result.level = curr_level;

curr_count := (SELECT COUNT(*) FROM temp_result);

IF curr_count = prev_count THEN EXIT;
END IF;

prev_count := curr_count;
curr_level := curr_level + 1;
END LOOP;

RETURN QUERY
SELECT
    m.varchar1,
    m.varchar3,
    CTE.parent_id,
    CTE.id,
    split_part(CTE.id_seq, '>', 1) id_1,
    CTE.float1,
    SUM(CTE.float2) sum_float2
FROM temp_result CTE
INNER JOIN other_table m ON m.varchar1 = CTE.varchar1
GROUP BY
    m.varchar1,
    m.varchar3,
    CTE.parent_id,
    CTE.id,
    id_1,
    CTE.float1
ORDER BY 7 DESC;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM rewrite_query(ARRAY['test_id1', 'test_id2']);