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

  • Mastering PostgreSQL 10
  • Hans Jürgen Sch?nig
  • 282字
  • 2021-06-30 19:03:53

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 autoincrement column that just keeps creating numbers and a column that will be filled with static values.

The   generate_series  function will generate numbers from 1 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: 176.949 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.

主站蜘蛛池模板: 承德县| 喀喇| 东阳市| 平南县| 从化市| 隆昌县| 涡阳县| 车险| 荣昌县| 苏尼特左旗| 金溪县| 垦利县| 常熟市| 靖安县| 泾阳县| 泗水县| 玛多县| 赫章县| 准格尔旗| 广水市| 年辖:市辖区| 隆林| 安庆市| 綦江县| 大足县| 汶川县| 镇原县| 灵石县| 黄龙县| 高雄市| 灵丘县| 赤城县| 东乡| 中阳县| 高清| 潞城市| 和田市| 江油市| 越西县| 县级市| 南昌市|