- 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.
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.
- 中文版Photoshop CS5數碼照片處理完全自學一本通
- Introduction to DevOps with Kubernetes
- Visual FoxPro 6.0數據庫與程序設計
- 工業機器人工程應用虛擬仿真教程:MotoSim EG-VRC
- Docker Quick Start Guide
- 機器人編程實戰
- Implementing Oracle API Platform Cloud Service
- Java Web整合開發全程指南
- 網絡布線與小型局域網搭建
- Visual FoxPro數據庫基礎及應用
- 大數據驅動的機械裝備智能運維理論及應用
- 內??刂萍捌鋺?/a>
- Azure PowerShell Quick Start Guide
- PostgreSQL 10 High Performance
- Getting Started with Tableau 2019.2