September 28, 2015 at 18:34PostgreSQL 9.5: what's new? Part 2. TABLESAMPLE
We continue to review the innovations in PostgreSQL 9.5.
Part 1. INSERT ... ON CONFLICT DO NOTHING / UPDATE and ROW LEVEL SECURITY .
Part 3. GROUPING SETS, CUBE, ROLLUP
Sometimes there are tasks in which you need to select a number of random entries from the table, for this you wrote sophisticated queries (to get really random data - you need to sweat a lot). With the release of PostgreSQL 9.5, this task will become easier.
Using the TABLESAMPLE keyword, you can select not all data from a table, but only some part of them, select a sample.
The syntax would be something like this:
sampling_method is a sampling method, by default in PostgreSQL 9.5 there are two of them: SYSTEM and BERNOULLI , they take a floating point number (or any valid expression that results in a number) as an argument, which is interpreted as a percentage for sampling: from 0 to 100.
Let's look at examples of how sampling works in PostgreSQL 9.5.
Suppose we have a table with transactions in which the transaction id, the transaction amount and the date with the time when the transaction was completed are stored. Add 100,000 entries to the table.
Let's try to take a sample of records 0.1% from the original table (100 records):
Why didn’t we get 100 records, but 157? The fact is that PostgreSQL stores the table data in the form of an array of 8 kb pages (by default, this parameter can be changed when building the server from the source codes) and with the sampling method SYSTEM simply takes the desired number of random pages for a given number of percent and gives them “ as it is". In this case, 157 entries are placed on one page. If you request 2 times more records for the sample, then data from 2 pages will be taken:
It should be understood that different pages can store a different number of records and therefore the number of returned records can vary from request to request.
In order to get the exact number of records, you can use the LIMIT expression , but it is worthwhile to understand that in this case, anyway, we will get records from one page. Therefore, if the values in the records depend on the order in which these records were inserted or the nature of the values in the records themselves is chronological (as in our case in the ending_time field), then most likely you will get meaningless results by making samples. For example, if we want to find out through the sample the maximum date when the transaction was made, then making the same request several times, we will get completely different results:
Whereas the real value will be:
In order to get a more distributed sample, you can use the BERNOULLI sampling method , which scans the entire table (in fact, “throws a coin” for each record) and selects random records:
Now let's look at the performance, try to analyze the average transaction amount in three ways and get the average:
1) Without a sample:
2) Sample by SYSTEM method :
3) BERNOULLI sample :
We see that sampling using the SYSTEM method is faster, but at the same time its accuracy is lower, while sampling by the BERNOULLI method is slower, but its accuracy is higher. You can choose a compromise between speed and accuracy. Also note that a new type of scan is used for sampling: Sample scan.
Add more records to the table, let it have 20 million records:
We see that with an increase in the number of records, the BERNOULLI method loses more performance. This is because it does, in fact, a full table scan, while SYSTEM just returns a few pages.
Now let's try to increase the percentage for selecting records:
As you can see, the SYSTEM method loses more performance when increasing the percentage of the sample. This is logical, since BERNOULLI does just as it does a full scan, while SYSTEM should return 10 times as many pages.
As a result, it can be noted that the SYSTEM method at a small percentage of the sample works much faster than BERNOULLI , but at the same time gives a less random selection of records. But with an increase in interest, this advantage is lost.
Using the optional REPEATABLE keyword, we can specify the seed for the random generator. If two queries have the same sampling method, the percentage of sampling andseed , then the same sample will be selected for these two queries:
As we saw above, if the REPEATABLE keyword is not specified, then each time the selection will be different from the previous one.
Separately, it is worth noting that sampling is performed BEFORE the WHERE clause , that is, it will not be possible to select a sample by condition. In this case, a sample will be selected first, and then the WHERE condition will be applied , but since the probability of getting records with id <100 from a table of 20,000,000 records is very small, the result will be empty:
SYSTEM and BERNOULLI are not the only sampling options; if you wish, you can write your own method for sampling. The documentation for this lies here . Moreover, custom sampling methods may take more than one argument or not at all. Custom methods may also ignore the REPEATABLE keyword .
This concludes my short story on sampling in PostgreSQL 9.5. Thanks for attention!
PS You can roughly estimate the number of records in a table using a sample :)
PPS Do not do as described above, this is a joke and does not always work as it should.
In the next part: GROUPINS SETS, ROLLUP, CUBE .
Part 1. INSERT ... ON CONFLICT DO NOTHING / UPDATE and ROW LEVEL SECURITY .
Part 3. GROUPING SETS, CUBE, ROLLUP
From the author
I apologize for the delay in releasing the second part. Initially, I planned to release the second part of the article a week after the first, but, due to my high workload, I could not do this. Therefore, I decided that I would publish not large articles, but in small portions, but more often.
Sometimes there are tasks in which you need to select a number of random entries from the table, for this you wrote sophisticated queries (to get really random data - you need to sweat a lot). With the release of PostgreSQL 9.5, this task will become easier.
Using the TABLESAMPLE keyword, you can select not all data from a table, but only some part of them, select a sample.
The syntax would be something like this:
SELECT ... FROM TABLE_NAME ... TABLESAMPLE sampling_method (argument [, ...]) [REPEATABLE (seed)]
sampling_method is a sampling method, by default in PostgreSQL 9.5 there are two of them: SYSTEM and BERNOULLI , they take a floating point number (or any valid expression that results in a number) as an argument, which is interpreted as a percentage for sampling: from 0 to 100.
Let's look at examples of how sampling works in PostgreSQL 9.5.
Suppose we have a table with transactions in which the transaction id, the transaction amount and the date with the time when the transaction was completed are stored. Add 100,000 entries to the table.
CREATE TABLE transactions (
id SERIAL PRIMARY KEY,
amount NUMERIC (15,2),
ending_time TIMESTAMP
);
INSERT INTO transactions (amount, ending_time)
SELECT
(round (CAST (random () * 100000 AS NUMERIC), 2)),
now () - random () * CAST ('1 day' AS INTERVAL)
FROM generate_series (1, 100000 );
Let's try to take a sample of records 0.1% from the original table (100 records):
SELECT * FROM transactions TABLESAMPLE SYSTEM (0.1)
Total query runtime: 213 ms.
157 rows retrieved.
Why didn’t we get 100 records, but 157? The fact is that PostgreSQL stores the table data in the form of an array of 8 kb pages (by default, this parameter can be changed when building the server from the source codes) and with the sampling method SYSTEM simply takes the desired number of random pages for a given number of percent and gives them “ as it is". In this case, 157 entries are placed on one page. If you request 2 times more records for the sample, then data from 2 pages will be taken:
SELECT * FROM transactions TABLESAMPLE SYSTEM (0.2)
Total query runtime: 21 ms.
314 rows retrieved.
It should be understood that different pages can store a different number of records and therefore the number of returned records can vary from request to request.
In order to get the exact number of records, you can use the LIMIT expression , but it is worthwhile to understand that in this case, anyway, we will get records from one page. Therefore, if the values in the records depend on the order in which these records were inserted or the nature of the values in the records themselves is chronological (as in our case in the ending_time field), then most likely you will get meaningless results by making samples. For example, if we want to find out through the sample the maximum date when the transaction was made, then making the same request several times, we will get completely different results:
SELECT MAX (ending_time) FROM transactions TABLESAMPLE SYSTEM (0.1)
| max |
|---|
| 2014-11-08 22: 30: 32.720855 |
SELECT MAX (ending_time) FROM transactions TABLESAMPLE SYSTEM (0.1)
| max |
|---|
| 2014-12-02 11: 42: 32.720855 |
SELECT MAX (ending_time) FROM transactions TABLESAMPLE SYSTEM (0.1)
| max |
|---|
| 2014-10-21 09: 40: 32.720855 |
Whereas the real value will be:
SELECT MAX (ending_time) FROM transactions
| max |
|---|
| 2014-12-07 04: 04: 32.720855 |
In order to get a more distributed sample, you can use the BERNOULLI sampling method , which scans the entire table (in fact, “throws a coin” for each record) and selects random records:
SELECT MAX (ending_time) FROM transactions TABLESAMPLE BERNOULLI (0.1)
| max |
|---|
| 2014-12-07 00: 06: 32.720855 |
Now let's look at the performance, try to analyze the average transaction amount in three ways and get the average:
1) Without a sample:
EXPLAIN ANALYZE SELECT AVG (amount) FROM transactions
"Aggregate (cost = 1887.00..1887.01 rows = 1 width = 8) (actual time = 25.795..25.795 rows = 1 loops = 1)"
"-> Seq Scan on transactions (cost = 0.00..1637.00 rows = 100000 width = 8) (actual time = 0.005..12.438 rows = 100000 loops = 1) "
" Planning time: 0.055 ms "
" Execution time: 25.816 ms "
SELECT AVG (amount) FROM transactions
50028.8742828
2) Sample by SYSTEM method :
EXPLAIN ANALYZE SELECT AVG (amount) FROM transactions TABLESAMPLE SYSTEM (0.1)
"Aggregate (cost = 1.25..1.26 rows = 1 width = 8) (actual time = 0.088..0.088 rows = 1 loops = 1)"
"-> Sample Scan (system) on transactions (cost = 0.00..1.00 rows = 100 width = 8) (actual time = 0.017..0.048 rows = 157 loops = 1) "
" Planning time: 0.068 ms "
" Execution time: 0.120 ms "
SELECT AVG (amount) FROM transactions TABLESAMPLE SYSTEM (0.1)
53628.223694267516
3) BERNOULLI sample :
EXPLAIN ANALYZE SELECT AVG (amount) FROM transactions TABLESAMPLE BERNOULLI (0.1)
"Aggregate (cost = 638.25..638.26 rows = 1 width = 8) (actual time = 2.847..2.847 rows = 1 loops = 1)"
"-> Sample Scan (bernoulli) on transactions (cost = 0.00..638.00 rows = 100 width = 8) (actual time = 0.020..2.780 rows = 104 loops = 1) "
" Planning time: 0.145 ms "
" Execution time: 2.872 ms "
SELECT AVG (amount) FROM transactions TABLESAMPLE BERNOULLI (0.1)
50285.863240740741
We see that sampling using the SYSTEM method is faster, but at the same time its accuracy is lower, while sampling by the BERNOULLI method is slower, but its accuracy is higher. You can choose a compromise between speed and accuracy. Also note that a new type of scan is used for sampling: Sample scan.
Add more records to the table, let it have 20 million records:
INSERT INTO transactions (amount, ending_time)
SELECT
(round (CAST (random () * 100000 AS DECIMAL), 2)),
now () - INTERVAL '1 year' + (i * INTERVAL '1 minute')
FROM generate_series (100001 , 20,000,000) i;
EXPLAIN ANALYZE SELECT AVG (amount) FROM transactions;
"Aggregate (cost = 377372.70..377372.71 rows = 1 width = 8) (actual time = 4604.297..4604.297 rows = 1 loops = 1)"
"-> Seq Scan on transactions (cost = 0.00..327375.96 rows = 19998696 width = 8) (actual time = 0.027..2043.846 rows = 20,000,000 loops = 1) "
" Planning time: 0.063 ms "
" Execution time: 4604.325 ms "
SELECT AVG (amount) FROM transactions;
50002.
"Aggregate (cost = 757.99..758.00 rows = 1 width = 8) (actual time = 7.309..7.309 rows = 1 loops = 1)"
"-> Sample Scan (system) on transactions (cost = 0.00..707.99 rows = 19999 width = 8) (actual time = 0.057..4.588 rows = 20096 loops = 1) "
" Planning time: 0.073 ms "
" Execution time: 7.340 ms "
SELECT AVG (amount) FROM transactions TABLESAMPLE SYSTEM (0.1)
50323.198322551752
EXPLAIN ANALYZE SELECT AVG (amount) FROM transactions TABLESAMPLE BERNOULLI (0.1)
"Aggregate (cost = 127638.99..127639.00 rows = 1 width = 8) (actual time = 751.831..751.832 rows = 1 loops = 1)"
"-> Sample Scan (bernoulli) on transactions (cost = 0.00..127588.99 rows = 19999 width = 8) (actual time = 0.260..747.682 rows = 19899 loops = 1) "
"Planning time: 0.055 ms "
"Execution time: 751.879 ms"
SELECT AVG (amount) FROM transactions TABLESAMPLE BERNOULLI (0.1)
50043.386386377336
We see that with an increase in the number of records, the BERNOULLI method loses more performance. This is because it does, in fact, a full table scan, while SYSTEM just returns a few pages.
Now let's try to increase the percentage for selecting records:
EXPLAIN ANALYZE SELECT AVG (amount) FROM transactions TABLESAMPLE SYSTEM (1)
"Aggregate (cost = 7591.84..7591.85 rows = 1 width = 8) (actual time = 65.055..65.055 rows = 1 loops = 1)"
"-> Sample Scan (system) on transactions (cost = 0.00..7091.87 rows = 199987 width = 8) (actual time = 0.043..37.939 rows = 200018 loops = 1) "
" Planning time: 0.053 ms "
" Execution time: 65.083 ms "
EXPLAIN ANALYZE SELECT AVG (amount) FROM transactions TABLESAMPLE BERNOULLI (1)
"Aggregate (cost = 129888.84..129888.85 rows = 1 width = 8) (actual time = 799.826..799.826 rows = 1 loops = 1)"
"-> Sample Scan (bernoulli) on transactions (cost = 0.00..129388.87 rows = 199987 width = 8) (actual time = 0.035..769.899 rows = 199682 loops = 1) "
"Planning time: 0.063 ms "
"Execution time: 799.859 ms"
As you can see, the SYSTEM method loses more performance when increasing the percentage of the sample. This is logical, since BERNOULLI does just as it does a full scan, while SYSTEM should return 10 times as many pages.
As a result, it can be noted that the SYSTEM method at a small percentage of the sample works much faster than BERNOULLI , but at the same time gives a less random selection of records. But with an increase in interest, this advantage is lost.
Using the optional REPEATABLE keyword, we can specify the seed for the random generator. If two queries have the same sampling method, the percentage of sampling andseed , then the same sample will be selected for these two queries:
SELECT MAX (amount) FROM transactions TABLESAMPLE SYSTEM (0.1) REPEATABLE (50)
99997.91
SELECT MAX (amount) FROM transactions TABLESAMPLE SYSTEM (0.1) REPEATABLE (300)
99999.15
SELECT MAX (amount) FROM transactions TABLESAMPLE BERNOULLI (0.1) REPEATABLE (50)
99995.9
SELECT MAX (amount) FROM transactions TABLESAMPLE SYSTEM (0.1) REPEATABLE (50)
99997.91
As we saw above, if the REPEATABLE keyword is not specified, then each time the selection will be different from the previous one.
Separately, it is worth noting that sampling is performed BEFORE the WHERE clause , that is, it will not be possible to select a sample by condition. In this case, a sample will be selected first, and then the WHERE condition will be applied , but since the probability of getting records with id <100 from a table of 20,000,000 records is very small, the result will be empty:
SELECT * FROM transactions TABLESAMPLE SYSTEM (1) WHERE id <100
Total query runtime: 31 ms.
0 rows retrieved.
SYSTEM and BERNOULLI are not the only sampling options; if you wish, you can write your own method for sampling. The documentation for this lies here . Moreover, custom sampling methods may take more than one argument or not at all. Custom methods may also ignore the REPEATABLE keyword .
This concludes my short story on sampling in PostgreSQL 9.5. Thanks for attention!
PS You can roughly estimate the number of records in a table using a sample :)
SELECT COUNT (*) * 100.0 FROM transactions TABLESAMPLE SYSTEM (1);
20001800
PPS Do not do as described above, this is a joke and does not always work as it should.
In the next part: GROUPINS SETS, ROLLUP, CUBE .
