Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 9658E73FD6D for ; Sat, 9 Jun 2012 11:39:34 -0300 (ADT) Received: from gwmta.wicourts.gov ([165.219.244.99] helo=gw.wicourts.gov) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SdMp8-0004C8-80 for pgsql-performance@postgresql.org; Sat, 09 Jun 2012 14:39:33 +0000 Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Sat, 09 Jun 2012 09:39:16 -0500 Message-Id: <4FD319BA0200002500048250@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.2 Date: Sat, 09 Jun 2012 09:39:06 -0500 From: "Kevin Grittner" To: Cc: , Subject: Re: how to change the index chosen in plan? Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Pg-Spam-Score: -4.2 (----) X-Archive-Number: 201206/50 X-Sequence-Number: 47076 Rural Hunter wrote: > =E4=BA=8E 2012/6/9 0:39, Kevin Grittner =E5=86=99=E9=81=93: =20 > name =7C current_setting =20 > full_page_writes =7C off =20 There may be exceptions on some file systems, but generally turning this off leaves you vulnerable to possible database corruption if you OS or hardware crashes. =20 > max_connections =7C 2500 =20 Yikes=21 You may want to look in to a connection pooler which can take 2500 client connections and funnel them into a much smaller number of database connections. =20 https://wiki.postgresql.org/wiki/Number_Of_Database_Connections =20 > shared_buffers =7C 60GB =20 You might want to compare your performance with this setting against a smaller setting. Many benchmarks have shown settings about a certain point (like 8MB to 12 MB) to be counter-productive, although a few have shown increased performance going past that. It really seems to depend on your hardware and workload, so you have to test to find the =22sweet spot=22 for your environment. =20 > work_mem =7C 8MB =20 With so many connections, I can understand being this low. One of the advantages of using connection pooling to funnel your user connections into fewer database conncections is that you can boost this, which might help considerably with some types of queries. =20 None of the above, however, really gets to your immediate problem. What is most significant about your settings with regard to the problem query is what=27s *not* in that list. You appear to have a heavily cached active data set, based on the row counts and timings in EXPLAIN ANALYZE output, and you have not adjusted your cost factors, which assume less caching. =20 Try setting these on a connection and then running your queries on that connection. =20 set seq_page_cost =3D 0.1; set random_page_cost =3D 0.1; set cpu_tuple_cost =3D 0.03; =20 > Ok, I get out a simple version of the actualy query. Here is the > explain anaylze without order-by, which is I wanted: > http://explain.depesz.com/s/p1p > > Another with the order-by which I want to avoid: > http://explain.depesz.com/s/ujU =20 You neglected to mention the LIMIT clause in your earlier presentation of the problem. A LIMIT can have a big impact on plan choice. Is the LIMIT 10 part of the actual query you want to optimize? Either way it would be helpful to see the EXPLAIN ANALYZE output for the the query without the LIMIT clause. =20 -Kevin