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

2015-10-14 4:24 GMT+02:00 <jkoceniak(at)mediamath(dot)com>:

> The following bug has been logged on the website:
>
> Bug reference: 13677
> Logged by: Jamie Koceniak
> Email address: jkoceniak(at)mediamath(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>