Re: 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
speedup 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
sequentialscan 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
ORDERBY  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.