Re: Bottleneck? - Mailing list pgsql-performance

Hi Scott,

Thanks for you suggestion. I have follow your suggestion by disable
nestloop and have a substantial improvement. Takes 51s now. I have
attached the new query plan in another file.

What I want to ask is, is there any other way to hint the planner to
choose to use merge join rather than nested loop by modifying my SQL?
I did try to sort my second inner join by the join condition, but the
planner still prefer to use nested loop.

As I am afraid changing the system wide configuration will have some
side effect on my other queries.

Here is my SQL.

select * from dummymediastatus rec INNER JOIN ( SELECT volumeGUID ,
MAX(startDatetime) AS msdt FROM dummymediastatus INNER JOIN ( select *
from getcurrentguids(1249281281666,'hardware.volume',null,null) ) AS
cfg ON ( cfg.getcurrentguids = volumeGUID) WHERE startDatetime <=
1249281281666 GROUP BY volumeGUID ) AS rec2 ON (  rec.volumeGUID =
rec2.volumeGUID AND  rec.startDatetime = rec2.msdt ) where  (  ( 1>0
and 1>0 )  and  rec.acsGUID in ( SELECT * FROM resolve('acs0') ) )
order by rec.startDatetime DESC,rec.id DESC;

thanks




On Thu, Aug 6, 2009 at 5:03 PM, Scott Marlowe<scott.marlowe@gmail.com> wrote:
> OK, two things.  First the row estimate starts going way off around
> the time it gets to the hash aggregate / nested loop which seems to be
> making the planner use a bad plan for this many rows.  You can try
> issuing
>
> set enable_nestloop = off;
>
> before running the query and see if that makes it any faster.
>
> Secondly, the first time you run this query you are reading the 1.8G
> table sequentially, and at about 55MB/s, which isn't gonna get faster
> without more / faster drives under your machine.
>
> On Thu, Aug 6, 2009 at 12:50 AM, Ip Wing Kin John<wkipjohn@gmail.com> wrote:
>> Here u go. Both in the same file.
>>
>> On Thu, Aug 6, 2009 at 4:48 PM, Scott Marlowe<scott.marlowe@gmail.com> wrote:
>>> Much better... Looks like I got the second one...
>>>
>>> Can I get the first one too?  Thx.
>>>
>>> On Thu, Aug 6, 2009 at 12:46 AM, Ip Wing Kin John<wkipjohn@gmail.com> wrote:
>>>> Hope you can get it this time.
>>>>
>>>> John
>>>>
>>>> On Thu, Aug 6, 2009 at 4:34 PM, Scott Marlowe<scott.marlowe@gmail.com> wrote:
>>>>> Sorry man, it's not coming through.  Try it this time addressed just to me.
>>>>>
>>>>> On Thu, Aug 6, 2009 at 12:23 AM, Ip Wing Kin John<wkipjohn@gmail.com> wrote:
>>>>>> Hi scott
>>>>>>
>>>>>> I attached the query plan with this email. The top one is the first
>>>>>> run after I restarted my machine. And the bottom one is the second
>>>>>> run.
>>>>>>
>>>>>> I am using PostgreSQL 8.3 on Solaris 10.
>>>>>>
>>>>>> cheers
>>>>>>
>>>>>> On Thu, Aug 6, 2009 at 4:15 PM, Scott Marlowe<scott.marlowe@gmail.com> wrote:
>>>>>>> On Wed, Aug 5, 2009 at 11:21 PM, <wkipjohn@gmail.com> wrote:
>>>>>>>> Sorry post again.
>>>>>>>
>>>>>>> Nope, still mangled.  Can you attach it?
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> John
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> When fascism comes to America, it will be intolerance sold as diversity.
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> John
>>>>
>>>
>>>
>>>
>>> --
>>> When fascism comes to America, it will be intolerance sold as diversity.
>>>
>>
>>
>>
>> --
>> John
>>
>
>
>
> --
> When fascism comes to America, it will be intolerance sold as diversity.
>



--
John