From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | Huan Ruan <leohuanruan(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: hash join vs nested loop join |
Date: | 2012-12-12 16:28:13 |
Message-ID: | CAMkU=1yLcgQxkDs6Q+0m6kOR7wXHYiReAbZakZ9XzTH2TBN7Cg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Tue, Dec 11, 2012 at 8:25 PM, Huan Ruan <leohuanruan(at)gmail(dot)com> 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 "[PERFORM] Slow query: bitmap scan troubles".
Back-patching the log(npages) change is starting to look like a good idea.
Cheers,
Jeff
From | Date | Subject | |
---|---|---|---|
Next Message | Niels Kristian Schjødt | 2012-12-12 16:46:11 | Re: Do I have a hardware or a software problem? |
Previous Message | Alejandro Carrillo | 2012-12-12 16:26:46 | Read rows deleted |