2. Query Execution

Deliverable

You have to produce by pair a practice report detailing and discussing all the experiments you will conduct during this lesson. At the end of the session, upload your practice report on Moodle.

Preparing the Docker environment

You should have already acquired the basics of Docker container management and Postgresql administration.

A Docker compose environment is provided HERE that serves two containers:

• a Postgresql server
• a Pgadmin client. After having extracted the content of the Docker environment, follow the instruction from the Readme file to run the servers.

Querying the testidx_db

The testidx_db contains three tables, users, posts and cities in the public namespace.

Refresh the statistics

Open the Query Tool panel to execute the ANALYZE users; whose aim is to update the DB statistics concerning the users table. You can do the same for the two other tables.

Discover the data distribution

Write three queries addressing the pg_stats table only, to retrieve the following information for each of the three tables users, posts and cities, :

• the fraction of NULL values,
• the average row width,
• the number of distinct values.

The following query displays the most frequent values and their frequency for the attribute users.age.

1
2
3
4

SELECT n_distinct,
       array_to_string(most_common_vals, ' | '), array_to_string(most_common_freqs, ' | ')
FROM pg_stats
WHERE tablename = 'users' and attname = 'age';

Then, knowing that the table users contains 1,000,000 tuples, determine, using these statistics and without querying the DB, how many users are 52 (you can use any other age for which the statistics are easier to extract from the list).

Then run a query that really count the number of persons of that given age. There is certainly a shift between the estimation and the reality. Try to image the strategy used by the ANALYZE; command.

What about multi-attribute conditions? When the selection clause involves several conditions on different attributes, the query planner has to estimate the cardinality of the intersection of the sets satisfying these conditions individually. The matter is that, by default, statistics about the marginal distributions are maintained only.

Write a query to determine the combination of age and city_id the most present in the database. Then, use the statistics from the pg_stats table to estimate how much users are that age and live in that city independantly. Which method and underlying hypothesis is used by the planner to estimate the cardinality of the intersection of these two sets of tuples?

You have certainly remarked that the estimation of multivariate conditions is poor. A way to improve the estimation is to create statistics about the join distribution on these two attributes. Use the following query to build these statistics and check that the estimations are then a little bit more accurate.

1
2

CREATE STATISTICS stts (dependencies) ON age, city_id FROM users;
ANALYZE users;

Querying faster the users table

In this part of the practice, we will discover how to retrieve faster tuples from a table.

Before that, let’s insert some more users. Run the following query that insert 100,000 random users. The query is preceded by an EXPLAIN and ANALYZE

1
2
3
4
5
6

EXPLAIN ANALYZE INSERT INTO users (name, age, city_id)
SELECT
  'user_'||g,
  (random()*60)::int + 10,              -- age 10..70
  1 + (random()*9999)::int              -- random city id 1..10000
FROM generate_series(1, 100000) g;     

Carrefully note the real cost of this insertion, you will need it later.

Now retrieve the number of young users, for instance whose age is between 20 and 25, paying attention to store the total real cost of the query and its associated execution plan.

Let’s make the execution of this query faster by defining a B-TREE index on the age attribute. And now, execute again the query to retrieve the young users, compare its cost and execution plan wrt. the previous run. You should observe a significant improvement.

It is useful to control the size of your tables and their associated indices. The following query gives you this information:

1
2

SELECT pg_size_pretty(pg_relation_size('public.users')) AS table_size,
       pg_size_pretty(pg_total_relation_size('public.users')) AS total_size;

But it has obviously a negative counterpart, else all attributes would be indexed. Insert 100,000 new random users and check the cost of this insertion.

Modify the index on the age attribute in such a way that the following query use the index only to return the answers. Use the following official documentation about indices to find the solution.

1
2

SELECT name
FROM users where age between 14 and 18;

What about textual data?

RDBMSs are certainly not the most appropriate to store and query textual data. A full-text index can be computed into an additional column of type tsvector.

Check the content of the posts table. The content and tags attributes contain text.

Use the following query to create an additional column in the posts table to be used as a full-text index.

1
2
3
4

ALTER TABLE posts ADD COLUMN textvec tsvector;
UPDATE posts SET textvec = to_tsvector('english', content);
CREATE INDEX idx_posts_textvec ON posts USING gin (textvec);
ANALYZE posts;

Here an example of use of a full-text search within an SQL query:

1
2

EXPLAIN ANALYZE
SELECT id FROM posts WHERE textvec @@ to_tsquery('666');

Describe the query execution plan of this query.