Postgres Pro
Downloads
Home   >   mailing lists

Re: Q on views and performance - Mailing list pgsql-performance

From Kynn Jones
Subject Re: Q on views and performance
Date
Msg-id c2350ba40802250850m2ae1867agbbdc942c3c482d5b@mail.gmail.com
Whole thread Raw
In response to Re: Q on views and performance  (Matthew <matthew@flymine.org>)
Responses Re: Q on views and performance
List pgsql-performance
Tree view
On Mon, Feb 25, 2008 at 8:45 AM, Matthew <matthew@flymine.org> wrote:
On Fri, 22 Feb 2008, Kynn Jones wrote:
> Hi.  I'm trying to optimize...
>
> (Q1)   SELECT a1.word, a2.word
>         FROM T a1 JOIN T a2 USING ( zipk )
>        WHERE a1.type = <int1>
>          AND a2.type = <int2>;

Okay, try this:

Create an index on T(type, zipk), and then CLUSTER on that index...

This is just GREAT!!!  It fits the problem to a tee.

Many, many thanks!

Also, including zipk in the index is a really nice extra boost.  (If you hadn't mentioned it I would have just settled for clustering only on type...)

Thanks for that also!

Kynn

pgsql-performance by date:

Previous
From: Matthew
Date:
Subject: Re: Weird issue with planner choosing seq scan
Next
From: Matthew
Date:
Subject: Re: Q on views and performance