Re: Q on views and performance

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. That
will effectively group all the data for one type together and sort it by
zipk, making a merge join very quick indeed. I'm not sure whether Postgres
will notice that, but it's worth a try.

> More specifically, how can I go about building table T and the views
> V<int?>'s to maximize the performance of (Q1)?  For example, I'm thinking
> that if T had an additional id column and were built in such a way that all
> the records belonging to each V<int?> were physically contiguous, and (say)
> had contiguous values in the id column, then I could define each view like
> this

The above index and CLUSTER will effectively do this - you don't need to
introduce another field.

Alternatively, you could go *really evil* and pre-join the table.
Something like this:

CREATE TABLE evilJoin AS SELECT a1.type AS type1, a2.type AS type2,
     a1.zipk, a1.word AS word1, a2.word AS word2
   FROM T AS a1, T AS a2
   WHERE a1.zipk = a2.zipk
   ORDER BY a1.type, a2.type, a1.zipk;
CREATE INDEX evilIndex1 ON evilJoin(type1, type2, zipk);

Then your query becomes:

SELECT word1, word2
    FROM evilJoin
    WHERE type1 = <int1>
      AND type2 = <int2>

which should run quick. However, your cache usefulness will be reduced
because of the extra volume of data.

Matthew

--
[About NP-completeness] These are the problems that make efficient use of
the Fairy Godmother.                    -- Computer Science Lecturer