Projet CSI 3530 DATABASES II - 2010

Informations

Code de nbtinsert.c fonctionne seulement sur postgresql-8.1.4

Notes on Postgresql configuration:

1. Make sure that you have configure your code :

./configure --prefix=$HOME/csi3130/pgbuild --enable-debug --enable-cassert --with-maxbackends=3

2. The commands I always use:

rm -r data

mkdir data

./initdb -D .../data

./postmaster -D .../data -p 5112

In a new window:

createdb –p 5112 dbtest1

psql -p 5112 dbtest1

Close Client: \q

Close server: ctrl+c

Client must be closed first!

3. How to use ELOG: Don’t Forget to execute <Make> and then <Make Install>

elog (NOTICE, "script name: %s", script_name);

elog (DEBUG1, "script name: %s", script_name);

4. Advanced debugging:

Use: gdb [executable-file [process-id]]

a. You need to know your postmaster pid:

ps -af | grep mgarz042 | grep postmaster

b. Go to pgdata/bin and execute

gdb ./postgres XXXXX

b nbtinsert.c:150

c. Other commands

bt (print call stack)

p query_string (print variable query_string)

s (execute step by step)

c (continue)

d,. How to Quit gdb

detach

quit

5. Index queries:

CREATE TABLE dict (id int4, word text);

CREATE INDEX dictix on dict(word);

COPY dict FROM '\words.txt' WITH DELIMITER AS ' ';

6. Test indexes:

explain select * from dict;

explain select * from dict where word between 'AAA' AND 'AAU';

/////OUTPUT EXPECTED/////

QUERY PLAN

------------------------------------------------------------------------

Bitmap Heap Scan on dict (cost=16.53..1172.43 rows=422 width=36)

Recheck Cond: ((word >= 'AAA'::text) AND (word <= 'AAU'::text))

-> Bitmap Index Scan on dictix (cost=0.00..16.53 rows=422 width=0)

Index Cond: ((word >= 'AAA'::text) AND (word <= 'AAU'::text))

(4 rows)

///// END OF OUTPUT /////

7. Other way to test:

drop table r; drop sequence r_seq;

create table r(a int, b int, c char, d char(10), e varchar(11));

create sequence r_seq;

insert into r values (nextval('r_seq'), 1, 'a', 'bbbbbbbb', 'cccccccccc');

insert into r select nextval('r_seq'), b, c, d, e from r;

select * from r limit 20;

select count(*) from r;

create index r_hash_ind on r using hash (a);

analyse r;

select * from r where a = 5000;

explain select * from r where a = 5000;

explain select * from r where a < 5000;

explain select * from r where a > 4900 and a < 5000;

create index r_bt_ind on r(a);

analyse r;

select * from r where a = 5000;

explain select * from r where a = 5000;

explain select * from r where a < 5000;

explain select * from r where a > 4900 and a < 5000;

explain select * from r where a <> 5000;