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@xxxxxxxxx> 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@xxxxxxxxx> wrote:
>> Here u go. Both in the same file.
>>
>> On Thu, Aug 6, 2009 at 4:48 PM, Scott Marlowe<scott.marlowe@xxxxxxxxx> 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@xxxxxxxxx> wrote:
>>>> Hope you can get it this time.
>>>>
>>>> John
>>>>
>>>> On Thu, Aug 6, 2009 at 4:34 PM, Scott Marlowe<scott.marlowe@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> wrote:
>>>>>>> On Wed, Aug 5, 2009 at 11:21 PM, <wkipjohn@xxxxxxxxx> 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
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=710118.74..710118.75 rows=3 width=567) (actual time=51563.029..51580.020 rows=80963 loops=1)
Sort Key: rec.startdatetime, rec.id
Sort Method: quicksort Memory: 43163kB
-> Hash Join (cost=360922.21..710118.72 rows=3 width=567) (actual time=43014.876..51132.786 rows=80963 loops=1)
Hash Cond: ((rec.acsguid)::text = (resolve.resolve)::text)
-> Merge Join (cost=360655.21..709851.67 rows=3 width=567) (actual time=42458.324..50434.884 rows=80963 loops=1)
Merge Cond: (rec.startdatetime = (max(dummymediastatus.startdatetime)))
Join Filter: ((rec.volumeguid)::text = (dummymediastatus.volumeguid)::text)
-> Index Scan using index_dummymediastatus_startdatetime on dummymediastatus rec (cost=0.00..339020.12 rows=4000362 width=414) (actual time=41.617..6324.895 rows=3999952 loops=1)
-> Sort (cost=360655.21..360664.23 rows=3608 width=153) (actual time=42416.687..42453.669 rows=81934 loops=1)
Sort Key: (max(dummymediastatus.startdatetime))
Sort Method: quicksort Memory: 5174kB
-> HashAggregate (cost=360360.86..360405.96 rows=3608 width=16) (actual time=42257.696..42309.261 rows=80000 loops=1)
-> Hash Join (cost=335135.05..354817.67 rows=1108637 width=16) (actual time=37252.925..39518.267 rows=4000000 loops=1)
Hash Cond: ((getcurrentguids.getcurrentguids)::text = (dummymediastatus.volumeguid)::text)
-> Function Scan on getcurrentguids (cost=0.00..260.00 rows=1000 width=32) (actual time=530.526..551.294 rows=80000 loops=1)
-> Hash (cost=285135.53..285135.53 rows=3999962 width=16) (actual time=36722.012..36722.012 rows=4000000 loops=1)
-> Seq Scan on dummymediastatus (cost=0.00..285135.53 rows=3999962 width=16) (actual time=0.014..34178.595 rows=4000000 loops=1)
Filter: (startdatetime <= 1249281281666::bigint)
-> Hash (cost=264.50..264.50 rows=200 width=32) (actual time=556.523..556.523 rows=1 loops=1)
-> HashAggregate (cost=262.50..264.50 rows=200 width=32) (actual time=556.518..556.519 rows=1 loops=1)
-> Function Scan on resolve (cost=0.00..260.00 rows=1000 width=32) (actual time=556.510..556.510 rows=1 loops=1)
Total runtime: 51713.047 ms
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
