Picasso Database Query Optimizer Visualizer

©Indian Institute of Science, Bangalore, India

 

 

PostgreSQL

 

 

(The following setup procedure is known to work with PostgreSQL 8 on Windows.)

 

  1. Install PostgreSQL by running the .msi installer file. The process will include creating two accounts: a service account (preferably called postgres) and an internal database account.  The service account is an OS user account, which will be created during this installation if it doesn’t exist.  It should not be a privileged (root or admin) user. 
     

  2. Grant full permissions to this service account on the PostgreSQL installation folder.

  3. Login to the machine as the PostgreSQL service account.  The database is already initialized during installation.  You can optionally initialize a new instance (in a new data folder) by running this command in the command prompt:

  4. initdb –D <data_folder name with path>

    For example, if the data folder is ‘data1’ in the current directory, the command is:

    initdb –D data1

     

  5. Start the service with the command:

  6. postmaster –D <data_folder name with path>

    For example,

    postmaster –D data1

     

  7. Create a database that will contain the database tables.  The command is:

  8. createdb <dbname>

     

  9. Open the PostgreSQL prompt with the command:

    psql <dbname> -U <db_username>

     

    Here, the db_username should be the internal database account.  You will be prompted for the password, after which the SQL prompt opens. 

    Note: If the –U <db_username> option is not specified, this command assumes the current OS user as the db user, and prompts for the password.  Incidentally if the current OS user name is the same as the internal database account, you will be directly logged in.

  10.  

  11. Create the tables by entering appropriate SQL statements.  If the schema to be created is in a file, run the command: \i <filename> in the psql prompt.

  12.  

  13. For loading tables into the database, use the COPY utility of PostgreSQL. The command template is given below.  Run the command in the psql prompt.

  14. COPY <tablename> FROM '<filename>' WITH DELIMITER AS '<delimiter>';

     

    The filename should be given with the full absolute path. 

     

    For example, to load data file C:\nation.tbl which has pipe-separated data, into the NATION table, the command is:

    COPY NATION FROM 'C:\\nation.tbl ' WITH DELIMITER AS '|';

      

    Note:  Before loading large data files, it may be helpful w.r.t. response time to set the checkpoint_segments to a higher value than the default in the file postgresql.conf in the data directory.

     

  15. Create the integrity constraints (primary key, foreign key and indexes) now.  If the commands are in a file, run the command: \i <filename> in the psql prompt.

  16.  

  17. Create statistical summaries for each of the columns that may be used as Picasso predicates in the query templates using the following command in the psql prompt:

  18.  

    VACUUM ANALYZE;

     

    Note: The diagrams on the Picasso web-site have been generated with statistical summaries created on all table columns.

 

 

 

Documentation Home