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.
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.
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 |