Re: experiments in query optimization

On Thu, 1 Apr 2010, Robert Haas wrote:

> On Thu, Apr 1, 2010 at 2:15 PM, Faheem Mitha <faheem(at)email(dot)unc(dot)edu> wrote:

>> I had set the foreign keys in question (on the geno table) to be
>> primary keys. This is because this setup is basically a glorified
>> spreadsheet, and I don't want more than one cell corresponding to a
>> particular tuple of idlink.id and anno.id (the conceptual rows and
>> cols). Since a primary key defines an index, I thought putting indexes
>> on idlink_id and anno_id was redundant. However, it looks like
>> (unsurprisingly) the index corresponding to the primary key is across
>> both columns, which may not be what is wanted for the aforesaid join

> Actually it is what is wanted - that is good.

I see.

>> So, should I add indexes on the individual foreign key cols idlink_id
>> and anno_id after all?
>
> I doubt that would help.

You're sure of this?

> The bottom line may be that you're dealing with hundreds of millions of
> rows here, so things are going to take a long time. Of course you can
> always get more/faster memory, a bigger I/O subsystem, faster
> processors... and it could be that with detailed study there are
> optimizations that could be done even without spending money, but I
> think I'm about tapped out on what I can do over an Internet mailing
> list.

Thanks for your assistance, Robert. It's been educational.

Regards, Faheem.