Date: Mon, 17 Jan 2011 08:37:35 -0500 (CST)
From: "Ing. Marcos Ortiz Valmaseda" <mlortiz@uci.cu>
To: Jeremy Palmer <JPalmer@linz.govt.nz>
Cc: pgsql-performance@postgresql.org, Andy Colson <andy@squeakycode.net>
Message-ID: <999618401.47865491295271455031.JavaMail.root@ucimail4.uci.cu>
In-Reply-To: <2074795588.47864911295271390356.JavaMail.root@ucimail4.uci.cu>
Subject: Re: Possible to improve query plan?
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Which is the type of your application? You can see it on the Performance Wh=
ackamole Presentation from Josh Berkus on the=20
PgCon 2009:
- Web application
- Online Transaction Processing (OLTP)
- Data WareHousing (DW)

And based on the type of your application, you can configure the postgresql=
.conf to gain a better performance of your PostgreSQL server.
PostgreSQL postgresql.conf baseline:
 shared_buffers =3D 25% RAM
 work_mem =3D 512K[W] 2 MB[O] 128 MB[D]
 - but no more that RAM/no_connections
 maintenance_work_mem =3D 1/16 RAM
 checkpoint_segments =3D 8 [W], 16-64 [O], [D]
 wal_buffer =3D 1 MB [W], 8 MB [O], [D]
 effective_cache_size =3D 2/3 RAM

Regards
=20

Ing. Marcos Lu=C3=ADs Ort=C3=ADz Valmaseda
Linux User # 418229 && PostgreSQL DBA
Centro de Tecnolog=C3=ADas Gesti=C3=B3n de Datos (DATEC)
http://postgresql.uci.cu
http://www.postgresql.org
http://it.toolbox.com/blogs/sql-apprentice

----- Mensaje original -----
De: "Jeremy Palmer" <JPalmer@linz.govt.nz>
Para: "Andy Colson" <andy@squeakycode.net>
CC: pgsql-performance@postgresql.org
Enviados: Lunes, 17 de Enero 2011 0:13:25 GMT -05:00 Regi=C3=B3n oriental E=
E. UU./Canad=C3=A1
Asunto: Re: [PERFORM] Possible to improve query plan?

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 OS disk caching is probably ta=
king over here.

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

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

And here another plan with work_mem =3D 500mb:

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

Thanks,
Jeremy

-----Original Message-----
From: Andy Colson [mailto:andy@squeakycode.net]=20
Sent: Monday, 17 January 2011 5:57 p.m.
To: Jeremy Palmer
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Possible to improve query plan?


Hum.. yeah it looks like it takes no time at all to pull data from the indi=
vidual indexes, and them bitmap them.  I'm not sure what the bitmap heap sc=
an is, or why its slow.  Hopefully someone smarter will come along.

Also its weird that explain.depesz.com didnt parse and show your entire pla=
n.  Hum.. you seem to have ending quotes on some of 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 (it=
s possible to set for session only, no need for server 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.=20
If you are not the intended recipient, you must not peruse, use, disseminat=
e, distribute or copy this message.
If you have received this message in error, please notify us immediately (P=
hone 0800 665 463 or info@linz.govt.nz) and destroy the original message.
LINZ accepts no responsibility for changes to this email, or for any attach=
ments, after its transmission from LINZ.

Thank you.
___________________________________________________________________________=
___________________________

--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance