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