From: "Oleg Kharin" <ok@uvadrev.udmnet.ru>
To: <pgsql-sql@postgresql.org>
Subject: Bad count of rows estimated for emerge join
Date: Sat, 15 Dec 2007 19:15:45 +0400
Message-ID: <000201c83f2d$5dc37d30$194a7790$@udmnet.ru>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Language: ru
Thread-Index: Acg/LV2smY0S1AbsTv670oiegE4ejw==

Hi all,

If I perform EXPLAIN ANALYZE (PostgreSQL 8.2.5) for the query:

SELECT
  _V8TblAli1_IR1._Fld10169RRef AS _Fld10169RRef,
  _V8TblAli1_IR1._Fld10170RRef AS _Fld10170RRef,
  _V8TblAli1_IR1._MAXPERIOD AS _MAXPERIOD,
  SUBSTR(MAX(_InfoReg10168_IR2._RecorderTRef || _InfoReg10168_IR2._RecorderRRef), (1)::int4, (4)::int4) AS _MAXRECORDERTRef,
  SUBSTR(MAX(_InfoReg10168_IR2._RecorderTRef || _InfoReg10168_IR2._RecorderRRef), (5)::int4, (16)::int4) AS _MAXRECORDERRRef
FROM
  (
     SELECT
       _InfoReg10168._Fld10169RRef AS _Fld10169RRef,
       _InfoReg10168._Fld10170RRef AS _Fld10170RRef,
       MAX(_InfoReg10168._Period) AS _MAXPERIOD
     FROM
       _InfoReg10168
     WHERE
       _InfoReg10168._Period <= '2007-10-31 23:59:59'::timestamp AND _InfoReg10168._Active = TRUE
     GROUP BY
       _InfoReg10168._Fld10169RRef,
       _InfoReg10168._Fld10170RRef
   ) _V8TblAli1_IR1

   INNER JOIN _InfoReg10168 _InfoReg10168_IR2
   ON _V8TblAli1_IR1._Fld10169RRef = _InfoReg10168_IR2._Fld10169RRef 
      AND _V8TblAli1_IR1._Fld10170RRef = _InfoReg10168_IR2._Fld10170RRef 
      AND _V8TblAli1_IR1._MAXPERIOD = _InfoReg10168_IR2._Period

WHERE
  _InfoReg10168_IR2._Active = TRUE
GROUP BY
  _V8TblAli1_IR1._Fld10169RRef,
  _V8TblAli1_IR1._Fld10170RRef,
  _V8TblAli1_IR1._MAXPERIOD

then I get the following plan:

HashAggregate  (cost=3647.11..3647.14 rows=1 width=100) (actual time=266.945..285.447 rows=16789 loops=1)
  ->  Merge Join  (cost=2290.92..3647.10 rows=1 width=100) (actual time=97.977..180.467 rows=16791 loops=1)
        Merge Cond: ((_inforeg10168_ir2._fld10169rref = _v8tblali1_ir1._fld10169rref) AND (_inforeg10168_ir2._period =
_v8tblali1_ir1._maxperiod))
        Join Filter: (_v8tblali1_ir1._fld10170rref = _inforeg10168_ir2._fld10170rref)
        ->  Index Scan using _infor10168_bydims22247_rtrn on _inforeg10168 _inforeg10168_ir2  (cost=0.00..1136.70 rows=18972
width=76) (actual time=0.012..20.989 rows=18972 loops=1)
              Filter: _active
        ->  Sort  (cost=2290.92..2332.07 rows=16458 width=72) (actual time=97.928..110.774 rows=16791 loops=1)
              Sort Key: _v8tblali1_ir1._fld10169rref, _v8tblali1_ir1._maxperiod
              ->  HashAggregate  (cost=768.02..973.75 rows=16458 width=48) (actual time=39.205..54.459 rows=16789 loops=1)
                    ->  Seq Scan on _inforeg10168  (cost=0.00..626.15 rows=18916 width=48) (actual time=0.006..14.709 rows=18957
loops=1)
                          Filter: ((_period <= '2007-10-31 23:59:59'::timestamp without time zone) AND _active)
Total runtime: 294.408 ms

The table _inforeg10168 is created by:

CREATE TABLE _inforeg10168
(
  _period timestamp without time zone NOT NULL,
  _recordertref bytea NOT NULL,
  _recorderrref bytea NOT NULL,
  _lineno numeric(9) NOT NULL,
  _active boolean NOT NULL,
  _fld10169rref bytea NOT NULL,
  _fld10170rref bytea NOT NULL,
  _fld10171rref bytea NOT NULL,
  _fld10172rref bytea NOT NULL
)
WITH (OIDS=FALSE);

Why planner estimates rows=1 for merge join? Actually there are 16791 rows.

Oleg.