Re: Bottleneck?
От | Ip Wing Kin John |
---|---|
Тема | Re: Bottleneck? |
Дата | |
Msg-id | afee5cf70908092322l6ea73ffewae95e0118b0c7aba@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Bottleneck? (Scott Marlowe <scott.marlowe@gmail.com>) |
Ответы |
Re: Bottleneck?
Re: Bottleneck? |
Список | 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
Вложения
В списке pgsql-performance по дате отправления: