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.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
