explain analyze does not report actual rows correctly?

From: chang chao <chang-chao(at)hotmail(dot)com>
To: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: explain analyze does not report actual rows correctly?
Date: 2016-05-18 16:01:59
Message-ID: SG2PR06MB1149BF2F840EB97707A4FD4B84490@SG2PR06MB1149.apcprd06.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi all

The test data and sql is in the following mail.

http://www.postgresql.org/message-id/SG2PR06MB114954351FA5DAE7566854A984490@SG2PR06MB1149.apcprd06.prod.outlook.com

After I disabled hash join and nested-loop join(set enable_hashjoin = off; set enable_nestloop = off;) and execute the query again,the query plan changes to the following one .

"Merge Join (cost=3179.24..3183.55 rows=200 width=1044) (actual time=2.409..8.969 rows=9999 loops=1)"
" Merge Cond: (als.node_no = l1.level1_no)"
" -> Merge Join (cost=3168.59..3249.95 rows=200 width=528) (actual time=2.362..5.933 rows=9999 loops=1)"
" Merge Cond: (als.node_no = l2.parent_no)"
" -> Index Scan using all_level_status_pkey on all_level_status als (cost=0.29..109.10 rows=200 width=4) (actual time=0.023..0.152 rows=200 loops=1)"
" Index Cond: (level = 1)"
" Filter: (status = 0)"
" -> Materialize (cost=3168.30..3218.30 rows=9999 width=524) (actual time=2.334..3.888 rows=9999 loops=1)"
" -> Sort (cost=3168.30..3193.30 rows=9999 width=524) (actual time=2.330..2.778 rows=9999 loops=1)"
" Sort Key: l2.parent_no"
" Sort Method: quicksort Memory: 853kB"
" -> Seq Scan on level2_table l2 (cost=0.00..144.99 rows=9999 width=524) (actual time=0.007..0.924 rows=9999 loops=1)"
" -> Sort (cost=10.64..11.14 rows=200 width=520) (actual time=0.045..0.561 rows=9950 loops=1)"
" Sort Key: l1.level1_no"
" Sort Method: quicksort Memory: 34kB"
" -> Seq Scan on level1_table l1 (cost=0.00..3.00 rows=200 width=520) (actual time=0.006..0.016 rows=200 loops=1)"
"Planning time: 1.379 ms"
"Execution time: 9.422 ms"

The actual rows(rows=9950) part in the following line contained in the above query plan seems strange.
" -> Sort (cost=10.64..11.14 rows=200 width=520) (actual time=0.045..0.561 rows=9950 loops=1)"
Shouldn't it be 200?

Chao.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Craig Ringer 2016-05-18 16:27:33 Re: foreign table batch inserts
Previous Message Jeff Janes 2016-05-18 15:52:38 Re: Reviewing freeze map code