Exercises for Chapter 10

In these exercises, we use again the database dbtuning and the survey table we have created in the chapter, but without the indexes for the city and year variables. You can regenerate this table with

library(RPostgres)

db <- dbConnect(Postgres(),
  dbname = "dbtuning",
  user = "postgres",
  password = "pgpasswd"
)
dbExecute(db, "DROP TABLE IF EXISTS survey")
dbExecute(db, "CREATE TABLE survey AS
          (SELECT * FROM
            generate_series(1,100) AS person,
            generate_series(1,1000) AS city,
            generate_series(1970, 2020) AS year,
            random() AS result)")

We also create a cities table with additional (random) data about the 1000 cities in the survey:

dbExecute(db, "DROP TABLE IF EXISTS cities")
dbExecute(db, "CREATE TABLE cities AS
          (SELECT * FROM
            generate_series(1,1000) AS id,
            random() AS city_var)")

Exercise 1: Automatically Creating Identifiers

As we have seen, it is highly recommended to have unique identifiers for the records in a table. These identifiers are often integer numbers. In this exercise, we will show how PostgreSQL helps you create and maintain these identifiers. This is done by adding a field of type serial to a table. A serial is an integer field, which is automatically populated by the database with unique values. Let’s see how this works. Add a new column id to the survey table that is of type serial. Next, check the values of the column by computing is minimum and maximum. Now, add a new record to the table (you can choose arbitrary values). When you do so, simply omit the value for the new id field! Finally, retrieve the newly inserted record and check what id value it has.

Solution

We add a new field of type serial:

dbExecute(db, "ALTER TABLE survey ADD COLUMN id serial")

The id column contains integer values assigned in ascending order, starting from 1 up to 5.1 million (which is the number of records in the table):

dbGetQuery(db, "SELECT min(id), max(id) FROM survey")

##   min     max
## 1   1 5100000

This behavior is different to other types of variables; for example, when you add an integer variable to your table, by default its values are NULL. Now, when we add a new record to the table, we can simply omit the value for the new id field, and only provide values for the other columns in the table

dbExecute(db, "INSERT INTO survey VALUES (2, 3, 2021, 0.111)")

## [1] 1

Let’s check what id value the new record gets:

dbGetQuery(db, "SELECT * FROM survey WHERE person=2 AND city=3 AND year=2021")

##   person city year result      id
## 1      2    3 2021  0.111 5100001

This shows you that the serial column automatically assigns new values, such that the identifier remains unique. However, you can still mess up this column - the following statement recodes a row such that it gets an id value that is already present in the table:

dbExecute(db, "UPDATE survey SET id = 50 WHERE id = 49")

Therefore, when you use a serial field to uniquely identify rows in your table, it’s a good idea to also declare it as primary key, in which case the database system ensures that its values are unique.

Exercise 2: Indexed and Non-indexed Joins between Tables

In this exercise, we explore how indexes affect joins between tables. As described above, we have a cities table that contains additional data about the cities that our survey respondents live in. We would like to join the survey data with the city-level data based on the city field in the survey table, which corresponds to the id field in the cities table. For each of the following joins, write a simple SELECT statement that counts the number of rows in the result (SELECT count(*) FROM ... JOIN ...) and measure the execution time.

Join the survey table with cities
Create a primary key on the id field in cities, and execute the join again. What do you observe?
Configure the database such that city in the survey table is now a foreign key that points to id in cities, and execute the join.

How does the execution time change during steps 1-3? Do you have an explanation for this?

Solution

Step 1: We first carry out the join without any keys and indexes:

{
  start_time <- Sys.time()
  dbGetQuery(db, "SELECT count(*) FROM survey JOIN cities ON survey.city = cities.id")
  Sys.time() - start_time
}

## Time difference of 0.3067179 secs

This takes a fairly long time, which is not surprising since we do not have any indexes on our two tables. In Step 2, we add a primary key on cities with

dbExecute(db, "ALTER TABLE cities ADD PRIMARY KEY (id)")

and measure the execution time again:

{
  start_time <- Sys.time()
  dbGetQuery(db, "SELECT count(*) FROM survey JOIN cities ON survey.city = cities.id")
  Sys.time() - start_time
}

## Time difference of 0.1342728 secs

Now, we see an improvement compared to the first try. Step 3: If we now add a foreign key to survey with

dbExecute(db, "ALTER TABLE survey ADD FOREIGN KEY (city) REFERENCES cities (id)")

this execution time changes very little:

{
  start_time <- Sys.time()
  dbGetQuery(db, "SELECT count(*) FROM survey JOIN cities ON survey.city = cities.id")
  Sys.time() - start_time
}

## Time difference of 0.1347501 secs

So what did we observe? Defining a primary key for cities in step 2 led to a considerable speedup, since PostgreSQL always indexes primary keys. No additional index is created for the referencing field (city) when we set a foreign key in step 3, which is why the execution time almost does not change. So as a rule of thumb, it is useful to properly index join attributes, unless they are already defined as primary keys.

Exercise 3: Indexing Text Fields

In this exercise, we explore if PostgreSQL’s default indexes also help up speed up searching text fields. For this, add a new column respondent_name of type VARCHAR column to the survey table. We fill this column with random strings. One way to do this is to use an UPDATE statement and assign the random strings as follows: respondent_name = md5(random()::text). Then, for each of the following operations, write a simple SELECT statement that counts the number of rows in the result (SELECT count(*) FROM ... WHERE ...) and measure the execution time:

Respondent names that contain the string abc
Respondent names that match the string abc exactly

For this, recall that the LIKE operator may be useful.

Next, create an index on respondent_name, repeat the two operations, and measure again the execution time. What do you observe? Where is the index most helpful?

Solution

We first create a new column respondent_name with random strings:

dbExecute(db, "ALTER TABLE survey ADD COLUMN respondent_name VARCHAR")
dbExecute(db, "UPDATE survey SET respondent_name = md5(random()::text)")

Next, we measure the execution time for a lookup with fuzzy matching (whether abc is contained somewhere in the string) with

{
  start_time <- Sys.time()
  dbGetQuery(db, "SELECT count(*) FROM survey
                WHERE respondent_name LIKE '%abc%'")
  Sys.time() - start_time
}

## Time difference of 4.471904 secs

and again for exact matching (whether the string matches abc exactly):

{
  start_time <- Sys.time()
  dbGetQuery(db, "SELECT count(*) FROM survey
                WHERE respondent_name LIKE 'abc'")
  Sys.time() - start_time
}

## Time difference of 0.142576 secs

As the longer execution time shows, exact matching is much simpler, since the database system does not have to go through the entire string – if it does not encounter an a as the first character, it can immediately move on.

Next, we create a default index on respondent_name

dbExecute(db, "CREATE INDEX ON survey (respondent_name)")

and execute the two lookup operations again:

{
  start_time <- Sys.time()
  dbGetQuery(db, "SELECT count(*) FROM survey
                WHERE respondent_name LIKE '%abc%'")
  Sys.time() - start_time
}

## Time difference of 0.2446098 secs

{
  start_time <- Sys.time()
  dbGetQuery(db, "SELECT count(*) FROM survey
                WHERE respondent_name LIKE 'abc'")
  Sys.time() - start_time
}

## Time difference of 0.0008790493 secs

While the index improves both operations, you can clearly see that the speedup is far more pronounced for exact string matching. This is because PostgreSQL’s default index functionality uses the entire content of a column, not just parts of it. In Chapter 13 of the book, we will explore text indices that are able to quickly search for patterns occurring somewhere in the text.

Exercise 4: Database Privileges at the Column Level

In this exercise, we will deal again with database privileges that we have covered in the chapter. Recall that we have set up an additional user other, and we gave this user the privilege to SELECT and to UPDATE the surveys table. At the end of the chapter, we revoked all these privileges, so the user can no longer access the table in any way:

dbExecute(db, "REVOKE ALL PRIVILEGES ON survey FROM other")

Let us now assume that user other serves as a research assistant in a project, and you would like this person to update the survey data. However, you would like to limit the changes this user can perform to a single column, such that the existing table remains unchanged. Can you use the PostgreSQL online documentation to find out how to do this? Hint: Since UPDATE requires the user to also reference the column names, you need to also grant SELECT privileges. As in the chapter, make sure to open again a second connection db1, which connects as user other.

Solution

We first open a second connection to the database, connecting as user other:

db1 <- dbConnect(Postgres(),
  dbname = "dbtuning",
  user = "other",
  password = "pgpasswd1"
)

Since we have revoked all privileges, updating does not work:

dbExecute(db1, "UPDATE survey SET result=0.01 WHERE year=2000")

## Error:
## ! Failed to fetch row : ERROR:  permission denied for table survey

Now, we can grant UPDATE on a single column, by referencing the column name explicitly:

dbExecute(db, "GRANT SELECT ON survey TO other")
dbExecute(db, "GRANT UPDATE(result) ON survey TO other")

Now, updating does work on the result column

dbExecute(db1, "UPDATE survey SET result=0.01 WHERE year=2000")

## [1] 100000

but not on any other column:

dbExecute(db1, " UPDATE survey SET year=2001 WHERE year=2000")

## Error:
## ! Failed to fetch row : ERROR:  permission denied for table survey