pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Daniel Lundin <daniel@helena-daniel.se>
To: pgsql-sql@postgresql.org
Subject: Identity not disvoered by planner?
Date: Mon, 22 Apr 2002 13:34:52 +0200
Message-ID: <20020422113452.GA24158@shire> (raw)

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




Message-ID: <20020422113452.GA24158@shire>
Permalink:  ../20020422113452.GA24158@shire/
Also on:    postgresql.org/message-id/20020422113452.GA24158@shire

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: daniel@helena-daniel.se
  Subject: Re: Identity not disvoered by planner?
  In-Reply-To: <20020422113452.GA24158@shire>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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