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 2271F9FAA6E
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 19 May 2006 20:11: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 64446-10
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 19 May 2006 20:11:18 -0300 (ADT)
X-Greylist: delayed 01:23:57.264381 by SQLgrey-
Received: from swordfish.dnsvelocity.com (swordfish.dnsvelocity.com
	[216.67.224.90])
	by postgresql.org (Postfix) with ESMTP id 8F2B29FA5FB
	for <pgsql-performance@postgresql.org>;
	Fri, 19 May 2006 20:11:18 -0300 (ADT)
Received: from [24.87.22.207] (port=4215 helo=chris)
	by swordfish.dnsvelocity.com with esmtpa (Exim 4.52)
	id 1FhE7o-0006sg-Ap
	for pgsql-performance@postgresql.org; Sat, 20 May 2006 00:11:16 +0100
Message-ID: <03e001c67b99$8321f010$6401a8c0@chris>
From: "Cstdenis" <cstdenis@voicio.com>
To: <pgsql-performance@postgresql.org>
Subject: How can I make this query faster (resend)
Date: Fri, 19 May 2006 15:56:49 -0700
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_03DD_01C67B5C.D6711260"
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/407
X-Sequence-Number: 19194

This is a multi-part message in MIME format.

------=_NextPart_000_03DD_01C67B5C.D6711260
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

(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=20
=20
CREATE INDEX idx_rating ON pictures USING btree  (rating_nudity, =
rating_violence, rating_sex, rating_racism, rating_spoilers, =
rating_yaoi, rating_yuri, rating_profanity);
=20
and approved and date_submitted and user_id also have their own btree =
indexes.
=20
In the picture_categories table pid and cat_id have their own btree =
indices plus one together.=20

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


Both pictures and picture categories have about 287,000 rows
=20
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?
=20
=20
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 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?
------=_NextPart_000_03DD_01C67B5C.D6711260
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1505" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>(Its been a hour and I dont see my =
message on the=20
list so I'm sending it again. I've moved the queries and analyze out of =
the=20
email incase it was rejected because too long)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>query: <A=20
href=3D"http://pastebin.ca/57218">http://pastebin.ca/57218</A></FONT></DI=
V>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV><FONT face=3DArial =
size=3D2>
<DIV>In the pictures table all the ratings have a shared index=20
<BR>&nbsp;<BR>CREATE INDEX idx_rating ON pictures USING btree&nbsp;=20
(rating_nudity, rating_violence, rating_sex, rating_racism, =
rating_spoilers,=20
rating_yaoi, rating_yuri, rating_profanity);<BR>&nbsp;<BR>and approved =
and=20
date_submitted and user_id also have their own btree =
indexes.<BR>&nbsp;<BR>In=20
the picture_categories table pid and cat_id have their own btree indices =
plus=20
one together. </DIV>
<DIV>&nbsp;</DIV>
<DIV>Full table definition: <A=20
href=3D"http://pastebin.ca/57219">http://pastebin.ca/57219</A></DIV>
<DIV>&nbsp;</DIV>
<DIV>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: <A=20
href=3D"http://pastebin.ca/57220">http://pastebin.ca/57220</A></DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>Both pictures and picture categories have about 287,000=20
rows<BR>&nbsp;<BR>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?<BR>&nbsp;<BR>&nbsp;<BR>Server is a dual xeon with a gig of ram =
dedicated=20
mostly to postgresql.<BR>Here is the changed lines in my =
postgresql.conf: <A=20
href=3D"http://pastebin.ca/57222">http://pastebin.ca/57222</A></DIV>
<DIV>&nbsp;</DIV>
<DIV>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?</DIV></FONT></BODY></HTML>

------=_NextPart_000_03DD_01C67B5C.D6711260--