1. 查询
通过索引查询
idm_prod_20180405=# \d token_store;
Table "public.token_store"
Column | Type | Modifiers
----------------------+-----------------------------+-----------
uuid | character varying(50) | not null
request_token | character varying(50) |
authentication_token | text |
subject | character varying(50) |
expiration | timestamp without time zone |
token_id | character varying(50) |
refresh_before | timestamp without time zone |
refresh_id | character varying(50) |
Indexes:
"token_store_new_pkey" PRIMARY KEY, btree (uuid)
"token_store_new_request_token_idx" btree (request_token)
idm_prod_20180405=# explain analyze select * from token_store where request_token = '77c7811d-637a-4247-8635-59ed75bdc526';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
----------------------------
Index Scan using token_store_new_request_token_idx on token_store (cost=0.28..8.30 rows=1 width=1358) (actual time=0
.061..0.064 rows=1 loops=1)
Index Cond: ((request_token)::text = '77c7811d-637a-4247-8635-59ed75bdc526'::text)
Planning time: 0.167 ms
Execution time: 0.182 ms
(4 rows)
无索引查询
idm_prod_20180405=# drop index token_store_new_request_token_idx;
DROP INDEX
idm_prod_20180405=# \d token_store;
Table "public.token_store"
Column | Type | Modifiers
----------------------+-----------------------------+-----------
uuid | character varying(50) | not null
request_token | character varying(50) |
authentication_token | text |
subject | character varying(50) |
expiration | timestamp without time zone |
token_id | character varying(50) |
refresh_before | timestamp without time zone |
refresh_id | character varying(50) |
Indexes:
"token_store_new_pkey" PRIMARY KEY, btree (uuid)
idm_prod_20180405=# explain analyze select * from token_store where request_token = '77c7811d-637a-4247-8635-59ed75bdc526';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Seq Scan on token_store (cost=0.00..942.64 rows=1 width=1358) (actual time=0.031..10.595 rows=1 loops=1)
Filter: ((request_token)::text = '77c7811d-637a-4247-8635-59ed75bdc526'::text)
Rows Removed by Filter: 6200
Planning time: 0.236 ms
Execution time: 10.641 ms
(5 rows)
PS:
idm_prod_20180405=# select count(*) from token_store ;
count
-------
6201
(1 row)
2. Join
idm_prod_20180405=# explain analyze SELECT o.* FROM abstract_user u
idm_prod_20180405-# INNER JOIN organizations o on (u.display_name = 'tx' and u.organization = o.uuid )
idm_prod_20180405-# INNER JOIN roles_users ru on ru.user_id = u.uuid
idm_prod_20180405-# INNER JOIN roles r on (ru.role_id = r.uuid )
idm_prod_20180405-# INNER JOIN permission_role rp on rp.role_id = r.uuid
idm_prod_20180405-# INNER JOIN permission p on (p.uuid = rp.permission_id )
idm_prod_20180405-# WHERE upper(p.name) in ('BYOIP') and o.deleted is null and r.deleted is null and p.deleted is null and u.deleted is null ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.39..212.33 rows=1 width=192) (actual time=2.371..4.317 rows=3 loops=1)
Join Filter: ((ru.role_id)::text = (r.uuid)::text)
-> Nested Loop (cost=1.11..212.00 rows=1 width=258) (actual time=2.330..4.207 rows=3 loops=1)
-> Nested Loop (cost=0.97..209.31 rows=15 width=289) (actual time=2.267..3.289 rows=26 loops=1)
-> Nested Loop (cost=0.56..208.23 rows=1 width=225) (actual time=2.183..3.021 rows=3 loops=1)
-> Nested Loop (cost=0.28..199.92 rows=1 width=225) (actual time=0.216..2.851 rows=5 loops=1)
-> Seq Scan on abstract_user u (cost=0.00..191.61 rows=1 width=66) (actual time=0.145..2.640 rows=5 loops=1)
Filter: ((deleted IS NULL) AND ((display_name)::text = 'tx'::text))
Rows Removed by Filter: 4572
-> Index Scan using organizations_pkey on organizations o (cost=0.28..8.29 rows=1 width=192) (actual time=0.035..0.037 rows=1 loops=5)
Index Cond: ((uuid)::text = (u.organization)::text)
Filter: (deleted IS NULL)
-> Index Scan using ru_u_index on roles_users ru (cost=0.28..8.30 rows=1 width=66) (actual time=0.030..0.031 rows=1 loops=5)
Index Cond: ((user_id)::text = (u.uuid)::text)
-> Index Only Scan using permission_role_pk on permission_role rp (cost=0.41..0.97 rows=11 width=64) (actual time=0.065..0.077 rows=9 loops=3)
Index Cond: (role_id = (ru.role_id)::text)
Heap Fetches: 26
-> Index Scan using roles_pkey on permission p (cost=0.14..0.17 rows=1 width=32) (actual time=0.033..0.033 rows=0 loops=26)
Index Cond: ((uuid)::text = (rp.permission_id)::text)
Filter: ((deleted IS NULL) AND (upper((name)::text) = 'BYOIP'::text))
Rows Removed by Filter: 1
-> Index Scan using roles_pkey1 on roles r (cost=0.28..0.32 rows=1 width=33) (actual time=0.030..0.031 rows=1 loops=3)
Index Cond: ((uuid)::text = (rp.role_id)::text)
Filter: (deleted IS NULL)
Planning time: 13.906 ms
Execution time: 4.543 ms
(26 rows)
idm_prod_20180405=# explain analyze SELECT o.* FROM abstract_user u
idm_prod_20180405-# INNER JOIN organizations o on (u.display_name = 'tx' and u.organization = o.uuid and u.deleted is null and o.deleted is null)
idm_prod_20180405-# INNER JOIN roles_users ru on ru.user_id = u.uuid
idm_prod_20180405-# INNER JOIN roles r on (ru.role_id = r.uuid and r.deleted is null)
idm_prod_20180405-# INNER JOIN permission_role rp on rp.role_id = r.uuid
idm_prod_20180405-# INNER JOIN permission p on (p.uuid = rp.permission_id and p.deleted is null)
idm_prod_20180405-# WHERE upper(p.name) in ('BYOIP')
idm_prod_20180405-# ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.39..212.33 rows=1 width=192) (actual time=2.381..4.418 rows=3 loops=1)
Join Filter: ((ru.role_id)::text = (r.uuid)::text)
-> Nested Loop (cost=1.11..212.00 rows=1 width=258) (actual time=2.340..4.307 rows=3 loops=1)
-> Nested Loop (cost=0.97..209.31 rows=15 width=289) (actual time=2.277..3.351 rows=26 loops=1)
-> Nested Loop (cost=0.56..208.23 rows=1 width=225) (actual time=2.189..3.091 rows=3 loops=1)
-> Nested Loop (cost=0.28..199.92 rows=1 width=225) (actual time=0.245..2.916 rows=5 loops=1)
-> Seq Scan on abstract_user u (cost=0.00..191.61 rows=1 width=66) (actual time=0.170..2.688 rows=5 loops=1)
Filter: ((deleted IS NULL) AND ((display_name)::text = 'tx'::text))
Rows Removed by Filter: 4572
-> Index Scan using organizations_pkey on organizations o (cost=0.28..8.29 rows=1 width=192) (actual time=0.037..0.039 rows=1 loops=5)
Index Cond: ((uuid)::text = (u.organization)::text)
Filter: (deleted IS NULL)
-> Index Scan using ru_u_index on roles_users ru (cost=0.28..8.30 rows=1 width=66) (actual time=0.031..0.032 rows=1 loops=5)
Index Cond: ((user_id)::text = (u.uuid)::text)
-> Index Only Scan using permission_role_pk on permission_role rp (cost=0.41..0.97 rows=11 width=64) (actual time=0.061..0.075 rows=9 loops=3)
Index Cond: (role_id = (ru.role_id)::text)
Heap Fetches: 26
-> Index Scan using roles_pkey on permission p (cost=0.14..0.17 rows=1 width=32) (actual time=0.035..0.035 rows=0 loops=26)
Index Cond: ((uuid)::text = (rp.permission_id)::text)
Filter: ((deleted IS NULL) AND (upper((name)::text) = 'BYOIP'::text))
Rows Removed by Filter: 1
-> Index Scan using roles_pkey1 on roles r (cost=0.28..0.32 rows=1 width=33) (actual time=0.030..0.032 rows=1 loops=3)
Index Cond: ((uuid)::text = (rp.role_id)::text)
Filter: (deleted IS NULL)
Planning time: 13.250 ms
Execution time: 4.638 ms
(26 rows)
idm_prod_20180405=#
以上红色部分可以看出数据库对sql做过优化处理,