From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-performance@postgresql.org
Subject: Recursive query gets slower when adding an index
Date: Fri, 19 Oct 2012 12:47:08 +0200
Message-ID: <k5rb2q$42s$1@ger.gmane.org> (raw)
Hi,
I have a self-referencing table that defines a hierarchy of projects and sub-projects.
This is the table definition:
CREATE TABLE project
(
project_id integer primary key,
project_name text,
pl_name text,
parent_id integer
);
ALTER TABLE project
ADD CONSTRAINT project_parent_id_fkey FOREIGN KEY (parent_id)
REFERENCES project (project_id)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
The table contains ~11000 rows
The following statement:
with recursive project_tree as (
select project_id,
parent_id,
pl_name as root_pl,
pl_name as sub_pl,
1 as lvl
from project
where parent_id is null
union all
select c.project_id,
c.parent_id,
coalesce(p.root_pl, c.pl_name) as root_pl,
coalesce(c.pl_name, p.sub_pl) as sub_pl,
p.lvl + 1
from project c
join project_tree p on p.project_id = c.parent_id
)
select count(*), max(lvl)
from project_tree
where root_pl <> sub_pl;
usually runs in something like 60-80ms when the parent_id column is *not* indexed.
This is the execution plan without index: http://explain.depesz.com/s/ecCT
When I create an index on parent_id execution time increases to something between 110ms and 130ms
This is the execution plan with index: http://explain.depesz.com/s/xiL
As far as I can tell, the choice for the nested loop is the reason for the (slightly) slower execution.
I increased the statistics for the parent_id column to 10000 (and did an analyze of course) but that didn't change anything.
I have no problem with that performance, so this is more a "I'm curious on why this happens" type of question.
(And I thought you might be interested in this behaviour as well)
My environment:
*Windows 7 Professional 64bit
* PostgreSQL 9.2.1, compiled by Visual C++ build 1600, 64-bit
Regards
Thomas
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: spam_eater@gmx.net
Subject: Re: Recursive query gets slower when adding an index
In-Reply-To: <k5rb2q$42s$1@ger.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox