Re: experiments in query optimization

От: Faheem Mitha
Тема: Re: experiments in query optimization
Дата: ,
Msg-id: alpine.DEB.2.00.1004020028220.17251@orwell.homelinux.org
(см: обсуждение, исходный текст)
Ответ на: Re: experiments in query optimization  (Robert Haas)
Ответы: Re: experiments in query optimization  (Eliot Gable)
Список: pgsql-performance

Скрыть дерево обсуждения

experiments in query optimization  (Faheem Mitha, )
 Re: experiments in query optimization  (Robert Haas, )
  Re: experiments in query optimization  (Faheem Mitha, )
   Re: experiments in query optimization  (Robert Haas, )
    Re: experiments in query optimization  (Faheem Mitha, )
   Re: experiments in query optimization  ("Kevin Grittner", )
    Re: experiments in query optimization  (Faheem Mitha, )
     Re: experiments in query optimization  (Robert Haas, )
      Re: experiments in query optimization  (Faheem Mitha, )
       Re: experiments in query optimization  (Robert Haas, )
       Re: experiments in query optimization  (Faheem Mitha, )
        Re: experiments in query optimization  (Eliot Gable, )
         Re: experiments in query optimization  (Faheem Mitha, )
          Re: experiments in query optimization  (Robert Haas, )
           Re: experiments in query optimization  (Faheem Mitha, )
            Re: experiments in query optimization  (Eliot Gable, )
             Re: experiments in query optimization  (Faheem Mitha, )
     Re: experiments in query optimization  (Matthew Wakeling, )
      Re: experiments in query optimization  (Faheem Mitha, )
    Re: experiments in query optimization  (Faheem Mitha, )


On Thu, 1 Apr 2010, Robert Haas wrote:

> On Thu, Apr 1, 2010 at 2:15 PM, Faheem Mitha <> 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.


В списке pgsql-performance по дате сообщения:

От: Scott Carey
Дата:
Сообщение: Re: Database size growing over time and leads to performance impact
От: Christiaan Willemsen
Дата:
Сообщение: Using high speed swap to improve performance?