query: http://pastebin.ca/57218

In the pictures table all the ratings have a shared index=20

CREATE INDEX idx_rating ON pictures USING btree =20 (rating_nudity, rating_violence, rating_sex, rating_racism, = rating_spoilers,=20 rating_yaoi, rating_yuri, rating_profanity);

and approved = and=20 date_submitted and user_id also have their own btree = indexes.

In=20 the picture_categories table pid and cat_id have their own btree indices = plus=20 one together.

Full table definition: http://pastebin.ca/57219

the cat_id and rating values vary from query to query. The one = listed above=20 took 54 seconds in a test run just now. Here is explain analyze: http://pastebin.ca/57220

Both pictures and picture categories have about 287,000=20 rows

This query needs to run in under about a second or it = kills my=20 site by clogging apache slots (apache maxes out at 256 and I can have = several=20 hundred people on my site at a time). How can I make it run=20 faster?

Server is a dual xeon with a gig of ram = dedicated=20 mostly to postgresql.
Here is the changed lines in my = postgresql.conf: http://pastebin.ca/57222

I know hyperthreading is considered something that can slow down a = server=20 but with my very high concurancy (averages about 400-500 concurant users = during=20 peak hours) I am hoping the extra virtual CPUs wil help. Anyone have = experance=20 that says diferent at high concurancy?

------=_NextPart_000_03DD_01C67B5C.D6711260-- From jnasby@pervasive.com Mon May 22 15:20:29 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id CD1179F946D for ; Mon, 22 May 2006 12:20:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 53873-05 for ; Mon, 22 May 2006 12:20:22 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 7D2A79F9316 for ; Mon, 22 May 2006 12:20:21 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id C5A3456431; Mon, 22 May 2006 10:20:20 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Mon, 22 May 2006 10:20:19 -0500 Date: Mon, 22 May 2006 10:20:18 -0500 From: "Jim C. Nasby" To: Cstdenis Cc: pgsql-performance@postgresql.org Subject: Re: How can I make this query faster (resend) Message-ID: <20060522152016.GM64371@pervasive.com> References: <03e001c67b99$8321f010$6401a8c0@chris> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <03e001c67b99$8321f010$6401a8c0@chris> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060522:cstdenis@voicio.com::hfddSE1OaHtJMBN2:000000000000000000 0000000000000000000000006m/7 X-Hashcash: 1:20:060522:pgsql-performance@postgresql.org::F63cFY9yjNGD1Ig3:00000 0000000000000000000000002fjC X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/416 X-Sequence-Number: 19203 On Fri, May 19, 2006 at 03:56:49PM -0700, Cstdenis wrote: > (Its been a hour and I dont see my message on the list so I'm sending it again. I've moved the queries and analyze out of the email incase it was rejected because too long) > > query: http://pastebin.ca/57218 > > In the pictures table all the ratings have a shared index > > CREATE INDEX idx_rating ON pictures USING btree (rating_nudity, rating_violence, rating_sex, rating_racism, rating_spoilers, rating_yaoi, rating_yuri, rating_profanity); > > and approved and date_submitted and user_id also have their own btree indexes. > > In the picture_categories table pid and cat_id have their own btree indices plus one together. > > Full table definition: http://pastebin.ca/57219 > > the cat_id and rating values vary from query to query. The one listed above took 54 seconds in a test run just now. Here is explain analyze: http://pastebin.ca/57220 pictures is the interesting table here. It looks like the planner would do better to choose something other than a nested loop on it. Try running EXPLAIN ANALYZE on the query with enable_nestloop=off and see what you get (you'll need to compare it to what you get with enable_nestloop on to see what the change is). > Both pictures and picture categories have about 287,000 rows > > This query needs to run in under about a second or it kills my site by clogging apache slots (apache maxes out at 256 and I can have several hundred people on my site at a time). How can I make it run faster? > > > Server is a dual xeon with a gig of ram dedicated mostly to postgresql. > Here is the changed lines in my postgresql.conf: http://pastebin.ca/57222 I suspect the low work_mem may be why it's using a nested loop. In addition to the test above, it would be interesting to see what happens to the plan if you set work_mem to 10000. To be honest, you're pushing things expecting a machine with only 1G to serve 300 active connections. How large is the database itself? > I know hyperthreading is considered something that can slow down a server but with my very high concurancy (averages about 400-500 concurant users during peak hours) I am hoping the extra virtual CPUs wil help. Anyone have experance that says diferent at high concurancy? Best bet is to try it and see. Generally, people find HT hurts, but I recently saw it double the performance of pgbench on a windows XP machine, so it's possible that windows is just more clever about how to use it than linux is. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 From schabi@logix-tt.com Mon May 22 15:30:25 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 2FAD79F9A8D for ; Mon, 22 May 2006 12:30:25 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 53873-09 for ; Mon, 22 May 2006 12:30:13 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.logix-tt.com (mail.logix-tt.com [212.211.145.186]) by postgresql.org (Postfix) with ESMTP id 649019F946D for ; Mon, 22 May 2006 12:30:13 -0300 (ADT) Received: from kingfisher.intern.logix-tt.com (Tb043.t.pppool.de [89.55.176.67]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.logix-tt.com (Postfix) with ESMTP id 52666656C0; Mon, 22 May 2006 17:31:30 +0200 (CEST) Received: from [127.0.0.1] (localhost [127.0.0.1]) by kingfisher.intern.logix-tt.com (Postfix) with ESMTP id 9AA89185B60B8; Mon, 22 May 2006 17:30:29 +0200 (CEST) Message-ID: <4471D915.3010109@logix-tt.com> Date: Mon, 22 May 2006 17:30:29 +0200 From: Markus Schaber Organization: Logical Tracking and Tracing International AG, Switzerland User-Agent: Debian Thunderbird 1.0.7 (X11/20051017) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Cstdenis Cc: pgsql-performance@postgresql.org Subject: Re: How can I make this query faster (resend) References: <03e001c67b99$8321f010$6401a8c0@chris> In-Reply-To: <03e001c67b99$8321f010$6401a8c0@chris> X-Enigmail-Version: 0.93.0.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/419 X-Sequence-Number: 19206 Hi, Cstendis, Cstdenis wrote: > Server is a dual xeon with a gig of ram dedicated mostly to postgresql. > Here is the changed lines in my postgresql.conf: http://pastebin.ca/57222 3M is really low for a production server. Try using pg_pool and limiting it to about 30 or so backend connections, and then give them at least 30 megs of RAM each. This should also cut down the connection creation overhead. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org From cstdenis@voicio.com Sun May 28 14:16:37 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 2B2EB9FA326 for ; Sun, 28 May 2006 11:16:37 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 21368-08 for ; Sun, 28 May 2006 11:16:27 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from swordfish.dnsvelocity.com (swordfish.dnsvelocity.com [216.67.224.90]) by postgresql.org (Postfix) with ESMTP id 210089F9F3D for ; Sun, 28 May 2006 11:16:26 -0300 (ADT) Received: from [24.87.22.207] (port=4069 helo=chris) by swordfish.dnsvelocity.com with esmtpa (Exim 4.52) id 1FkM48-0002LR-08; Sun, 28 May 2006 15:16:24 +0100 Message-ID: <001601c68261$4a975c70$6401a8c0@chris> From: "Cstdenis" To: "Jim C. Nasby" Cc: References: <03e001c67b99$8321f010$6401a8c0@chris> <20060522152016.GM64371@pervasive.com> Subject: Re: How can I make this query faster (resend) Date: Sun, 28 May 2006 03:49:06 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1506 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 X-Antivirus-Scanner: Clean mail though you should still use an Antivirus X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - swordfish.dnsvelocity.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - voicio.com X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/505 X-Sequence-Number: 19292 From: "Jim C. Nasby" To: "Cstdenis" Cc: Sent: Monday, May 22, 2006 8:20 AM Subject: Re: [PERFORM] How can I make this query faster (resend) > On Fri, May 19, 2006 at 03:56:49PM -0700, Cstdenis wrote: > > (Its been a hour and I dont see my message on the list so I'm sending it again. I've moved the queries and analyze out of the email incase it was rejected because too long) > > > > query: http://pastebin.ca/57218 > > > > In the pictures table all the ratings have a shared index > > > > CREATE INDEX idx_rating ON pictures USING btree (rating_nudity, rating_violence, rating_sex, rating_racism, rating_spoilers, rating_yaoi, rating_yuri, rating_profanity); > > > > and approved and date_submitted and user_id also have their own btree indexes. > > > > In the picture_categories table pid and cat_id have their own btree indices plus one together. > > > > Full table definition: http://pastebin.ca/57219 > > > > the cat_id and rating values vary from query to query. The one listed above took 54 seconds in a test run just now. Here is explain analyze: http://pastebin.ca/57220 > > pictures is the interesting table here. It looks like the planner would > do better to choose something other than a nested loop on it. Try > running EXPLAIN ANALYZE on the query with enable_nestloop=off and see > what you get (you'll need to compare it to what you get with > enable_nestloop on to see what the change is). With enable_nestloop=off the same query as is explained further down in this email took much longer 63 seconds insted of 6. It decided to do sequencial scans on pictures and users with nested loop disabled. Merge Join (cost=146329.63..146963.96 rows=231 width=66) (actual time=61610.538..62749.176 rows=1305 loops=1) Merge Cond: ("outer".user_id = "inner".user_id) -> Sort (cost=123828.88..123829.46 rows=231 width=47) (actual time=60445.367..60451.176 rows=1305 loops=1) Sort Key: pictures.user_id -> Hash Join (cost=634.36..123819.81 rows=231 width=47) (actual time=128.088..60423.623 rows=1305 loops=1) Hash Cond: ("outer".pid = "inner".pid) -> Seq Scan on pictures (cost=0.00..121670.43 rows=302543 width=47) (actual time=0.210..58795.925 rows=291318 loops=1) -> Hash (cost=633.78..633.78 rows=231 width=4) (actual time=38.443..38.443 rows=1305 loops=1) -> Bitmap Heap Scan on picture_categories (cost=2.81..633.78 rows=231 width=4) (actual time=4.753..32.259 rows=1305 loops=1) Recheck Cond: (cat_id = 182) -> Bitmap Index Scan on idx_picture_categories_cat_id (cost=0.00..2.81 rows=231 width=0) (actual time=4.398..4.398 rows=1305 loops=1) Index Cond: (cat_id = 182) -> Sort (cost=22500.74..22816.79 rows=126418 width=23) (actual time=1163.788..1505.104 rows=52214 loops=1) Sort Key: users.user_id -> Seq Scan on users (cost=0.00..11788.18 rows=126418 width=23) (actual time=0.017..692.992 rows=54605 loops=1) Total runtime: 62776.720 ms > > Both pictures and picture categories have about 287,000 rows > > > > This query needs to run in under about a second or it kills my site by clogging apache slots (apache maxes out at 256 and I can have several hundred people on my site at a time). How can I make it run faster? > > > > > > Server is a dual xeon with a gig of ram dedicated mostly to postgresql. > > Here is the changed lines in my postgresql.conf: http://pastebin.ca/57222 > > I suspect the low work_mem may be why it's using a nested loop. In > addition to the test above, it would be interesting to see what happens > to the plan if you set work_mem to 10000. I moved to a more powerful server (2gb ram and mirrored scsi HDs) and upped the work mem to 10mb. Its much faster now, however its still doing a nested loop. (see also my reply to Markus Schaber) Nested Loop (cost=2.81..3398.76 rows=231 width=66) (actual time=14.946..5797.701 rows=1305 loops=1) -> Nested Loop (cost=2.81..2022.71 rows=231 width=47) (actual time=14.551..5181.042 rows=1305 loops=1) -> Bitmap Heap Scan on picture_categories (cost=2.81..633.78 rows=231 width=4) (actual time=9.966..140.606 rows=1305 loops=1) Recheck Cond: (cat_id = 182) -> Bitmap Index Scan on idx_picture_categories_cat_id (cost=0.00..2.81 rows=231 width=0) (actual time=9.720..9.720 rows=1305 loops=1) Index Cond: (cat_id = 182) -> Index Scan using pictures_pkey on pictures (cost=0.00..6.00 rows=1 width=47) (actual time=3.802..3.820 rows=1 loops=1305) Index Cond: (pictures.pid = "outer".pid) -> Index Scan using users_pkey on users (cost=0.00..5.94 rows=1 width=23) (actual time=0.095..0.100 rows=1 loops=1305) Index Cond: ("outer".user_id = users.user_id) Total runtime: 5812.238 ms > To be honest, you're pushing things expecting a machine with only 1G to > serve 300 active connections. How large is the database itself? The database is 3.7G on disk. There is about 1G of actual data in it -- the rest is dead tuples and indices. (I vacuum regularly, but a vacuum full causes too much downtime to do unless I have to) > > I know hyperthreading is considered something that can slow down a server but with my very high concurancy (averages about 400-500 concurant users during peak hours) I am hoping the extra virtual CPUs wil help. Anyone have experance that says diferent at high concurancy? > > Best bet is to try it and see. Generally, people find HT hurts, but I > recently saw it double the performance of pgbench on a windows XP > machine, so it's possible that windows is just more clever about how to > use it than linux is. Anyone know if those who have found it hurts are low concurancy complex cpu intensive queries or high concurancy simple queries or both? I can understand it hurting in the former, but not the later. I'll have to give it a try I guess. It should at least help my very high load averages. > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > From cstdenis@voicio.com Mon May 29 14:33:55 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id D59529FA16E for ; Mon, 29 May 2006 11:33:55 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 14268-03-3 for ; Mon, 29 May 2006 11:33:47 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from swordfish.dnsvelocity.com (swordfish.dnsvelocity.com [216.67.224.90]) by postgresql.org (Postfix) with ESMTP id A0F1E9FA2C7 for ; Mon, 29 May 2006 11:33:47 -0300 (ADT) Received: from [24.87.22.207] (port=4446 helo=chris) by swordfish.dnsvelocity.com with esmtpa (Exim 4.52) id 1Fkio7-00089L-Jo; Mon, 29 May 2006 15:33:23 +0100 Message-ID: <010201c6832c$d6ab7b80$6401a8c0@chris> From: "Cstdenis" To: "Markus Schaber" Cc: References: <03e001c67b99$8321f010$6401a8c0@chris> <4471D915.3010109@logix-tt.com> Subject: Re: How can I make this query faster (resend) Date: Mon, 29 May 2006 07:33:18 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1506 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 X-Antivirus-Scanner: Clean mail though you should still use an Antivirus X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - swordfish.dnsvelocity.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - voicio.com X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/515 X-Sequence-Number: 19302 (re-sending because my first one forgot the CC to the list. Sorry) I moved my database to a more powerful server. Mirrored ultra 320 SCSI HDs and 2GB of ram. It performs much faster. I also changed some conf settings accordingly work_mem = 10240 shared_buffers = 25600 max_connections = 450 (Also moved the webserver and needed more connections during the DNS propagation). I've been looking into pgpool. If I understand things correctly I can have persistent connections from all 256 apache processes to a pgpool and it can have like 30 persistent connections to the actual server thus saving lots of server memory (due to very high concurrency I would probably actually use at least 100) Is this correct? However, memory doesn't seem to be my problem anymore, the query is still taking longer than I'd like for the larger categories (6 seconds for one with 1300 pictures) but its more managable. The problem now is that my server's load average during peak hours has gone as high as 30 (tho the server seems to still be responding fairly quickly it still worrysome) Given my new server specs can anyone suggest any other config file improvements? Perhaps some of the *_cost variables could be adjusted to better reflect my server's hardware? ----- Original Message ----- From: "Markus Schaber" To: "Cstdenis" Cc: Sent: Monday, May 22, 2006 8:30 AM Subject: Re: [PERFORM] How can I make this query faster (resend) > Hi, Cstendis, > > Cstdenis wrote: > > > Server is a dual xeon with a gig of ram dedicated mostly to postgresql. > > Here is the changed lines in my postgresql.conf: http://pastebin.ca/57222 > > 3M is really low for a production server. > > Try using pg_pool and limiting it to about 30 or so backend connections, > and then give them at least 30 megs of RAM each. > > This should also cut down the connection creation overhead. > > HTH, > Markus > -- > Markus Schaber | Logical Tracking&Tracing International AG > Dipl. Inf. | Software Development GIS > > Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org > From cstdenis@voicio.com Mon May 29 14:35:31 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id C52909FA2C7 for ; Mon, 29 May 2006 11:35:31 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 13123-09 for ; Mon, 29 May 2006 11:35:22 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from swordfish.dnsvelocity.com (swordfish.dnsvelocity.com [216.67.224.90]) by postgresql.org (Postfix) with ESMTP id E5FD69FA16E for ; Mon, 29 May 2006 11:35:22 -0300 (ADT) Received: from [24.87.22.207] (port=4510 helo=chris) by swordfish.dnsvelocity.com with esmtpa (Exim 4.52) id 1Fkiq1-0008IR-4d; Mon, 29 May 2006 15:35:21 +0100 Message-ID: <013801c6832d$1cf73d90$6401a8c0@chris> From: "Cstdenis" To: "Nis Jorgensen" Cc: References: <03e001c67b99$8321f010$6401a8c0@chris> <447177B2.2020109@superlativ.dk> Subject: Re: How can I make this query faster (resend) Date: Mon, 29 May 2006 07:35:14 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1506 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 X-Antivirus-Scanner: Clean mail though you should still use an Antivirus X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - swordfish.dnsvelocity.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - voicio.com X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/516 X-Sequence-Number: 19303 (Resending because my other send didn't get a CC to the list) From: "Jim C. Nasby" To: "Cstdenis" Cc: Sent: Monday, May 22, 2006 8:20 AM Subject: Re: [PERFORM] How can I make this query faster (resend) > On Fri, May 19, 2006 at 03:56:49PM -0700, Cstdenis wrote: > > (Its been a hour and I dont see my message on the list so I'm sending it again. I've moved the queries and analyze out of the email incase it was rejected because too long) > > > > query: http://pastebin.ca/57218 > > > > In the pictures table all the ratings have a shared index > > > > CREATE INDEX idx_rating ON pictures USING btree (rating_nudity, rating_violence, rating_sex, rating_racism, rating_spoilers, rating_yaoi, rating_yuri, rating_profanity); > > > > and approved and date_submitted and user_id also have their own btree indexes. > > > > In the picture_categories table pid and cat_id have their own btree indices plus one together. > > > > Full table definition: http://pastebin.ca/57219 > > > > the cat_id and rating values vary from query to query. The one listed above took 54 seconds in a test run just now. Here is explain analyze: http://pastebin.ca/57220 > > pictures is the interesting table here. It looks like the planner would > do better to choose something other than a nested loop on it. Try > running EXPLAIN ANALYZE on the query with enable_nestloop=off and see > what you get (you'll need to compare it to what you get with > enable_nestloop on to see what the change is). With enable_nestloop=off the same query as is explained further down in this email took much longer 63 seconds insted of 6. It decided to do sequencial scans on pictures and users with nested loop disabled. Merge Join (cost=146329.63..146963.96 rows=231 width=66) (actual time=61610.538..62749.176 rows=1305 loops=1) Merge Cond: ("outer".user_id = "inner".user_id) -> Sort (cost=123828.88..123829.46 rows=231 width=47) (actual time=60445.367..60451.176 rows=1305 loops=1) Sort Key: pictures.user_id -> Hash Join (cost=634.36..123819.81 rows=231 width=47) (actual time=128.088..60423.623 rows=1305 loops=1) Hash Cond: ("outer".pid = "inner".pid) -> Seq Scan on pictures (cost=0.00..121670.43 rows=302543 width=47) (actual time=0.210..58795.925 rows=291318 loops=1) -> Hash (cost=633.78..633.78 rows=231 width=4) (actual time=38.443..38.443 rows=1305 loops=1) -> Bitmap Heap Scan on picture_categories (cost=2.81..633.78 rows=231 width=4) (actual time=4.753..32.259 rows=1305 loops=1) Recheck Cond: (cat_id = 182) -> Bitmap Index Scan on idx_picture_categories_cat_id (cost=0.00..2.81 rows=231 width=0) (actual time=4.398..4.398 rows=1305 loops=1) Index Cond: (cat_id = 182) -> Sort (cost=22500.74..22816.79 rows=126418 width=23) (actual time=1163.788..1505.104 rows=52214 loops=1) Sort Key: users.user_id -> Seq Scan on users (cost=0.00..11788.18 rows=126418 width=23) (actual time=0.017..692.992 rows=54605 loops=1) Total runtime: 62776.720 ms > > Both pictures and picture categories have about 287,000 rows > > > > This query needs to run in under about a second or it kills my site by clogging apache slots (apache maxes out at 256 and I can have several hundred people on my site at a time). How can I make it run faster? > > > > > > Server is a dual xeon with a gig of ram dedicated mostly to postgresql. > > Here is the changed lines in my postgresql.conf: http://pastebin.ca/57222 > > I suspect the low work_mem may be why it's using a nested loop. In > addition to the test above, it would be interesting to see what happens > to the plan if you set work_mem to 10000. I moved to a more powerful server (2gb ram and mirrored scsi HDs) and upped the work mem to 10mb. Its much faster now, however its still doing a nested loop. (see also my reply to Markus Schaber) Nested Loop (cost=2.81..3398.76 rows=231 width=66) (actual time=14.946..5797.701 rows=1305 loops=1) -> Nested Loop (cost=2.81..2022.71 rows=231 width=47) (actual time=14.551..5181.042 rows=1305 loops=1) -> Bitmap Heap Scan on picture_categories (cost=2.81..633.78 rows=231 width=4) (actual time=9.966..140.606 rows=1305 loops=1) Recheck Cond: (cat_id = 182) -> Bitmap Index Scan on idx_picture_categories_cat_id (cost=0.00..2.81 rows=231 width=0) (actual time=9.720..9.720 rows=1305 loops=1) Index Cond: (cat_id = 182) -> Index Scan using pictures_pkey on pictures (cost=0.00..6.00 rows=1 width=47) (actual time=3.802..3.820 rows=1 loops=1305) Index Cond: (pictures.pid = "outer".pid) -> Index Scan using users_pkey on users (cost=0.00..5.94 rows=1 width=23) (actual time=0.095..0.100 rows=1 loops=1305) Index Cond: ("outer".user_id = users.user_id) Total runtime: 5812.238 ms > To be honest, you're pushing things expecting a machine with only 1G to > serve 300 active connections. How large is the database itself? The database is 3.7G on disk. There is about 1G of actual data in it -- the rest is dead tuples and indices. (I vacuum regularly, but a vacuum full causes too much downtime to do unless I have to) > > I know hyperthreading is considered something that can slow down a server but with my very high concurancy (averages about 400-500 concurant users during peak hours) I am hoping the extra virtual CPUs wil help. Anyone have experance that says diferent at high concurancy? > > Best bet is to try it and see. Generally, people find HT hurts, but I > recently saw it double the performance of pgbench on a windows XP > machine, so it's possible that windows is just more clever about how to > use it than linux is. Anyone know if those who have found it hurts are low concurancy complex cpu intensive queries or high concurancy simple queries or both? I can understand it hurting in the former, but not the later. I'll have to give it a try I guess. It should at least help my very high load averages. > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > From jnasby@pervasive.com Mon Jun 5 14:35:45 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 2FC239FA5D2 for ; Mon, 5 Jun 2006 11:35:45 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 81821-02 for ; Mon, 5 Jun 2006 11:35:34 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 577819FA111 for ; Mon, 5 Jun 2006 11:35:32 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 82B4F56440; Mon, 5 Jun 2006 09:35:30 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Mon, 5 Jun 2006 09:35:29 -0500 Date: Mon, 5 Jun 2006 09:35:29 -0500 From: "Jim C. Nasby" To: Cstdenis Cc: Nis Jorgensen , pgsql-performance@postgresql.org Subject: Re: How can I make this query faster (resend) Message-ID: <20060605143529.GP53487@pervasive.com> References: <03e001c67b99$8321f010$6401a8c0@chris> <447177B2.2020109@superlativ.dk> <013801c6832d$1cf73d90$6401a8c0@chris> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <013801c6832d$1cf73d90$6401a8c0@chris> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060605:cstdenis@voicio.com::lW0v3qK58Ezz50Pq:000000000000000000 0000000000000000000000003Ct/ X-Hashcash: 1:20:060605:nis@superlativ.dk::HUKuk11Ksyx0yA5E:00000000000000000000 0000000000000000000000002bHw X-Hashcash: 1:20:060605:pgsql-performance@postgresql.org::OAQF/448u/RJs8fN:00000 0000000000000000000000003fm7 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/34 X-Sequence-Number: 19391 On Mon, May 29, 2006 at 07:35:14AM -0700, Cstdenis wrote: > > To be honest, you're pushing things expecting a machine with only 1G to > > serve 300 active connections. How large is the database itself? > > The database is 3.7G on disk. There is about 1G of actual data in it -- the > rest is dead tuples and indices. (I vacuum regularly, but a vacuum full > causes too much downtime to do unless I have to) It sounds like you're not vacuuming anywhere near regularly enough if you have that much dead space. You should at least reindex. > > > I know hyperthreading is considered something that can slow down a > server but with my very high concurancy (averages about 400-500 concurant > users during peak hours) I am hoping the extra virtual CPUs wil help. Anyone > have experance that says diferent at high concurancy? > > > > Best bet is to try it and see. Generally, people find HT hurts, but I > > recently saw it double the performance of pgbench on a windows XP > > machine, so it's possible that windows is just more clever about how to > > use it than linux is. > > Anyone know if those who have found it hurts are low concurancy complex cpu > intensive queries or high concurancy simple queries or both? I can > understand it hurting in the former, but not the later. I'll have to give it > a try I guess. It should at least help my very high load averages. The issue is that HT doesn't give you anything close to having 2 CPUs, so for all but the most trivial and limited cases it's not going to be a win. Incidentally, the only good results I've seen with HT are on windows. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461