Again: Identity not discovered by planner? - Mailing 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