Skip site navigation (1) Skip section navigation (2)

How can I make this query faster (resend)

From: "Cstdenis" <cstdenis(at)voicio(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: How can I make this query faster (resend)
Date: 2006-05-19 22:56:49
Message-ID: 03e001c67b99$8321f010$6401a8c0@chris (view raw or flat)
Thread:
Lists: pgsql-performance
(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


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 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?

Responses

pgsql-performance by date

Next:From: Kenji MorishigeDate: 2006-05-20 02:37:45
Subject: utilizing multiple disks for i/o performance
Previous:From: Jim C. NasbyDate: 2006-05-19 20:59:15
Subject: Re: why is bitmap index chosen for this query?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group