Database System Indexing 2

ejkim·2023년 3월 21일
0

Sample Table Information

Q1.

Create two indexes

  • Create indexes on attribute “recordid” in “table_btree” and “table_hash”
    • Create b-tree in “table_btree.recordid”
    • Create hash index in “table_hash.recordid”
  • Type “\h CREATE INDEX” for detailed index creation syntax
    • Use a method name “btree” for creating b-tree and “hash” for creating hash index

A1.

Q2.

a. Run two queries and compare the query execution plan and total execution time

  • SELECT * FROM table_btree WHERE recordid=10001;
  • SELECT * FROM table_hash WHERE recordid=10001;

b. Run two queries and compare the query execution plan and total execution time

  • SELECT * FROM table_btree WHERE recordid>250 AND recordid<550;
  • SELECT * FROM table_hash WHERE recordid>250 AND recordid<550;

A2.

a.


두 쿼리 모두 index scan을 사용한다. 첫 쿼리의 총 수행 시간은 1.143ms, 두 번째 쿼리의 총 수행 시간은 2.275ms이다.

b.


첫 쿼리는 index scan을 사용하며, 두 번째 쿼리는 sequential scan을 사용한다. 첫 쿼리의 총 수행 시간은 0.871ms, 두 번째 쿼리의 총 수행 시간은 706.374ms이다.

Q3.

a. Update a single “recordid” field in “table_btree”. And update a single “recordid” field in “table_noindex”. Then find a difference

  • Update “recordid” from 9,999,997 to 9,999,998

b. Update 2,000,000 “recordid” fields in “table_btree”. And update 2,000,000 “recordid” fields in “table_noindex”. Then find a difference

  • Increase “recordid” fields by 100% whose value is greater than 8,000,000

c. Update all “recordid” fields in “table_btree”. And update all “recordid” fields in “table_noindex”. Then find a difference

  • Increase all “recordid” fields by 10%

A3.

a.


table_btree에서 single “recorded” field를 업데이트 할 때는 index scan을 사용하고, table_noindex에서 업데이트 할 때는 sequential scan을 사용한다.

b.


table_btree에서 update하는 경우와 table_noindex에서 업데이트 하는 경우 모두 sequential scan을 사용한다. (index가 있어도 index를 사용하는 것이 유리하지 않기 때문이다.)

c.



table_btree에서 update하는 경우와 table_noindex에서 업데이트 하는 경우 모두 sequential scan을 사용한다. (index가 있어도 index를 사용하는 것이 유리하지 않기 때문이다.)

Q4-Lab Setup.

Synthetic data – randomly distributed points

  • CREATE TABLE test0(id serial, x double precision, y double precision);
  • INSERT INTO test0(x, y)
    SELECT tmp.x, tmp.y
    FROM
    (SELECT (0.5-random())180 as x, random()360 as y
    FROM (SELECT generate_series(1, 1000000)) as t) as tmp;
  • CREATE INDEX test_idx_x on test0(x);
  • CREATE INDEX test_idx_y on test0(y);
  • SET enable_bitmapscan=false;

Synthetic data – randomly distributed points

  • CREATE TABLE test1(id serial, p point);
  • INSERT INTO test1(p)
    SELECT point(tmp.lat, tmp.long)
    FROM
    (SELECT (0.5-random())180 as lat, random()360 as long
    FROM (SELECT generate_series(1, 1000000)) as t) as tmp;
  • CREATE INDEX test_rtree_idx on test1 using gist(p);
  • SET enable_bitmapscan=false;

Synthetic data – randomly distributed points

  • CREATE TABLE test2(id serial, testbox box);
  • INSERT INTO test2(testbox)
    SELECT box(point(tmp.x1, tmp.y1), point(tmp.x2, tmp.y2))
    FROM
    (SELECT (0.5-random())180 as x1, random()360 as y1,
    ((0.5-random())180 + random()10) as x2, (random()36010) as y2
    FROM (SELECT generate_series(1, 1000000)) as t) as tmp;
  • CREATE INDEX test_box_idx on test2 using gist(testbox);
  • SET enable_bitmapscan=false;

Q4.

a. Find all points within a rectangle ((1,1), (10,10)) on the tables “test0” and “test1”

  • Compare an index scan and seq scan
    • SET enable_indexscan=true;
    • SET enable_indexscan=false;

b. Find all boxes overlapped with rectangles ((0,0), (1,1)) and ((9,9), (10,10)) at the same time on the table “test2”

  • Compare an index scan and seq scan
    • SET enable_indexscan=true;
    • SET enable_indexscan=false;

c. Find 10 nearest points to (0,0) on the tables “test0” and “test1”

  • Compare an index scan and seq scan
    • SET enable_indexscan=true;
    • SET enable_indexscan=false;

A4.

a.


"test0" indexscan=true


"test0" indexscan=false

profile
기록하자!

0개의 댓글