pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAgain: Identity not discovered by planner?
2+ messages / 2 participants
[nested] [flat]
* Again: Identity not discovered by planner?
@ 2002-04-25 13:08 Daniel Lundin <daniel@helena-daniel.se>
2002-04-25 16:57 ` Re: Again: Identity not discovered by planner? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Daniel Lundin @ 2002-04-25 13:08 UTC (permalink / raw)
To: 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.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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Again: Identity not discovered by planner?
2002-04-25 13:08 Again: Identity not discovered by planner? Daniel Lundin <daniel@helena-daniel.se>
@ 2002-04-25 16:57 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2002-04-25 16:57 UTC (permalink / raw)
To: Daniel Lundin <daniel@helena-daniel.se>; +Cc: pgsql-sql
Daniel Lundin <daniel@helena-daniel.se> writes:
> I would have thought that the planner would notice that child.parent =
> parent.id = 1193, implies that child.parent = 1193,
Nope, it does not.
It will notice transitive equality of variables, but that happens to
fall out of processing that it has to do anyway to recognize sort keys
for mergejoins. Constants aren't interesting for join planning so they
are not handled in that code.
Offhand I do not see a way of recognizing this case that wouldn't expend
a great deal more cycles than it's likely to be worth.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-04-25 16:57 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-25 13:08 Again: Identity not discovered by planner? Daniel Lundin <daniel@helena-daniel.se>
2002-04-25 16:57 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox