1 / 11

Query Planner (con’t)

CSI 3130 – 2009 – Lab 7. Query Planner (con’t). Ruiwen Chen http://www.site.uottawa.ca/~rchen052/csi3130/. Installation and Initialization. Compile: cd postgresql-8.4.1 ./configure --enable-debug --enable-cassert --enable-depend --prefix =/home/rwchen/pginstall make install

Download Presentation

Query Planner (con’t)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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()

More Related