From: | Daniel Lundin <daniel(at)helena-daniel(dot)se> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Identity not disvoered by planner? |
Date: | 2002-04-22 11:34:52 |
Message-ID: | 20020422113452.GA24158@shire |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | jack | 2002-04-22 11:36:21 | Is this a BUG? Is there anyone has the same problem? |
Previous Message | Tom Lane | 2002-04-21 15:13:46 | Re: Is this a BUG? Is there anyone has the same problem? |