Identity not disvoered by planner?

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

Browse pgsql-sql by date

  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?