PostgreSQL: denormalization example

Trabla: PostgreSQL: denormalization example


Example Table Structure

Goal: select all records from Table 3 which depends to one record in Table 1

Query Execution Statistics:

Num - param of generator script, means
 - create N rows in Table 1,
- foreach row in Table 1 create N rows in Table 2,
- foreach row in Table 2 create N rows in Table 3.

Num	Execution time INNER JOIN ( t1->t2->t3), ms	Execution time INDEX search ( t3 ), ms
10	1.016	0.187
20	7.815	0.465
30	12.441	1.099
40	30.461	1.582
50	46.039	2.103
100	275.438	4.836

Data Generator Script:

CREATE OR REPLACE FUNCTION pg_performance_test ( num int)

  RETURNS character varying AS

$BODY$

  

DECLARE

    -- Variables
    article_index bigint;
    word_index bigint;
    symbol_index bigint;

    article__id bigint := 1;
    word__id bigint := 1;
    symbol__id bigint := 1;

BEGIN

   
    DROP TABLE IF EXISTS example_symbol;
    DROP TABLE IF EXISTS example_word;
    DROP TABLE IF EXISTS example_article;
   


    -- ================================================
    --     Table definition
    -- ================================================
    CREATE TABLE example_article(
        id bigint PRIMARY KEY,
        article text
    );

   
    CREATE TABLE example_word(
        id bigint PRIMARY KEY,
        article_id bigint REFERENCES example_article(id),
        word text
    );

   
    CREATE TABLE example_symbol(
        id bigint PRIMARY KEY,
        word_id bigint REFERENCES example_word(id),
        symbol text
    );

    -- ================================================
    --     Table optimization
    -- ================================================


    ALTER TABLE example_symbol ADD COLUMN article_id bigint;

    CREATE INDEX example_article_idx ON example_symbol (article_id);

   
    -- ================================================
    --     Fill tables with data
    -- ================================================
    FOR article_index IN 1..num LOOP

        article__id := article__id + article_index;

        INSERT INTO example_article( id, article ) VALUES ( article__id , 'Bla Bla Bla' );


        FOR word_index IN 1..num LOOP

            word__id := word__id + word_index;

            INSERT INTO example_word( id, word , article_id) VALUES ( word__id , 'Bla' , article__id);


                FOR symbol_index IN 1..num LOOP

                    symbol__id := symbol__id + symbol_index;


                    INSERT INTO example_symbol( id, word_id,  symbol, article_id  ) VALUES ( symbol__id, word__id, 'B', article__id );

                END LOOP;

        END LOOP;
   
      
    END LOOP;
   

  
    RETURN  'DONE';

  

END;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

-- Set function owner

ALTER FUNCTION pg_performance_test ( int )

  OWNER TO postgres;