Skip to content

Instantly share code, notes, and snippets.

@waynehuang13

waynehuang13/gist:e7175423f638bc44edd3

Forked from newhouseb/gist:1620133
Last active August 29, 2015 14:24
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save waynehuang13/e7175423f638bc44edd3 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save waynehuang13/e7175423f638bc44edd3 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. Wayne Huang revised this gist Jul 2, 2015. No changes.
  2. @newhouseb newhouseb revised this gist Jan 16, 2012. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion gistfile1.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -7,7 +7,8 @@ basically free in PostgreSQL and O(n) in MySQL. For adding indexes its at best
    everywhere, but with PostgreSQL it claims not to do any locking that would otherwise
    prevent table interaction.

    Also, PostgreSQL has _awsome_ documentation (it has real examples!). I always get lost in circular links looking at MySQL document.
    Also, PostgreSQL has _awsome_ documentation (it has real examples!). I always get
    lost in circular links looking at MySQL documentation.

    EC2 m1.Small instance (instance-store) running Ubuntu

  3. @newhouseb newhouseb revised this gist Jan 16, 2012. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions gistfile1.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -7,6 +7,8 @@ basically free in PostgreSQL and O(n) in MySQL. For adding indexes its at best
    everywhere, but with PostgreSQL it claims not to do any locking that would otherwise
    prevent table interaction.

    Also, PostgreSQL has _awsome_ documentation (it has real examples!). I always get lost in circular links looking at MySQL document.

    EC2 m1.Small instance (instance-store) running Ubuntu

    # POSTGRES 8.4.9
  4. @newhouseb newhouseb revised this gist Jan 16, 2012. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions gistfile1.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -7,7 +7,7 @@ basically free in PostgreSQL and O(n) in MySQL. For adding indexes its at best
    everywhere, but with PostgreSQL it claims not to do any locking that would otherwise
    prevent table interaction.

    EC2 m1.Small instance (EBS backed) running Ubuntu
    EC2 m1.Small instance (instance-store) running Ubuntu

    # POSTGRES 8.4.9

    @@ -67,7 +67,7 @@ Time: 0.651 ms
    ALTER TABLE words DROP COLUMN pos;
    Time: 0.658 ms

    # Think this is just a hiccup on EBS latency possibly? occaisonally other random ALTERs spiked to about a second
    # Occasionally other random ALTERs spiked to about a second, not sure why
    ALTER TABLE words DROP COLUMN pos2;
    Time: 1011.007 ms

  5. @newhouseb newhouseb revised this gist Jan 16, 2012. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion gistfile1.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -7,7 +7,7 @@ basically free in PostgreSQL and O(n) in MySQL. For adding indexes its at best
    everywhere, but with PostgreSQL it claims not to do any locking that would otherwise
    prevent table interaction.

    EC2 m1.Large instance (EBS backed) running Ubuntu
    EC2 m1.Small instance (EBS backed) running Ubuntu

    # POSTGRES 8.4.9

  6. @newhouseb newhouseb revised this gist Jan 16, 2012. 1 changed file with 8 additions and 8 deletions.
    16 changes: 8 additions & 8 deletions gistfile1.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,11 +1,11 @@
    #The basic idea here is to substantiate the claims made by this square post:
    #http://corner.squareup.com/2011/06/postgresql-data-is-important.html

    #In PostgreSQL, and MySQL (MyISAM and InnoDB) I create millions of rows and then add
    #and remove columns and add and remove indexes. For columns without defaults this is
    #basically free in PostgreSQL and O(n) in MySQL. For adding indexes it's at best O(n)
    #everywhere, but with PostgreSQL it claims not to do any locking that would otherwise
    #prevent table interaction.
    The basic idea here is to substantiate the claims made by this square post:
    http://corner.squareup.com/2011/06/postgresql-data-is-important.html

    In PostgreSQL, and MySQL (MyISAM and InnoDB) I create millions of rows and then add
    and remove columns and add and remove indexes. For columns without defaults this is
    basically free in PostgreSQL and O(n) in MySQL. For adding indexes its at best O(n)
    everywhere, but with PostgreSQL it claims not to do any locking that would otherwise
    prevent table interaction.

    EC2 m1.Large instance (EBS backed) running Ubuntu

  7. @newhouseb newhouseb revised this gist Jan 16, 2012. 1 changed file with 8 additions and 8 deletions.
    16 changes: 8 additions & 8 deletions gistfile1.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,11 +1,11 @@
    The basic idea here is to substantiate the claims made by this square post:
    http://corner.squareup.com/2011/06/postgresql-data-is-important.html

    In PostgreSQL, and MySQL (MyISAM and InnoDB) I create millions of rows and then add
    and remove columns and add and remove indexes. For columns without defaults this is
    basically free in PostgreSQL and O(n) in MySQL. For adding indexes it's at best O(n)
    everywhere, but with PostgreSQL it claims not to do any locking that would otherwise
    prevent table interaction.
    #The basic idea here is to substantiate the claims made by this square post:
    #http://corner.squareup.com/2011/06/postgresql-data-is-important.html

    #In PostgreSQL, and MySQL (MyISAM and InnoDB) I create millions of rows and then add
    #and remove columns and add and remove indexes. For columns without defaults this is
    #basically free in PostgreSQL and O(n) in MySQL. For adding indexes it's at best O(n)
    #everywhere, but with PostgreSQL it claims not to do any locking that would otherwise
    #prevent table interaction.

    EC2 m1.Large instance (EBS backed) running Ubuntu

  8. @newhouseb newhouseb revised this gist Jan 16, 2012. 1 changed file with 4 additions and 1 deletion.
    5 changes: 4 additions & 1 deletion gistfile1.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -2,7 +2,10 @@ The basic idea here is to substantiate the claims made by this square post:
    http://corner.squareup.com/2011/06/postgresql-data-is-important.html

    In PostgreSQL, and MySQL (MyISAM and InnoDB) I create millions of rows and then add
    and remove columns and add and remove indexes. For columns without defaults this is basically free in PostgreSQL and O(n) in MySQL. For adding indexes it's at best O(n) everywhere, but with PostgreSQL it claims not to do any locking that would otherwise prevent table interaction.
    and remove columns and add and remove indexes. For columns without defaults this is
    basically free in PostgreSQL and O(n) in MySQL. For adding indexes it's at best O(n)
    everywhere, but with PostgreSQL it claims not to do any locking that would otherwise
    prevent table interaction.
    EC2 m1.Large instance (EBS backed) running Ubuntu
  9. @newhouseb newhouseb revised this gist Jan 16, 2012. 1 changed file with 2 additions and 5 deletions.
    7 changes: 2 additions & 5 deletions gistfile1.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,11 +1,8 @@
    The basic idea here is to substantiate the claims made by this square post:
    http://corner.squareup.com/2011/06/postgresql-data-is-important.html

    In PostgreSQL, and MySQL (MyISAM and InnoDB) I create millions of rows and then add and
    remove columns and add and remove indexes. For columns without defaults this is basically
    free in PostgreSQL and O(n) in MySQL. For adding indexes it's at best O(n) everywhere,
    but with PostgreSQL it claims not to do any locking that would otherwise prevent table
    interaction.
    In PostgreSQL, and MySQL (MyISAM and InnoDB) I create millions of rows and then add
    and remove columns and add and remove indexes. For columns without defaults this is basically free in PostgreSQL and O(n) in MySQL. For adding indexes it's at best O(n) everywhere, but with PostgreSQL it claims not to do any locking that would otherwise prevent table interaction.
    EC2 m1.Large instance (EBS backed) running Ubuntu
  10. @newhouseb newhouseb revised this gist Jan 16, 2012. 1 changed file with 8 additions and 3 deletions.
    11 changes: 8 additions & 3 deletions gistfile1.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,11 @@
    The basic idea here is to substantiate the claims made by this square post: http://corner.squareup.com/2011/06/postgresql-data-is-important.html

    In PostgreSQL, and MySQL (MyISAM and InnoDB) I create millions of rows and then add and remove columns and add and remove indexes. For columns without defaults this is basically free in PostgreSQL and O(n) in MySQL. For adding indexes it's at best O(n) everywhere, but with PostgreSQL it claims not to do any locking that would otherwise prevent table interaction.
    The basic idea here is to substantiate the claims made by this square post:
    http://corner.squareup.com/2011/06/postgresql-data-is-important.html

    In PostgreSQL, and MySQL (MyISAM and InnoDB) I create millions of rows and then add and
    remove columns and add and remove indexes. For columns without defaults this is basically
    free in PostgreSQL and O(n) in MySQL. For adding indexes it's at best O(n) everywhere,
    but with PostgreSQL it claims not to do any locking that would otherwise prevent table
    interaction.
    EC2 m1.Large instance (EBS backed) running Ubuntu
  11. @newhouseb newhouseb revised this gist Jan 16, 2012. 1 changed file with 5 additions and 1 deletion.
    6 changes: 5 additions & 1 deletion gistfile1.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,7 @@
    The basic idea here is to substantiate the claims made by this square post: http://corner.squareup.com/2011/06/postgresql-data-is-important.html

    In PostgreSQL, and MySQL (MyISAM and InnoDB) I create millions of rows and then add and remove columns and add and remove indexes. For columns without defaults this is basically free in PostgreSQL and O(n) in MySQL. For adding indexes it's at best O(n) everywhere, but with PostgreSQL it claims not to do any locking that would otherwise prevent table interaction.
    EC2 m1.Large instance (EBS backed) running Ubuntu
    # POSTGRES 8.4.9
    @@ -164,4 +168,4 @@ ALTER TABLE words ADD COLUMN pos2 varchar(30) NOT NULL DEFAULT 'noun';
    Time: 23996.939 ms
    select count(*) from words;
    1 row in set (8.85 sec)
    1 row in set (8.85 sec)
  12. @newhouseb newhouseb created this gist Jan 16, 2012.
    167 changes: 167 additions & 0 deletions gistfile1.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,167 @@
    EC2 m1.Large instance (EBS backed) running Ubuntu

    # POSTGRES 8.4.9

    create table words ( word varchar(40) );
    create language plpgsql;
    CREATE FUNCTION "million" () RETURNS text AS '
    DECLARE
    BEGIN
    FOR i IN 0..1000000 LOOP
    INSERT INTO words values (''the'');
    END LOOP; RETURN ''OK'';
    END;
    ' LANGUAGE 'plpgsql';

    CREATE FUNCTION "fivemillion" () RETURNS text AS '
    DECLARE
    BEGIN
    FOR i IN 0..5000000 LOOP
    INSERT INTO words values (''the'');
    END LOOP; RETURN ''OK'';
    END;
    ' LANGUAGE 'plpgsql';

    \timing

    SELECT million();
    Time: 21069.652 ms

    ALTER TABLE words ADD COLUMN pos varchar(30);
    Time: 1.867 ms

    ALTER TABLE words ADD COLUMN pos2 varchar(30) NOT NULL DEFAULT 'noun';
    Time: 4311.439 ms

    ALTER TABLE words DROP COLUMN pos;
    Time: 0.645 ms

    ALTER TABLE words DROP COLUMN pos2;
    Time: 1.249 ms

    CREATE INDEX CONCURRENTLY ON words (word);
    Time: 58110.661 ms

    CREATE INDEX derp ON words (word);
    Time: 56086.085 ms

    DROP INDEX derp;
    Time: 3.349 ms

    # After wiping the table
    SELECT fivemillion();
    Time: 104633.836 ms

    ALTER TABLE words ADD COLUMN pos varchar(31);
    Time: 0.651 ms

    ALTER TABLE words DROP COLUMN pos;
    Time: 0.658 ms

    # Think this is just a hiccup on EBS latency possibly? occaisonally other random ALTERs spiked to about a second
    ALTER TABLE words DROP COLUMN pos2;
    Time: 1011.007 ms

    select count(*) from words;
    Time: 2536.649 ms

    # MYSQL 5.1.41-3ubuntu12.3 (MyISAM)

    create table words ( word varchar(40) );
    delimiter $$
    CREATE PROCEDURE million()
    BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE (i<1000000) DO
    INSERT INTO words values ('the');
    SET i=i+1;
    END WHILE;
    END$$
    delimiter ;

    delimiter $$
    CREATE PROCEDURE fivemillion()
    BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE (i<5000000) DO
    INSERT INTO words values ('the');
    SET i=i+1;
    END WHILE;
    END$$
    delimiter ;

    call million();
    Query OK, 1 row affected (1 min 17.99 sec)

    ALTER TABLE words ADD COLUMN pos varchar(30);
    Query OK, 1000000 rows affected (1.72 sec)
    Records: 1000000 Duplicates: 0 Warnings: 0

    ALTER TABLE words ADD COLUMN pos2 varchar(30) NOT NULL DEFAULT 'noun';
    Query OK, 1000000 rows affected (2.04 sec)
    Records: 1000000 Duplicates: 0 Warnings: 0

    ALTER TABLE words DROP COLUMN pos;
    Query OK, 1000000 rows affected (1.70 sec)
    Records: 1000000 Duplicates: 0 Warnings: 0

    ALTER TABLE words DROP COLUMN pos2;
    Query OK, 1000000 rows affected (1.78 sec)
    Records: 1000000 Duplicates: 0 Warnings: 0

    CREATE INDEX derp ON words (word);
    Query OK, 1000000 rows affected (23.40 sec)
    Records: 1000000 Duplicates: 0 Warnings: 0

    DROP INDEX derp ON words;
    Query OK, 1000000 rows affected (1.15 sec)
    Records: 1000000 Duplicates: 0 Warnings: 0

    # MYSQL 5.1.41-3ubuntu12.3 (InnoDB)
    create table words ( word varchar(40) ) ENGINE=InnoDB;

    call million();
    Query OK, 1 row affected (2 min 35.03 sec)

    ALTER TABLE words ADD COLUMN pos varchar(30);
    Query OK, 1000000 rows affected (15.93 sec)
    Records: 1000000 Duplicates: 0 Warnings: 0

    ALTER TABLE words ADD COLUMN pos2 varchar(30) NOT NULL DEFAULT 'noun';
    Query OK, 1000000 rows affected (17.30 sec)
    Records: 1000000 Duplicates: 0 Warnings: 0

    ALTER TABLE words DROP COLUMN pos;
    Query OK, 1000000 rows affected (16.70 sec)
    Records: 1000000 Duplicates: 0 Warnings: 0

    ALTER TABLE words DROP COLUMN pos2;
    Query OK, 1000000 rows affected (15.84 sec)
    Records: 1000000 Duplicates: 0 Warnings: 0

    CREATE INDEX derp ON words (word);
    Query OK, 1000000 rows affected (23.28 sec)
    Records: 1000000 Duplicates: 0 Warnings: 0

    DROP INDEX derp ON words;
    Query OK, 1000000 rows affected (15.39 sec)
    Records: 1000000 Duplicates: 0 Warnings: 0

    # Dropping and recreating empty words table

    call fivemillion();
    Query OK, 1 row affected (12 min 57.41 sec)

    ALTER TABLE words ADD COLUMN pos varchar(30);
    Query OK, 5000000 rows affected (1 min 16.76 sec)
    Records: 5000000 Duplicates: 0 Warnings: 0

    ALTER TABLE words DROP COLUMN pos;
    Query OK, 5000000 rows affected (1 min 16.92 sec)
    Records: 5000000 Duplicates: 0 Warnings: 0

    ALTER TABLE words ADD COLUMN pos2 varchar(30) NOT NULL DEFAULT 'noun';
    Time: 23996.939 ms

    select count(*) from words;
    1 row in set (8.85 sec)