Received: from nw-smtp.wineasy.se (nw-smtp-02.wineasy.se [195.42.210.227]) by postgresql.org (Postfix) with ESMTP id 84D23475481 for ; Mon, 22 Apr 2002 07:34:50 -0400 (EDT) Received: from shire (202.234.88.213.host.songnetworks.se [213.88.234.202]) by nw-smtp.wineasy.se (Postfix) with ESMTP id C76203FA49 for ; Mon, 22 Apr 2002 13:34:52 +0200 (CEST) Received: from dl by shire with local (Exim 3.35 #1 (Debian)) id 16zc5s-0006kX-00 for ; Mon, 22 Apr 2002 13:34:52 +0200 Date: Mon, 22 Apr 2002 13:34:52 +0200 From: Daniel Lundin To: pgsql-sql@postgresql.org Subject: Identity not disvoered by planner? Message-ID: <20020422113452.GA24158@shire> Mail-Followup-To: pgsql-sql@postgresql.org Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.3.28i Organization: Helena & Daniel AB X-Archive-Number: 200204/265 X-Sequence-Number: 7529 I have an hierarchical table which I join on id = parent. I'm surprised that the planner doesn't seem to notice that parent.id = child.parent and parent.id = 1193 implies that child.parent = 1193. As displayed below it takes different paths when I explicitly restrict the query on both keys or only on the parent. Furthermore, it takes the same path, but estimates the cost differently when I restrict on both or only on the child rows. (The query below is simplified to illustrate the plan. I need the join in the real query.) I'm running 7.2.1 on Red Hat 7.2. 1. Query only restricted on parent.id: easytest=# explain select easytest-# parent.id, easytest-# count(*) easytest-# from easytest-# t_object parent, easytest-# t_object child easytest-# where easytest-# parent.id = child.parent and easytest-# parent.id = 1193 easytest-# group by easytest-# parent.id easytest-# ; NOTICE: QUERY PLAN: Aggregate (cost=18.82..18.82 rows=1 width=8) -> Group (cost=18.82..18.82 rows=1 width=8) -> Sort (cost=18.82..18.82 rows=1 width=8) -> Hash Join (cost=5.40..18.81 rows=1 width=8) -> Seq Scan on t_object child (cost=0.00..11.60 rows=360 width=4) -> Hash (cost=5.39..5.39 rows=1 width=4) -> Index Scan using t_object_pkey on t_object parent (cost=0.00..5.39 rows=1 width=4) EXPLAIN 2. Query restricted both on parent.id and child.parent: easytest=# explain select easytest-# parent.id, easytest-# count(*) easytest-# from easytest-# t_object parent, easytest-# t_object child easytest-# where easytest-# parent.id = child.parent and easytest-# parent.id = 1193 and easytest-# child.parent = 1193 easytest-# group by easytest-# parent.id easytest-# ; NOTICE: QUERY PLAN: Aggregate (cost=0.00..10.02 rows=1 width=8) -> Group (cost=0.00..10.01 rows=1 width=8) -> Nested Loop (cost=0.00..10.01 rows=1 width=8) -> Index Scan using t_object_pkey on t_object parent (cost=0.00..5.39 rows=1 width=4) -> Index Scan using xt_object_parent on t_object child (cost=0.00..4.60 rows=1 width=4) EXPLAIN 3. Query restricted only on child.parent: easytest=# explain select easytest-# parent.id, easytest-# count(*) easytest-# from easytest-# t_object parent, easytest-# t_object child easytest-# where easytest-# parent.id = child.parent and easytest-# child.parent = 1193 easytest-# group by easytest-# parent.id easytest-# ; NOTICE: QUERY PLAN: Aggregate (cost=0.00..18.08 rows=1 width=8) -> Group (cost=0.00..18.08 rows=2 width=8) -> Nested Loop (cost=0.00..18.07 rows=2 width=8) -> Index Scan using xt_object_parent on t_object child (cost=0.00..6.72 rows=2 width=4) -> Index Scan using t_object_pkey on t_object parent (cost=0.00..5.39 rows=1 width=4) EXPLAIN /Daniel