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!

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