Again: Identity not discovered by planner? - Mailing list pgsql-sql
From | Daniel Lundin |
---|---|
Subject | Again: Identity not discovered by planner? |
Date | |
Msg-id | 20020425130838.GA1495@shire Whole thread Raw |
Responses |
Re: Again: Identity not discovered by planner?
|
List | pgsql-sql |
I asked this question a couple of days ago, but didn't receive any comments, to I try again: Why doesn't these where clauses result in the same plan: from t_object parent, t_object child where parent.id = child.parent and parent.id = 1193 from t_object parent, t_object child where parent.id = child.parent and parent.id = 1193 and child.parent = 1193 -- Only difference ? I would have thought that the planner would notice that child.parent = parent.id = 1193, implies that child.parent = 1193, so the where clauses are equivalent. My problem is that the plan produced by the first where clause is less efficient than the second, and it's hard to rephrase the sql since the select is destined for a view, so I have no control over the details of the where clause. There's more detail below, in my original mail. /Daniel Original mail: 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.82rows=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.01rows=1 width=8) -> Index Scan using t_object_pkey on t_object parent (cost=0.00..5.39 rows=1width=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.07rows=2 width=8) -> Index Scan using xt_object_parent on t_object child (cost=0.00..6.72 rows=2width=4) -> Index Scan using t_object_pkey on t_object parent (cost=0.00..5.39 rows=1 width=4) EXPLAIN /Daniel ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org