Hello do you have same configuration? Regards Pavel 2013/7/31 Sandeep Gupta <gupta.sandeep@xxxxxxxxx>: > I have two postgres instances each with a database of same schema. The > dataset in both is ''same'' but for randomness i.e. both contain two tables > pc(did) and tc(pid, did) that have almost > same number of rows and have been generate from same distribution. > > However the query plan for the join turns out to be completely different: on > one join takes 2.3 secs while on the other it takes 7 secs. > > > Here are the statistics: > > for database 1: > size of tc table: 49987585 > size of pc table: 499616 > > join plan: > > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------------- > Aggregate (cost=1534125.08..1534125.09 rows=1 width=0) (actual > time=8473.296..8473.296 rows=1 loops=1) > -> Merge Join (cost=2.48..1514765.90 rows=7743672 width=0) (actual > time=0.084..8409.065 rows=998038 loops=1) > Merge Cond: (pc.did = tc.did) > -> Index Only Scan using pc_did_idx on pc (cost=0.00..12987.04 > rows=499616 width=4) (actual time=0.016..58.202 rows=499616 loops=1) > Heap Fetches: 0 > -> Index Only Scan using tc_did_idx on tc (cost=0.00..1298125.32 > rows=49987616 width=4) (actual time=0.014..5141.809 rows=49997291 loops=1) > Heap Fetches: 0 > Total runtime: 8473.337 ms > ' > > Query Running time: 5135 > > > for database 2: > size of tc table: 50012415 > size of pc table: 500384 > > QUERY > PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------ > Aggregate (cost=35279895.52..35279895.53 rows=1 width=0) (actual > time=2501.970..2501.970 rows=1 loops=1) > -> Nested Loop (cost=0.00..35276697.82 rows=1279080 width=0) (actual > time=0.038..2418.766 rows=1000834 loops=1) > -> Index Only Scan using pc_did_idx on pc (cost=0.00..15224.56 > rows=500384 width=4) (actual time=0.017..109.080 rows=500384 loops=1) > Heap Fetches: 500384 > -> Index Only Scan using tc_did_idx on tc (cost=0.00..70.44 > rows=3 width=4) (actual time=0.004..0.004 rows=2 loops=500384) > Index Cond: (did = pc.did) > Heap Fetches: 1000834 > Total runtime: 2502.017 ms > > Query running time: 2090.388 ms > > My question is why is the query plan so different for two datasets that are > really exactly the same. And how can i force the plan to be nested index > scan on > database 1 . > > > -Sandeep > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
