On Tue, Dec 11, 2012 at 8:25 PM, Huan Ruan <leohuanruan@xxxxxxxxx> wrote: > Hello All > > While investigating switching to Postgres, we come across a query plan that > uses hash join and is a lot slower than a nested loop join. > > I don't understand why the optimiser chooses the hash join in favor of the > nested loop. What can I do to get the optimiser to make a better decision > (nested loop in this case)? I have run analyze on both tables. > > The query is, > > /* > smalltable has about 48,000 records. > bigtable has about 168,000,000 records. > invtranref is char(10) and is the primary key for both tables > */ > SELECT > * > FROM IM_Match_Table smalltable > inner join invtran bigtable on > bigtable.invtranref = smalltable.invtranref .. > " -> Index Scan using pk_invtran on public.invtran bigtable (cost=0.00..267.03 rows=1 width=108)" This looks like the same large-index over-penalty as discussed in the recent thread " Slow query: bitmap scan troubles". Back-patching the log(npages) change is starting to look like a good idea. Cheers, Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
