Thread: BUG #13677: CPU 100% for WITH RECURSIVE CTE Queries

The following bug has been logged on the website:

Bug reference:      13677
Logged by:          Jamie Koceniak
Email address:      jkoceniak@mediamath.com
PostgreSQL version: 9.1.13
Operating system:   Debian GNU/Linux 7 (wheezy)
Description:

Hi,

Are there any known bugs/issues with RECURSIVE CTE Queries? We have the
classic parent/child table (with only 82K total records). The hierarchy goes
as deep as 10 levels. We turned a WITH RECURSIVE query into a view (see
below). When I perform a simple count against this view, CPU goes to 100%
for the process. Also, as more and more concurrent queries are run against
this view, there is a huge decrease in performance, every process uses 100%
CPU. The query should run in about 120ms but ends up taking several
minutes.

Here is our view definition:
View definition:
 WITH RECURSIVE path AS (
                 SELECT a.id, a.name::text || ''::text AS path
                   FROM table1 a
                  WHERE a.parent_id IS NULL
        UNION ALL
                 SELECT a.id, (p.path || ' - '::text) || a.name::text AS
path
                   FROM table1 a, path p
                  WHERE p.id = a.parent_id
        )
 SELECT a.id, p.path AS full_path, ( SELECT count(*) AS count
           FROM table1 child
          WHERE child.parent_id = a.id) AS child_count
   FROM table1 a, path p
  WHERE a.id = p.id
  ORDER BY a.id;

How do we optimize a query like this? Is there a way to rewrite this query
without using recursive cte?

Thanks!

2015-10-14 4:24 GMT+02:00 <jkoceniak@mediamath.com>:

> The following bug has been logged on the website:
>
> Bug reference:      13677
> Logged by:          Jamie Koceniak
> Email address:      jkoceniak@mediamath.com
> PostgreSQL version: 9.1.13
> Operating system:   Debian GNU/Linux 7 (wheezy)
> Description:
>
> Hi,
>
> Are there any known bugs/issues with RECURSIVE CTE Queries? We have the
> classic parent/child table (with only 82K total records). The hierarchy
> goes
> as deep as 10 levels. We turned a WITH RECURSIVE query into a view (see
> below). When I perform a simple count against this view, CPU goes to 100%
> for the process. Also, as more and more concurrent queries are run against
> this view, there is a huge decrease in performance, every process uses 100%
> CPU. The query should run in about 120ms but ends up taking several
> minutes.
>
> Here is our view definition:
> View definition:
>  WITH RECURSIVE path AS (
>                  SELECT a.id, a.name::text || ''::text AS path
>                    FROM table1 a
>                   WHERE a.parent_id IS NULL
>         UNION ALL
>                  SELECT a.id, (p.path || ' - '::text) || a.name::text AS
> path
>                    FROM table1 a, path p
>                   WHERE p.id = a.parent_id
>         )
>  SELECT a.id, p.path AS full_path, ( SELECT count(*) AS count
>            FROM table1 child
>           WHERE child.parent_id = a.id) AS child_count
>    FROM table1 a, path p
>   WHERE a.id = p.id
>   ORDER BY a.id;
>
> How do we optimize a query like this? Is there a way to rewrite this query
> without using recursive cte?
>
> Thanks!
>
>
Hi

do you have necessary indexes?

You can try to rewrite this query to recursive plpgsql function


https://gustavostraube.wordpress.com/2009/11/17/retrieving-an-hierarchical-tree-recursively-with-plpgsql/

Regards

Pavel

>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>