Install PostgreSQL (Ubuntu) and measure the effect of index

1. Conclusion
– Adding an index didn’t make it much faster.

2. Install PostgreSQL
– reference https://www.postgresql.org/download/linux/ubuntu/

$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql

[sudo] password for wsd01: 

...

Creating config file /etc/postgresql-common/createcluster.conf with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
  en_us
Removing obsolete dictionary files:
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /lib/systemd/system/postgresql.service.
Setting up postgresql-14 (14.5-1.pgdg22.04+1) ...
Creating new PostgreSQL cluster 14/main ...
/usr/lib/postgresql/14/bin/initdb -D /var/lib/postgresql/14/main --auth-local peer --auth-host scram-sha-256 --no-instructions
データベースシステム内のファイルの所有者はユーザー"postgres"となります。
このユーザーをサーバープロセスの所有者とする必要があります。

データベースクラスタはロケール"ja_JP.UTF-8"で初期化されます。
デフォルトのデータベース符号化方式はそれに対応してUTF8に設定されました。
initdb: ロケール"ja_JP.UTF-8"用の適切なテキスト検索設定が見つかりませんでした
デフォルトのテキスト検索構成は simple に設定されます。

データベージのチェックサムは無効です。

ディレクトリ/var/lib/postgresql/14/mainの権限を設定しています ... ok
サブディレクトリを作成しています ... ok
動的共有メモリの実装を選択しています ... posix
デフォルトのmax_connectionsを選択しています ... 100
デフォルトのshared_buffersを選択しています ... 128MB
デフォルトの時間帯を選択しています ... Asia/Tokyo
設定ファイルを作成しています ... ok
ブートストラップスクリプトを実行しています ... ok
ブートストラップ後の初期化を実行しています ... ok
データをディスクに同期しています... ok
update-alternatives: /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) を提供するために自動モードで /usr/share/postgresql/14/man/man1/postmaster.1.gz を使います
Setting up postgresql (14+243.pgdg22.04+1) ...
Processing triggers for man-db (2.10.2-1) ...
Processing triggers for libc-bin (2.35-0ubuntu3.1) ...
$

3. Confirm

$ sudo apt list --installed|grep postgresql

WARNING: apt does not have a stable CLI interface. Use with caution in scripts.

postgresql-14/jammy-pgdg,now 14.5-1.pgdg22.04+1 amd64 [installed,automatic]
postgresql-client-14/jammy-pgdg,now 14.5-1.pgdg22.04+1 amd64 [installed,automatic]
postgresql-client-common/jammy-pgdg,now 243.pgdg22.04+1 all [installed,automatic]
postgresql-common/jammy-pgdg,now 243.pgdg22.04+1 all [installed,automatic]
postgresql/jammy-pgdg,now 14+243.pgdg22.04+1 all [installed]

4. External connection permission

$ sudo vi /etc/postgresql/14/main/postgresql.conf

#listen_addresses = 'localhost'
listen_addresses = '*'

5. IPv4 local connections

$ sudo vi /etc/postgresql/14/main/pg_hba.conf

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             192.168.1.1/24          scram-sha-256

6. Service restart

$ sudo service postgresql restart

6. Create user

$ sudo su - postgres
$ pwd
/var/lib/postgresql
$ ls
14
$ createuser --pwprompt --interactive pgadmin
新しいロールのためのパスワード: [パスワード入力]
もう一度入力してください： [上記とパスワード入力]
新しいロールをスーパユーザにしますか？ (y/n)y

8. Connect from HeidiSQL
(1) Session manager

(2) After connection

9. Connect to postgres from terminal
$ psql -h localhost -U pgadmin -d postgres
ユーザー pgadmin のパスワード:
psql (14.5 (Ubuntu 14.5-1.pgdg22.04+1))
SSL 接続 (プロトコル: TLSv1.3、暗号化方式: TLS_AES_256_GCM_SHA384、ビット長: 256、圧縮: オフ)
“help”でヘルプを表示します。

10. Index effect measurement
– reference
https://symfoware.blog.fc2.com/blog-entry-2527.html

11. Create table
postgres=# CREATE TABLE json_test (doc json);
CREATE TABLE
postgres=# CREATE TABLE jsonb_test (doc jsonb);
CREATE TABLE
postgres=#

12. Check with HeidiSQL

13. exit psql

postgres=# \q
$

14. python version

$ python3 -V
Python 3.10.6

15. exit postgres

exit
$

16. install python3-psycopg2

$ sudo apt-get -y install python3-psycopg2

17. confirm python3-psycopg2

$ sudo apt list --installed|grep python3-psycopg2

WARNING: apt does not have a stable CLI interface. Use with caution in scripts.

python3-psycopg2/jammy-pgdg,now 2.9.4-1.pgdg22.04+1 amd64 [installed]
$

18. Data registration program(python)
– table : json_test

$ sudo su - postgres
$ cat insert.py 
import psycopg2
import psycopg2.extras
con = psycopg2.connect(
    host = 'localhost',
    port = 5432,
    database='postgres',
    user='pgadmin',
    password='123456')
cur = con.cursor()
for i in range(1, 1000001):
    cur.execute("INSERT INTO json_test(doc) VALUES (%s)", [psycopg2.extras.Json({'id': i, 'value':'value1-%d' % (i)})])
    if i % 10000 == 0:
        print(i)
        con.commit()
con.commit()
cur.close()
con.close()

19. Data registration execution

$ python3 insert.py 
10000
20000
...
990000
1000000
$

20. Check with HeidiSQL

21. Data registration program(python)
– table : jsonb_test

$ sudo su - postgres
$ cat insert.py 
import psycopg2
import psycopg2.extras
con = psycopg2.connect(
    host = 'localhost',
    port = 5432,
    database='postgres',
    user='pgadmin',
    password='123456')
cur = con.cursor()
for i in range(1, 1000001):
    cur.execute("INSERT INTO jsonb_test(doc) VALUES (%s)", [psycopg2.extras.Json({'id': i, 'value':'value1-%d' % (i)})])
    if i % 10000 == 0:
        print(i)
        con.commit()
con.commit()
cur.close()
con.close()

22. Data registration execution

$ python3 insert.py 
10000
20000
...
990000
1000000
$

23. Check with HeidiSQL

24. select.py
– table : json_test

$ cat select.py
import psycopg2
import time
import psycopg2.extras
con = psycopg2.connect(
    host = 'localhost',
    port = 5432,
    database='postgres',
    user='pgadmin',
    password='123456')
cur = con.cursor()

start_time = time.perf_counter()
cur.execute("SELECT * FROM json_test where doc->>'id' = '1';")
con.commit()

execution_time = time.perf_counter() - start_time
print(execution_time)

cur.close()
con.close()

25. Run time of select.py

$ python3 select.py 
0.2227892939990852
$

26. select2.py
– table : jsonb_test

$ cat select.py
import psycopg2
import time
import psycopg2.extras
con = psycopg2.connect(
    host = 'localhost',
    port = 5432,
    database='postgres',
    user='pgadmin',
    password='123456')
cur = con.cursor()

start_time = time.perf_counter()
cur.execute("SELECT * FROM jsonb_test where doc->>'id' = '1';")
con.commit()

execution_time = time.perf_counter() - start_time
print(execution_time)

cur.close()
con.close()

27. Run time of select2.py

$ python3 select2.py 
0.11176194200379541
$

28. @>演算子

$  psql -h localhost -U pgadmin -d postgres
ユーザー pgadmin のパスワード: 
psql (14.5 (Ubuntu 14.5-1.pgdg22.04+1))
SSL 接続 (プロトコル: TLSv1.3、暗号化方式: TLS_AES_256_GCM_SHA384、ビット長: 256、圧縮: オフ)
"help"でヘルプを表示します。

postgres=# SELECT * FROM jsonb_test where doc @> '{"id":1}';
              doc               
--------------------------------
 {"id": 1, "value": "value1-1"}
(1 行)

postgres=# SELECT * FROM json_test where doc @> '{"id":1}';
ERROR:  演算子が存在しません: json @> unknown
行 1: SELECT * FROM json_test where doc @> '{"id":1}';
                                        ^
HINT:  指定した名称と引数の型に合う演算子がありません。明示的な型キャストが必要かもしれません。
postgres=# 
SELECT * FROM json_test where doc::jsonb @> '{"id":1}';
              doc               
--------------------------------
 {"id": 1, "value": "value1-1"}
(1 行)

postgres=#

29. Query plan

postgres=# EXPLAIN SELECT * FROM json_test where doc::jsonb @> '{"id":1}';
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Gather  (cost=1000.00..18536.67 rows=10000 width=40)
   Workers Planned: 2
   ->  Parallel Seq Scan on json_test  (cost=0.00..16536.67 rows=4167 width=40)
         Filter: ((doc)::jsonb @> '{"id": 1}'::jsonb)
(4 行)

postgres=# EXPLAIN SELECT * FROM jsonb_test where doc @> '{"id":1}';
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Gather  (cost=1000.00..16518.33 rows=100 width=51)
   Workers Planned: 2
   ->  Parallel Seq Scan on jsonb_test  (cost=0.00..15508.33 rows=42 width=51)
         Filter: (doc @> '{"id": 1}'::jsonb)
(4 行)

postgres=#

30. Create index

postgres=# CREATE INDEX idx_doc ON jsonb_test USING GIN (doc);
CREATE INDEX
postgres=#

31. Check with HeidiSQL

32. exec select2.py add index

$ python3 select2.py 
0.10611801099730656

33. exec select2.py add index without commit

$ python3 select2.py 
0.10100354099995457

34. Query plan (josonb_test)

postgres=# EXPLAIN SELECT * FROM jsonb_test where doc @> '{"id":1}';
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Bitmap Heap Scan on jsonb_test  (cost=36.77..408.47 rows=100 width=51)
   Recheck Cond: (doc @> '{"id": 1}'::jsonb)
   ->  Bitmap Index Scan on idx_doc  (cost=0.00..36.75 rows=100 width=0)
         Index Cond: (doc @> '{"id": 1}'::jsonb)
(4 行)

postgres=#

35. Query plan (json_test)

postgres=# EXPLAIN SELECT * FROM jsonb_test where doc->'id' = '1';

 Gather  (cost=1000.00..18050.00 rows=5000 width=51)
   Workers Planned: 2
   ->  Parallel Seq Scan on jsonb_test  (cost=0.00..16550.00 rows=2083 width=51)
         Filter: ((doc -> 'id'::text) = '1'::jsonb)
(4 行)

36. result ( python3 select2.py )
– no index : 0.11176194200379541 (#27)
– add index: 0.10611801099730656 (#32)

Web Service Development

Open Source Development Blog

Install PostgreSQL (Ubuntu) and measure the effect of index