官术网_书友最值得收藏!

  • Mastering PostgreSQL 9.6
  • Hans Jurgen Schonig
  • 276字
  • 2021-07-09 19:57:15

Understanding simple queries and the cost model

In this section, we will get started with indexes. To show how things work, some test data is needed. The following code snippet shows how data can be created easily:

test=# CREATE TABLE t_test (id serial, name text); 
CREATE TABLE
test=# INSERT INTO t_test (name) SELECT 'hans'
FROM generate_series(1, 2000000);
INSERT 0 2000000
test=# INSERT INTO t_test (name) SELECT 'paul'
FROM generate_series(1, 2000000);
INSERT 0 2000000

In the first line, a simple table is created. Two columns are used: an auto increment column, which just keeps creating numbers, and a column that will be filled with static values.

The generate_series function will generate numbers from 1 million to 2 million. So in this example, 2 million static values for hans and 2 million static values for paul are created.

In all, 4 million rows have been added:

test=# SELECT name, count(*) FROM t_test GROUP BY 1; 
name | count
------+---------
hans | 2000000
paul | 2000000
(2 rows)

These 4 million rows have some nice properties. IDs are ascending and there are only two distinct names.

Let's run a simple query now:

test=# \timing 
Timing is on.
test=# SELECT * FROM t_test WHERE id = 432332;
id | name
--------+------
432332 | hans
(1 row)

Time: 119.318 ms

In this case, the \timing command will tell psql to show the runtime of a query. Note that this is not the real execution time on the server but the time measured by psql. In case of very short queries, network latency can be a substantial part of the total time, so this has to be taken into account.

主站蜘蛛池模板: 邛崃市| 华安县| 图木舒克市| 资兴市| 汝阳县| 龙陵县| 连江县| 高雄县| 青海省| 象州县| 建水县| 建始县| 连江县| 平陆县| 靖江市| 岢岚县| 古蔺县| 拜城县| 滁州市| 永康市| 逊克县| 高淳县| 富川| 巴马| 武城县| 乌兰浩特市| 定结县| 仁布县| 南平市| 澄城县| 万安县| 翼城县| 万山特区| 南昌市| 崇信县| 湖北省| 成安县| 康定县| 阿尔山市| 仙桃市| 尉犁县|