Thread: Q on views and performance

On 2008-02-22 12:49, Kynn Jones wrote:
> Of course, I expect that using views V<int1> and V<int2>... would
> result in a loss in performance relative to a version that used bona
> fide tables T<int1> and T<int2>.  My question is, how can I minimize
> this performance loss?

That used to be my thoughts too, but I have found over the years that
the PostgreSQL execution planner is able to "flatten" SELECTs using
VIEWs, ALMOST ALWAYS in a way that does not adversely affect
performance, and often gives an IMPROVEMENT in performance, probably
because by using VIEWs I am stating the query problem in a better way
than if I try to guess the best way to optimize a SELECT.

I have at least a 10:1 ratio of VIEWs to TABLEs.  Occasionally, with
some query that is slow, I will try to rewrite it without VIEWs.  This
ALMOST NEVER results in an improvement in performance, and when it does,
I am able to find another way to write the VIEW and SELECT to recapture
the gain.

-- Dean

--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.

Hi Kynn,

Lets take these up as cases :

Case A: keep one large table T and keep V1 .... V100
Case B: keep one large table T and store the the same data also in T1...T100
Case C: keep T1...T100 and store one V which is a UNION of T1 ... T100

1. The way I look at it, in case B although fetching data instead of evaluating VIEWs would help (when compared to case A), you are missing a small negative fact that your caching mechanism would be severely hit by having to cache two copies of the same data once in T1..T100 and the second time in T.

2. Case C seems to me like a particularly bad idea... and the indexing point that you make, seems all the more complicated... I don't know much about it, so I would try to avoid it.

3. Also, it seems you got the Postgresql VIEW mechanism wrong here. What Dean was trying to say was that PG flattens the VIEW (and its JOINS) directly into a *single* SELECT query *before* it hits even the first record. The per-record redirection is not how it approaches VIEWs which is pretty much why Dean's experience says that relying on the Parser to generate a better SQL (compared to our expertise at optimising it) is not really a bad idea.

4. Personally, Case A is a far far simpler approach to understability (as well as data storage) and if you ask my take ? I'll take Case A :)

Robins Tharakan

On 2008-02-23 05:59, Kynn Jones wrote:

On Fri, Feb 22, 2008 at 8:48 PM, Dean Gibson (DB Administrator) <postgresql@ultimeth.com> wrote:

...

Since you have experience working with views, let me ask you this.  The converse strategy to the one I described originally would be to create the individual tables T1, T2, T3, ..., T100, but instead of keeping around the original (and now redundant) table T, replace it with a view V made up of the union of T1, T2, T3, ..., T100.  The problem with this alternative is that one cannot index V, or define a primary key constraint for it, because it's a view.  This means that a search in V, even for a primary key value, would be *have to be* very inefficient (i.e. I don't see how even the very clever PostgreSQL implementers could get around this one!), because the engine would have to search *all* the underlying tables, T1 through T100, even if it found the desired record in T1, since it has no way of knowing that the value is unique all across V.

Is there a way around this?

kynn

Oh, I wouldn't create separate tables and do a UNION of them, I'd think that would be inefficient.

I didn't look in detail at your previous eMail, but I will now:

1. You haven't told us the distribution of "zipk", or what the tables are indexed on, or what type of performance you are expecting.  Your initial examples don't help much unless you actually have performance numbers or EXPLAIN output for them, since adding the third JOIN significantly changes the picture, as does changing one of the JOINs to a LEFT JOIN.

2. In your actual (Q1** and Q2**) examples, why is one JOIN an INNER JOIN and the other one a LEFT JOIN?  Given your description of Q1 at the top of your message, that doesn't make sense to me.

3. Why not write:

CREATE VIEW txt AS
  SELECT a1.word AS word1, a1.type AS type1, a2.word AS word2, a2.type AS type2
    FROM T a1 [LEFT] JOIN T a2 USING( zipk );  -- Use "LEFT" if appropriate
SELECT word1, word1
  FROM S JOIN txt ON word = word1
  WHERE type1 = <int1> AND type2 = <int2>;

If either of those (either with or without the "LEFT") are not equivalent to your problem, how about just:

SELECT a1.word AS word1, a2.word AS word2
  FROM S JOIN T a1 USING( word)
    [LEFT] JOIN T a2 USING( zipk )  -- Use "LEFT" if appropriate
  WHERE a1.type = <int1> AND a2.type = <int2>;

Show us (using EXPLAIN) what the query planner thinks of each of these.

-- 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.

On 2008-02-23 07:08, Dean Gibson (DB Administrator) wrote:

...


SELECT word1, word1
  FROM S JOIN txt ON word = word1
  WHERE type1 = <int1> AND type2 = <int2>;

...
Oops that should be:

SELECT word1, word2
  FROM S JOIN txt ON word = word1
  WHERE type1 = <int1> AND type2 = <int2>;

-- 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.

Hi, Dean.  The system I'm working with is very similar "in spirit" to a large multilingual dictionary covering 100 languages.  Using this analogy, the "type" column would correspond to the language, and the zipk column would correspond to some language-independent key associated with a concept ("concept key" for short).  So, if it were indeed a multilingual dictionary, records in T would look like

On 2008-02-23 08:21, Kynn Jones wrote:

...

3. Why not write:

CREATE VIEW txt AS
  SELECT a1.word AS word1, a1.type AS type1, a2.word AS word2, a2.type AS type2
    FROM T a1 [LEFT] JOIN T a2 USING( zipk );  -- Use "LEFT" if appropriate
SELECT word1, word1
  FROM S JOIN txt ON word = word1
  WHERE type1 = <int1> AND type2 = <int2>;

This is would indeed produce the same results as Q1, but this approach would require defining about 10,000 views, one for each possible pair of int1 and int2

Why 10,000 views???  What's wrong with the ONE view above?  You DON'T want to be defining VIEWs based on actual tables VALUES;  leave that to the SELECT.  For that matter, what's wrong with the final SELECT I listed (below)?

SELECT a1.word AS word1, a2.word AS word2
  FROM S JOIN T a1 USING( word )
    LEFT JOIN T a2 USING( zipk )
  WHERE a1.type = <int1> AND a2.type = <int2>;

-- Dean

-- 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.

On 2008-02-23 08:49, Dean Gibson (DB Administrator) wrote:

Why 10,000 views???  What's wrong with the ONE view above?  You DON'T want to be defining VIEWs based on actual tables VALUES;  leave that to the SELECT.  For that matter, what's wrong with the final SELECT I listed (below)?

SELECT a1.word AS word1, a2.word AS word2
  FROM S JOIN T a1 USING( word )
    LEFT JOIN T a2 USING( zipk )
  WHERE a1.type = <int1> AND a2.type = <int2>;

-- Dean

Amendment:  I forgot, that if it's a LEFT JOIN you have to write it as:

SELECT a1.word AS word1, a2.word AS word2
  FROM S JOIN T a1 USING( word )
    LEFT JOIN T a2 USING( zipk )
  WHERE a1.type = <int1> AND (a2.type = <int2> OR a2.type IS NULL);

-- Dean

-- 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.

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

So, this email is directed much more towards Postgres Powers That Be. I
came across this problem a while ago, and I haven't checked whether it has
been improved.

On Mon, 25 Feb 2008, I 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>;
>
> 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.

Statistics are generated on fields in a table, and the one I'm interested
in is the correlation coefficient which tells Postgres how costly an index
scan sorted on that field would be. This entry is ONLY useful when the
result needs to be sorted by that exact field only. For example:

CREATE TABLE test (a int, b int);
// insert a bazillion entries
CREATE INDEX testIndex ON test(a, b);
CLUSTER test ON testIndex;
ANALYSE;

So now we have a table sorted by (a, b), but the statistics only record
the fact that it is sorted by a, and completely unsorted by b. If we run:

SELECT * FROM test ORDER BY a;

then the query will run quickly, doing an index scan. However, if we run:

SELECT * FROM test ORDER BY a, b;

then Postgres will not be able to use the index, because it cannot tell
how sequential the fetches from the index will be. Especially if we run:

SELECT * FROM test WHERE a = <something> ORDER BY b;

then this is the case.

So, these observations were made a long time ago, and I don't know if they
have been improved. A while back I suggested a "partial sort" algorithm
that could take a stream sorted by a and turn it into a stream sorted by
(a, b) at small cost. That would fix some instances of the problem.
However, now I suggest that the statistics are in the wrong place.

At the moment, the correlation coefficient, which is an entry purely
designed to indicate how good an index is at index scans, is a statistic
on the first field of the index. Why not create a correlation coefficient
statistic for the index as a whole instead, and store it elsewhere in the
statistics data? That way, instead of having to infer from the first field
how correlated an index is, and getting it wrong beyond the first field,
you can just look up the correlation for the index.

Opinions?

Matthew

--
If you let your happiness depend upon how somebody else feels about you,
now you have to control how somebody else feels about you. -- Abraham Hicks

On Mon, 25 Feb 2008, Kynn Jones wrote:
> This is just GREAT!!!  It fits the problem to a tee.

It makes the queries quick then?

Matthew

--
The only secure computer is one that's unplugged, locked in a safe,
and buried 20 feet under the ground in a secret location...and i'm not
even too sure about that one.                         --Dennis Huges, FBI