Picasso
Database Query Optimizer Visualizer
©Indian
Institute of Science,
PostgreSQL
(The
following setup procedure is known to work with PostgreSQL
8 on Windows.)
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.
Grant
full permissions to this
service account on the PostgreSQL installation
folder.
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:
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
Start
the service with the command:
postmaster –D
<data_folder name with path>
For example,
postmaster
–D data1
Create
a database that will contain the database tables. The command is:
createdb
<dbname>
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.
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.
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.
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.
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.
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:
VACUUM ANALYZE;
Note: The diagrams on the Picasso web-site have been
generated with statistical summaries created on all table columns.