From: Bruno Wolff III <bruno@wolff.to>
To: ogjunk-pgjedan@yahoo.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Help with a seq scan on multi-million row table
Date: Wed, 10 May 2006 14:23:29 -0500
Message-ID: <20060510192329.GB4099@wolff.to> (raw)
In-Reply-To: <20060510181359.58201.qmail@web50306.mail.yahoo.com>
References: <20060510181359.58201.qmail@web50306.mail.yahoo.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, ogjunk-pgjedan@yahoo.com
Subject: Re: Help with a seq scan on multi-million row table
In-Reply-To: <20060510192329.GB4099@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox