Re: experiments in query optimization

From: Faheem Mitha
Subject: Re: experiments in query optimization
Date: ,
Msg-id: alpine.DEB.2.00.1004011708460.26028@orwell.homelinux.org
(view: Whole thread, Raw)
In response to: Re: experiments in query optimization  (Faheem Mitha)
Responses: Re: experiments in query optimization  (Eliot Gable)
List: pgsql-performance

Tree view

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 Wed, 31 Mar 2010, Faheem Mitha wrote:

> On Tue, 30 Mar 2010, Robert Haas wrote:
>
>> On Tue, Mar 30, 2010 at 12:30 PM, Faheem Mitha <>

>>>>>> You might need to create some indices, too.
>>>>>
>>>>> Ok. To what purpose? This query picks up everything from the
>>>>> tables and the planner does table scans, so conventional wisdom
>>>>> and indeed my experience, says that indexes are not going to be so
>>>>> useful.
>>>>
>>>> There are situations where scanning the entire table to build up a
>>>> hash table is more expensive than using an index.  Why not test it?
>>>
>>> Certainly, but I don't know what you and Robert have in mind, and I'm not
>>> experienced enough to make an educated guess. I'm open to specific
>>> suggestions.
>>
>> Try creating an index on geno on the columns that are being used for the
>> join.
>
> Ok, I'll try that. I guess the cols in question on geno are idlink_id and
> anno_id. I thought that I already had indexes on them, but no. Maybe I had
> indexes, but removed them.

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?

                                                           Regards, Faheem.


pgsql-performance by date:

From: Eliot Gable
Date:
Subject: Re: experiments in query optimization
From: Faheem Mitha
Date:
Subject: Re: experiments in query optimization