Re: experiments in query optimization

On Thu, Apr 1, 2010 at 7:46 AM, Faheem Mitha <faheem(at)email(dot)unc(dot)edu> wrote:

> Looking at this more closely, idlink_id and anno_id are primary keys, so
> already have indexes on them, so my understanding (from the docs) is there
> is no purpose in creating them. That's why I removed the indexes that were
> there (back last August, actually, according to my logs). Anyway, doesn't
> look there is anything I can do here. Does anyone have additions or
> corrections to this?
>
>
When you do a join, you typically have a foreign key in one table
referencing a primary key in another table. While designating a foreign key
does put a constraint on the key to ensure referential integrity, it does
not put an index on the column that is being designated as a foreign key. If
I understand correctly, the scan done as the inner loop of the nested loop
scan for the join is going to be your foreign key column, not your primary
key column. Thus, if you have no index on the foreign key column, you will
be forced to do a sequential table scan to do the join. In that case the
hash-based join will almost certainly be faster (especially for such a large
number of rows). If you put an index on the foreign key, then the inner scan
can be an index scan and that might turn out to be faster than building the
hash indexes on all the table rows.

Somebody can correct me if I'm wrong.

--
Eliot Gable

"We do not inherit the Earth from our ancestors: we borrow it from our
children." ~David Brower

"I decided the words were too conservative for me. We're not borrowing from
our children, we're stealing from them--and it's not even considered to be a
crime." ~David Brower

"Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live; not
live to eat.) ~Marcus Tullius Cicero