query planner con t n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Query Planner (con’t) PowerPoint Presentation
Download Presentation
Query Planner (con’t)

Loading in 2 Seconds...

play fullscreen
1 / 11

Query Planner (con’t) - PowerPoint PPT Presentation


  • 167 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


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