pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Again: 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>
  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 16:57  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Daniel Lundin <daniel@helena-daniel.se>
  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