explain analyze does not report actual rows correctly?
Hi all
The test data and sql is in the following mail.
/messages/by-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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
chang chao <chang-chao@hotmail.com> writes:
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?
No, that's probably correct, seeing that this node is the righthand child
of a mergejoin. The discrepancy is from extra fetches due to the same row
being re-fetched multiple times thanks to mark/restore rescanning. What
explain is reporting is the number of rows pulled from the node, not the
number of unique rows it processed.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers