DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type;
	b=jdkYq3gHZS9biEl4MQlgmPFTdnm0aqdt8p5eYO07KwVp4ZuvegiSmumoHqOXv/JlpO8BkqIyVk3YKQOJRR0L8CdnFXUuOv8UMzGccaRFSv3WlSH84Aeddps+cmQDMrdP786uEOxXMLnqV24N64M+8MdOkK3oQPOu8KaoVdVMU8c=
	;
Message-ID: <20060510203817.62210.qmail@web50301.mail.yahoo.com>
Date: Wed, 10 May 2006 13:38:17 -0700 (PDT)
From: <ogjunk-pgjedan@yahoo.com>
Subject: Re: Help with a seq scan on multi-million row table
To: pgsql-sql@postgresql.org
In-Reply-To: <20060510192329.GB4099@wolff.to>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Hi,

Thanks Bruno.  That was indeed a redundant DISTINCT.  It did reduce the amount of work, but as you said it doesn't get rid of the sequential scan, which is the real problem with this query.

Otis

----- Original Message ----
From: Bruno Wolff III <bruno@wolff.to>
To: ogjunk-pgjedan@yahoo.com
Cc: pgsql-sql@postgresql.org
Sent: Wednesday, May 10, 2006 3:23:29 PM
Subject: Re: [SQL] Help with a seq scan on multi-million row table

On Wed, May 10, 2006 at 13:13:59 -0500,
  ogjunk-pgjedan@yahoo.com wrote:
>   Hello,
> 
> I have a little 2-table JOIN, GROUP BY, ORDER BY query that does a sequential scan on a multi-million row table.  I _thought_ I had all the appropriate indices, but apparently I do not.  I was wondering if anyone can spot a way I can speed up this query.
> The query currently takes... *gulp*: 381119.201 ms :(
>   
> There are only 2 tables in the game: user_url and user_url_tag.  The latter has FKs pointing to the former.  The sequential scan happens on the latter - user_url_tag:
> 
> EXPLAIN ANALYZE select DISTINCT userurltag0_.tag as x0_0_, COUNT(*) as x1_0_ from user_url_tag userurltag0_, user_url userurl1_ WHERE (((userurl1_.user_id=1 )) AND ((userurltag0_.user_url_id=userurl1_.id ))) GROUP BY userurltag0_.tag ORDER BY  count(*) DESC;  

While this isn't a big issue, it looks like DISTINCT is redundant in your
query and seems to be adding some extra work.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq