Re: Possible to improve query plan?

From: Jeremy Palmer
Subject: Re: Possible to improve query plan?
Date: ,
Msg-id: 666FB8D75E95AE42965A0E76A5E5337E06D2C68482@prdlsmmsg01.ad.linz.govt.nz
(view: Whole thread, Raw)
In response to: Re: Possible to improve query plan?  (Andy Colson)
List: pgsql-performance

Tree view

Possible to improve query plan?  (Jeremy Palmer, )
 Re: Possible to improve query plan?  (Andy Colson, )
  Possible to improve query plan?  (Jeremy Palmer, )
   Re: Possible to improve query plan?  (Andy Colson, )
    Re: Possible to improve query plan?  (Jeremy Palmer, )
   Re: Possible to improve query plan?  ("Ing. Marcos Ortiz Valmaseda", )
    Re: Possible to improve query plan?  (Jeremy Palmer, )
   Re: Possible to improve query plan?  ("Kevin Grittner", )
    Re: Possible to improve query plan?  (Robert Haas, )
     Re: Possible to improve query plan?  ("Kevin Grittner", )
      Re: Possible to improve query plan?  (Tom Lane, )
       Re: Possible to improve query plan?  ("Kevin Grittner", )
        Re: Possible to improve query plan?  (Jeremy Palmer, )
         Re: Possible to improve query plan?  ("Kevin Grittner", )
          Re: Possible to improve query plan?  (Cédric Villemain, )
         Re: Possible to improve query plan?  ("Kevin Grittner", )
          Re: Possible to improve query plan?  (Jeremy Palmer, )
     Re: Possible to improve query plan?  (Tom Lane, )
 Re: Possible to improve query plan?  (Jayadevan M, )
 Re: Possible to improve query plan?  (Tom Lane, )
  Re: Possible to improve query plan?  (Jeremy Palmer, )
  Re: Possible to improve query plan?  (Mladen Gogala, )

Hi Andy,

Yes important omissions:

Server version: 8.4.6
OS Windows Server 2003 Standard Ed :(
The work mem is 50mb.

I tried setting the work_mem to 500mb, but it didn't make a huge difference in query execution time. But then again the
OSdisk caching is probably taking over here. 

Ok here's the new plan with work_mem = 50mb:

http://explain.depesz.com/s/xwv

And here another plan with work_mem = 500mb:

http://explain.depesz.com/s/VmO

Thanks,
Jeremy

-----Original Message-----
From: Andy Colson [mailto:]
Sent: Monday, 17 January 2011 5:57 p.m.
To: Jeremy Palmer
Cc: 
Subject: Re: [PERFORM] Possible to improve query plan?


Hum.. yeah it looks like it takes no time at all to pull data from the individual indexes, and them bitmap them.  I'm
notsure what the bitmap heap scan is, or why its slow.  Hopefully someone smarter will come along. 

Also its weird that explain.depesz.com didnt parse and show your entire plan.  Hum.. you seem to have ending quotes on
someof the lines? 

One other though: quicksort  Memory: 23960kB
It needs 20Meg to sort... It could be your sort is swapping to disk.

What sort of PG version is this?
What are you using for work_mem?  (you could try to bump it up a little (its possible to set for session only, no need
forserver restart) and see if that'd help. 

And sorry, but its my bedtime, good luck though.

-Andy

______________________________________________________________________________________________________

This message contains information, which is confidential and may be subject to legal privilege.
If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message.
If you have received this message in error, please notify us immediately (Phone 0800 665 463 or ) and
destroythe original message. 
LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ.

Thank you.
______________________________________________________________________________________________________


pgsql-performance by date:

From: Jayadevan M
Date:
Subject: Re: Possible to improve query plan?
From: "Ing. Marcos Ortiz Valmaseda"
Date:
Subject: Re: Possible to improve query plan?