Query Planner (con’t)

1. CSI 3130 – 2009 – Lab 7 Query Planner (con’t) Ruiwen Chen http://www.site.uottawa.ca/~rchen052/csi3130/

2. Installation and Initialization Compile: cd postgresql-8.4.1 ./configure --enable-debug --enable-cassert --enable-depend --prefix=/home/rwchen/pginstall make install Initialization: cd ../pginstall/bin ./initdb –D../data Start the server: ./postgres –D../data –p 5678 Start a client: ./psql postgres –p 5678 Start the server with single backend: ./postgres --single –D../data postgres (use Ctrl+D to exit) Change to your own directory (use pwd to get current path) Change to a random port number to avoid conflicts

3. Querying Single Relation • Initialize a table • drop table cust; drop sequence seq_cust; • create table cust(cid int primary key, name char(40), bno int); • create sequence seq_cust; create index cust_bno on cust(bno); • insert into cust values(nextval('seq_cust'), 'aaaaaaaaaaaaaaaaaaaaaaa', (random()*50)::int); • insert into cust select nextval('seq_cust'), name, (random()*50)::int from cust; • insert into cust select nextval('seq_cust'), name, (random()*50)::int from cust; • insert into cust select nextval('seq_cust'), name, (random()*50)::int from cust; • insert into cust select nextval('seq_cust'), name, (random()*50)::int from cust; • insert into cust select nextval('seq_cust'), name, (random()*50)::int from cust; • insert into cust select nextval('seq_cust'), name, (random()*50)::int from cust; • insert into cust select nextval('seq_cust'), name, (random()*50)::int from cust; • insert into cust select nextval('seq_cust'), name, (random()*50)::int from cust; • insert into cust select nextval('seq_cust'), name, (random()*50)::int from cust;

4. Check stat info • \d cust • select * from pg_stat_user_tables; • analyse verbose cust; • select * from pg_stat_user_tables; • select * from pg_stat_user_indexes; • insert into cust select nextval('seq_cust'), name, (random()*50)::int from cust; • select * from pg_stat_user_tables; • update cust set bno = bno+5 where bno>20; • vacuum full analyse cust; • select * from pg_stat_user_tables; • select * from pg_class where relname='cust'; • select relname, relpages, reltuples from pg_class where relname='cust';

5. Explain Queries • explain select * from cust where cid = 102; • explain select * from cust where cid < 102; • explain select * from cust where cid > 102; • explain analyse select * from cust where cid = 102; • explain analyse select * from cust where cid < 102; • explain analyse select * from cust where cid > 102; • \timing • select * from cust where cid = 102; • select * from pg_stat_user_indexes; • select * from cust where cid = 102; • select * from pg_stat_user_indexes; • explain analyse select * from cust where cid < 102; • select * from pg_stat_user_indexes;

6. Change planner options • set enable_indexscan=off; • explain analyse select * from cust where cid = 102; • explain analyse select * from cust where cid < 102; • explain analyse select * from cust where cid > 102; • set enable_bitmapscan=off; • explain analyse select * from cust where cid = 102; • set enable_indexscan=on; • set enable_seqscan=off; • explain analyse select * from cust where cid = 102; • explain analyse select * from cust where cid < 102; • explain analyse select * from cust where cid > 102;

7. Enable the Debug Option in Planner • Shutdown the server • Edit • src/backend/optimizer/plan/planner.c • src/backend/optimizer/path/allpaths.c • Add “#define OPTIMIZER_DEBUG” • Make install • Restart the server, and try • explain analyse select * from cust where cid = 102;

8. Join Queries • Add another table • drop table branch; drop sequence seq_br; • create table branch(bno int primary key, name char(40), region int); • create sequence seq_br; • insert into branch values(nextval('seq_br'), 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb', (random()*10)::int); • insert into branch select nextval('seq_br'), name, (random()*10)::int from branch; • insert into branch select nextval('seq_br'), name, (random()*10)::int from branch; • insert into branch select nextval('seq_br'), name, (random()*10)::int from branch; • insert into branch select nextval('seq_br'), name, (random()*10)::int from branch; • insert into branch select nextval('seq_br'), name, (random()*10)::int from branch;

9. Join • explain analyse select * from cust, branch where cust.bno = branch.bno order by branch.region; • set enable_hashjoin = off; • explain analyse select * from cust, branch where cust.bno = branch.bno order by branch.region; • set enable_mergejoin = off; • explain analyse select * from cust, branch where cust.bno = branch.bno order by branch.region;

10. Join • set enable_indexscan = off; • explain analyse select * from cust, branch where cust.bno = branch.bno order by branch.region; • set enable_bitmapscan = off; • set enable_mergejoin = on; • explain analyse select * from cust, branch where cust.bno = branch.bno order by branch.region;

11. Modify Source Codes • src/backend/optimizer/plan/planmain.c • Line 85: query_planner() • Generate a plan • src/backend/optimizer/plan/allpaths.c • Line 86: make_one_relation() • Find all paths to generate a result relation • Line 166: set_rel_pathlist() • Line 214: set_plain_rel_pathlist() • Build paths for a base relation • /src/backend/optimizer/util/pathnode.c • Line 397: create_seqscan_path()