Hi all The test data and sql is in the following mail.
http://www.postgresql.org/message-id/sg2pr06mb114954351fa5dae7566854a984...@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
