个人中心
文章发布
退出
作者热门文章
- Java 双重比较
- java - 比较器与 Apache BeanComparator
- Objective-C 完成 block 导致额外的方法调用？
- database - RESTful URI 是否应该公开数据库主键？
27
4
我正在使用的系统有计划。每个计划都直接连接到多个帐户实体。这些帐户实体用于决定谁有权查看计划。
帐户实体可以是以下类型之一:
个人和办公室成员都附加到用户帐户。
每种帐户实体类型都提供不同类型的授权:
此授权逻辑以数据库 View 的形式表示。该 View 有两列，个人或 office_member 的 account_entity_id，以及他们有权访问的计划的 ID。然后将此数据库 View 加入其他查询以建立对整个系统的访问。
-- team_members
CREATE TABLE team_members (
id integer NOT NULL,
plan_id integer NOT NULL,
care_role_id integer DEFAULT 0,
account_entity_id integer,
created timestamp without time zone DEFAULT now() NOT NULL,
deleted boolean DEFAULT false NOT NULL
);
CREATE INDEX idx_team_member_plan_id_care_role_id_deleted ON team_members USING btree (plan_id, care_role_id, deleted);
CREATE INDEX idx_team_members_account_entity_id ON team_members USING btree (account_entity_id);
CREATE INDEX idx_team_members_plan_account_entity ON team_members USING btree (plan_id, account_entity_id);
CREATE INDEX idx_team_members_plan_dashboard ON team_members USING btree (account_entity_id, deleted, created DESC, plan_id);
CREATE INDEX idx_team_members_plan_id ON team_members USING btree (plan_id);
-- account_entities
CREATE TABLE account_entities (
id integer NOT NULL,
account_id integer NOT NULL,
entity_id integer NOT NULL,
entity_type_id integer NOT NULL,
rbac_role_uuid character(36) DEFAULT NULL::bpchar
);
CREATE INDEX idx_account_entities_entity_id_entity_type_id_account_id ON account_entities USING btree (entity_id, entity_type_id, account_id);
CREATE INDEX idx_account_entities_entity_type_id ON account_entities USING btree (entity_type_id);
-- office_members
CREATE TABLE office_members (
person_account_entity_id integer NOT NULL,
office_account_entity_id integer NOT NULL,
rbac_role_uuid character(36) DEFAULT NULL::bpchar
);
CREATE INDEX idx_office_members_location_id ON office_members USING btree (office_account_entity_id);
CREATE INDEX idx_office_members_rbac_role_uuid_location_id ON office_members USING btree (rbac_role_uuid, office_account_entity_id);
-- plans
CREATE TABLE plans (
person_full_name text,
plan_id integer
);
CREATE INDEX idx_plans_plan_id ON plans (plan_id, person_first_name);
-- Plan <-> individual or office member
DROP VIEW IF EXISTS public.accessible_plans;
CREATE OR REPLACE VIEW public.accessible_plans AS (
-- Plan <-> individual
SELECT tm.plan_id content_id, ae_i.id account_entity_id
FROM public.team_members tm
INNER JOIN public.account_entities ae_i ON ae_i.id = tm.account_entity_id
AND ae_i.entity_type_id = 2 -- Individual
WHERE tm.deleted = FALSE
UNION
-- Plan <-> office <-> office member
SELECT tm.plan_id content_id, ae_om.id account_entity_id
FROM public.team_members tm
INNER JOIN public.account_entities ae_o ON ae_o.id = tm.account_entity_id
AND ae_o.entity_type_id = 3 -- Office
INNER JOIN public.office_members om ON om.office_account_entity_id = ae_o.id
INNER JOIN public.account_entities ae_om ON ae_om.entity_id = om.person_account_entity_id
AND ae_om.entity_type_id = 4 -- Office member
WHERE tm.deleted = FALSE
UNION
-- Plan <-> office member <-> office <-> office member
SELECT tm.plan_id content_id, ae_om_om.id account_entity_id
FROM public.team_members tm
INNER JOIN public.account_entities ae_om ON ae_om.id = tm.account_entity_id
AND ae_om.entity_type_id = 4 -- Office member
INNER JOIN public.office_members om ON om.person_account_entity_id = ae_om.entity_id
INNER JOIN public.account_entities ae_o ON ae_o.entity_id = om.office_account_entity_id
AND ae_o.entity_type_id = 3 -- Office
INNER JOIN public.office_members om2 ON om2.office_account_entity_id = ae_o.id
INNER JOIN public.account_entities ae_om_om ON ae_om_om.entity_id = om2.person_account_entity_id
AND ae_om_om.entity_type_id = 4 -- Office member
WHERE tm.deleted = FALSE
);
Unique (cost=488461.98..496389.21 rows=1056964 width=8) (actual time=175241.204..200905.191 rows=52171056 loops=1)
-> Sort (cost=488461.98..491104.39 rows=1056964 width=8) (actual time=175241.202..190780.286 rows=61152360 loops=1)
Sort Key: tm.plan_id, ae_i.id
Sort Method: external merge Disk: 1076024kB
-> Append (cost=6.92..353801.83 rows=1056964 width=8) (actual time=5828.072..30005.448 rows=61152360 loops=1)
-> Nested Loop (cost=6.92..1035.20 rows=43 width=8) (actual time=0.064..0.064 rows=0 loops=1)
-> Index Scan using idx_account_entities_entity_type_id on account_entities ae_i (cost=0.42..8.44 rows=1 width=4) (actual time=0.048..0.049 rows=1 loops=1)
Index Cond: (entity_type_id = 2)
-> Bitmap Heap Scan on team_members tm (cost=6.50..1024.09 rows=267 width=8) (actual time=0.009..0.009 rows=0 loops=1)
Recheck Cond: (account_entity_id = ae_i.id)
Filter: (NOT deleted)
-> Bitmap Index Scan on idx_team_members_account_entity_id (cost=0.00..6.43 rows=267 width=0) (actual time=0.005..0.005 rows=0 loops=1)
Index Cond: (account_entity_id = ae_i.id)
-> Hash Join (cost=8985.39..185496.61 rows=632976 width=8) (actual time=4637.744..4637.744 rows=0 loops=1)
Hash Cond: (ctm_1.account_entity_id = ae_o.id)
-> Seq Scan on team_members ctm_1 (cost=0.00..111477.86 rows=5218086 width=8) (actual time=0.019..1758.942 rows=5218086 loops=1)
Filter: (NOT deleted)
-> Hash (cost=8801.25..8801.25 rows=14731 width=12) (actual time=294.159..294.159 rows=82510 loops=1)
Buckets: 2048 Batches: 4 (originally 1) Memory Usage: 1025kB
-> Hash Join (cost=5136.32..8801.25 rows=14731 width=12) (actual time=141.155..269.546 rows=82510 loops=1)
Hash Cond: (ae_om.entity_id = om.person_account_entity_id)
-> Seq Scan on account_entities ae_om (cost=0.00..3144.95 rows=99379 width=8) (actual time=0.029..28.814 rows=99019 loops=1)
Filter: (entity_type_id = 4)
Rows Removed by Filter: 21927
-> Hash (cost=4911.47..4911.47 rows=17988 width=12) (actual time=140.625..140.625 rows=82510 loops=1)
Buckets: 2048 Batches: 4 (originally 1) Memory Usage: 1025kB
-> Hash Join (cost=1528.62..4911.47 rows=17988 width=12) (actual time=19.159..117.250 rows=82510 loops=1)
Hash Cond: (om.office_account_entity_id = ae_o.id)
-> Seq Scan on office_members om (cost=0.00..2334.52 rows=99252 width=8) (actual time=0.004..51.164 rows=99044 loops=1)
-> Hash (cost=1253.52..1253.52 rows=22008 width=4) (actual time=19.097..19.097 rows=21885 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 770kB
-> Index Scan using idx_account_entities_entity_type_id on account_entities ae_o (cost=0.42..1253.52 rows=22008 width=4) (actual time=0.008..14.818 rows=21885 loops=1)
Index Cond: (entity_type_id = 3)
-> Hash Join (cost=21415.31..156700.39 rows=423945 width=8) (actual time=1190.264..19177.727 rows=61152360 loops=1)
Hash Cond: (ctm_2.account_entity_id = ae_om_1.id)
-> Seq Scan on team_members ctm_2 (cost=0.00..111477.86 rows=5218086 width=8) (actual time=0.034..1105.484 rows=5218086 loops=1)
Filter: (NOT deleted)
-> Hash (cost=21291.98..21291.98 rows=9866 width=8) (actual time=1189.954..1189.954 rows=400053 loops=1)
Buckets: 1024 Batches: 32 (originally 1) Memory Usage: 1025kB
-> Hash Join (cost=17054.58..21291.98 rows=9866 width=8) (actual time=801.650..1105.307 rows=400053 loops=1)
Hash Cond: (ae_om_1.entity_id = om_1.person_account_entity_id)
-> Seq Scan on account_entities ae_om_1 (cost=0.00..3144.95 rows=99379 width=8) (actual time=0.018..22.423 rows=99019 loops=1)
Filter: (entity_type_id = 4)
Rows Removed by Filter: 21927
-> Hash (cost=16903.99..16903.99 rows=12047 width=8) (actual time=799.013..799.013 rows=400053 loops=1)
Buckets: 2048 Batches: 32 (originally 1) Memory Usage: 1025kB
-> Hash Join (cost=13265.90..16903.99 rows=12047 width=8) (actual time=384.006..703.187 rows=400053 loops=1)
Hash Cond: (ae_om_om.entity_id = om2.person_account_entity_id)
-> Seq Scan on account_entities ae_om_om (cost=0.00..3144.95 rows=99379 width=8) (actual time=0.011..22.382 rows=99019 loops=1)
Filter: (entity_type_id = 4)
Rows Removed by Filter: 21927
-> Hash (cost=13082.01..13082.01 rows=14711 width=8) (actual time=380.308..380.308 rows=400053 loops=1)
Buckets: 2048 Batches: 32 (originally 1) Memory Usage: 1025kB
-> Hash Join (cost=9855.99..13082.01 rows=14711 width=8) (actual time=99.375..294.180 rows=400053 loops=1)
Hash Cond: (om2.office_account_entity_id = ae_o_1.id)
-> Seq Scan on office_members om2 (cost=0.00..2334.52 rows=99252 width=8) (actual time=0.003..12.347 rows=99044 loops=1)
-> Hash (cost=9631.01..9631.01 rows=17999 width=8) (actual time=99.061..99.061 rows=99039 loops=1)
Buckets: 2048 Batches: 8 (originally 1) Memory Usage: 1025kB
-> Hash Join (cost=1533.57..9631.01 rows=17999 width=8) (actual time=9.522..72.621 rows=99039 loops=1)
Hash Cond: (om_1.office_account_entity_id = ae_o_1.entity_id)
-> Seq Scan on office_members om_1 (cost=0.00..2334.52 rows=99252 width=8) (actual time=0.002..11.773 rows=99044 loops=1)
-> Hash (cost=1258.47..1258.47 rows=22008 width=8) (actual time=9.497..9.497 rows=21885 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 855kB
-> Index Scan using idx_account_entities_entity_type_id on account_entities ae_o_1 (cost=0.42..1258.47 rows=22008 width=8) (actual time=0.016..5.457 rows=21885 loops=1)
Index Cond: (entity_type_id = 3)
Total runtime: 203539.978 ms
SELECT plan.plan_id, plan.person_full_name
FROM public.plans AS plan
INNER JOIN public.accessible_plans acp ON acp.content_id = plan.plan_id
AND acp.account_entity_id = 30710 -- Logged in office member
ORDER BY person_full_name ASC NULLS LAST
LIMIT 15
Limit (cost=4972.60..4972.64 rows=15 width=21) (actual time=846.592..846.595 rows=15 loops=1)
-> Sort (cost=4972.60..4973.32 rows=285 width=21) (actual time=846.591..846.592 rows=15 loops=1)
Sort Key: plan.person_full_name
Sort Method: top-N heapsort Memory: 26kB
-> Nested Loop (cost=2735.50..4965.61 rows=285 width=21) (actual time=281.224..771.767 rows=56382 loops=1)
-> HashAggregate (cost=2735.07..2737.72 rows=265 width=8) (actual time=281.200..302.871 rows=55393 loops=1)
-> Append (cost=6.83..2733.75 rows=265 width=8) (actual time=0.100..247.291 rows=61448 loops=1)
-> Nested Loop (cost=6.83..990.42 rows=255 width=8) (actual time=0.023..0.023 rows=0 loops=1)
-> Index Scan using idx_account_entities_entity_type_id on account_entities ae_i (cost=0.42..8.44 rows=1 width=4) (actual time=0.022..0.022 rows=0 loops=1)
Index Cond: (entity_type_id = 2)
Filter: (id = 30710)
Rows Removed by Filter: 1
-> Bitmap Heap Scan on team_members tm (cost=6.41..979.43 rows=255 width=8) (never executed)
Recheck Cond: (account_entity_id = 30710)
Filter: (NOT deleted)
-> Bitmap Index Scan on idx_team_members_account_entity_id (cost=0.00..6.34 rows=255 width=0) (never executed)
Index Cond: (account_entity_id = 30710)
-> Nested Loop (cost=6.85..844.87 rows=6 width=8) (actual time=0.021..0.021 rows=0 loops=1)
-> Nested Loop (cost=1.13..23.17 rows=1 width=12) (actual time=0.014..0.015 rows=1 loops=1)
-> Nested Loop (cost=0.71..16.76 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=1)
-> Index Scan using account_entities_pkey on account_entities ae_om (cost=0.42..8.44 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1)
Index Cond: (id = 30710)
Filter: (entity_type_id = 4)
-> Index Scan using office_members_pkey on office_members om (cost=0.29..8.31 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: (person_account_entity_id = ae_om.entity_id)
-> Index Scan using account_entities_pkey on account_entities ae_o (cost=0.42..6.40 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: (id = om.office_account_entity_id)
Filter: (entity_type_id = 3)
-> Bitmap Heap Scan on team_members ctm_1 (cost=5.73..819.04 rows=267 width=8) (actual time=0.004..0.004 rows=0 loops=1)
Recheck Cond: (account_entity_id = ae_o.id)
Filter: (NOT deleted)
-> Bitmap Index Scan on idx_team_members_account_entity_id (cost=0.00..5.66 rows=267 width=0) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: (account_entity_id = ae_o.id)
-> Nested Loop (cost=7.54..895.81 rows=4 width=8) (actual time=0.056..239.982 rows=61448 loops=1)
-> Nested Loop (cost=1.84..66.66 rows=1 width=8) (actual time=0.032..10.580 rows=949 loops=1)
-> Nested Loop (cost=1.42..60.16 rows=1 width=8) (actual time=0.018..3.128 rows=949 loops=1)
-> Nested Loop (cost=1.13..23.26 rows=1 width=8) (actual time=0.006..0.009 rows=1 loops=1)
-> Nested Loop (cost=0.71..16.76 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=1)
-> Index Scan using account_entities_pkey on account_entities ae_om_om (cost=0.42..8.44 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)
Index Cond: (id = 30710)
Filter: (entity_type_id = 4)
-> Index Scan using office_members_pkey on office_members om2 (cost=0.29..8.31 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=1)
Index Cond: (person_account_entity_id = ae_om_om.entity_id)
-> Index Scan using account_entities_pkey on account_entities ae_o_1 (cost=0.42..6.49 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=1)
Index Cond: (id = om2.office_account_entity_id)
Filter: (entity_type_id = 3)
-> Index Scan using idx_office_members_location_id on office_members om_1 (cost=0.29..36.79 rows=11 width=8) (actual time=0.012..2.872 rows=949 loops=1)
Index Cond: (office_account_entity_id = ae_o_1.entity_id)
-> Index Scan using idx_account_entities_entity_id_entity_type_id_account_id on account_entities ae_om_1 (cost=0.42..6.49 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=949)
Index Cond: ((entity_id = om_1.person_account_entity_id) AND (entity_type_id = 4))
-> Bitmap Heap Scan on team_members ctm_2 (cost=5.71..826.48 rows=267 width=8) (actual time=0.023..0.229 rows=65 loops=949)
Recheck Cond: (account_entity_id = ae_om_1.id)
Filter: (NOT deleted)
-> Bitmap Index Scan on idx_team_members_account_entity_id (cost=0.00..5.64 rows=267 width=0) (actual time=0.014..0.014 rows=65 loops=949)
Index Cond: (account_entity_id = ae_om_1.id)
-> Index Scan using idx_plans_plan_id on plans plan (cost=0.43..8.39 rows=1 width=21) (actual time=0.007..0.008 rows=1 loops=55393)
Index Cond: (plan_id = tm.plan_id)
Total runtime: 846.750 ms
这里有很多处理，但这显然在行数较少时有效。它不再工作了。关于查询中更改的实现，我可以更改哪些内容可以让此查询运行得更快？
一个陷阱:- 此信息需要是实时的。不应向用户显示过时的信息。
最佳答案
尝试执行如下操作:我认为您的“offices”子查询需要分解为 2 以帮助优化器。您可能希望根据需要将我的 union alls 更改为 union，但 union all 会更快。
CREATE OR REPLACE VIEW public.accessible_plans AS (
-- Plan <-> office
SELECT tm.plan_id, ae_o.id account_entity_id
FROM public.team_members tm
INNER JOIN public.account_entities ae_o ON ae_o.id = tm.account_entity_id
AND ae_o.entity_type_id = 3 -- Office
INNER JOIN public.office_members om ON om.office_account_entity_id = ae_o.id
INNER JOIN public.account_entities ae_om ON ae_om.entity_id = om.person_account_entity_id
AND ae_om.entity_type_id = 4
WHERE tm.deleted = FALSE
UNION ALL
-- Plan <-> office member <-> office
SELECT tm.plan_id, ae_om2.id office_account_entity_id
FROM public.team_members tm
INNER JOIN public.account_entities ae_om ON ae_om.id = tm.account_entity_id
AND ae_om.entity_type_id = 4 -- Office member
INNER JOIN public.office_members om ON om.person_account_entity_id = ae_om.entity_id
INNER JOIN public.account_entities ae_o ON ae_o.entity_id = om.office_account_entity_id
AND ae_o.entity_type_id = 3 -- Office
INNER JOIN public.office_members om2 ON om2.office_account_entity_id = ae_o.id
INNER JOIN public.account_entities ae_om2 ON ae_om2.entity_id = om2.person_account_entity_id
AND ae_om.entity_type_id = 4 -- Office member
WHERE tm.deleted = FALSE
UNION ALL
-- Plan <-> individual
SELECT tm.plan_id plan_id, ae.id account_entity_id
FROM public.account_entities ae
INNER JOIN public.team_members tm ON tm.account_entity_id = ae.id
WHERE ae.entity_type_id = 2 -- Individual
AND tm.deleted = FALSE
);
关于database - 如何扩展此查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/27613337/
27
4
0
我有三张 table 。表 A 有选项名称(即颜色、尺寸)。表 B 有选项值名称(即蓝色、红色、黑色等)。表C通过将选项名称id和选项名称值id放在一起来建立关系。 我的查询需要显示值和选项的名称，而
在mysql中，如何计算一行中的非空单元格？我只想计算某些列之间的单元格，比如第 3-10 列之间的单元格。不是所有的列...同样，仅在该行中。 最佳答案 如果你想这样做，只能在 sql 中使用名称而
关闭。这个问题需要多问focused 。目前不接受答案。 想要改进此问题吗？更新问题，使其仅关注一个问题 editing this post . 已关闭 7 年前。 Improve this ques
我正在为版本7.6进行Elasticsearch查询 我的查询是这样的: { "query": { "bool": { "should": [ {
关闭。这个问题需要多问focused 。目前不接受答案。 想要改进此问题吗？更新问题，使其仅关注一个问题 editing this post . 已关闭 7 年前。 Improve this ques
是否可以编写一个查询来检查任一子查询(而不是一个子查询)是否正确？ SELECT * FROM employees e WHERE NOT EXISTS (
我找到了很多关于我的问题的答案，但问题没有解决 我有表格，有数据，例如: Data 1 Data 2 Data 3
以下查询返回错误: 查询: SELECT Id, FirstName, LastName, OwnerId, PersonEmail FROM Account WHERE lower(PersonEm
以下查询返回错误: 查询: SELECT Id, FirstName, LastName, OwnerId, PersonEmail FROM Account WHERE lower(PersonEm
我从 EditText 中获取了 String 值。以及提交查询的按钮。 String sql=editQuery.getText().toString();// SELECT * FROM empl
我有一个或多或少有效的查询(关于结果)，但处理大约需要 45 秒。这对于在 GUI 中呈现数据来说肯定太长了。 所以我的需求是找到一个更快/更高效的查询(几毫秒左右会很好)我的数据表大约有 3000
这是我第一次使用 Stack Overflow，所以我希望我以正确的方式提出这个问题。 我有 2 个 SQL 查询，我正在尝试比较和识别缺失值，尽管我无法将 NULL 字段添加到第二个查询中以识别缺失
什么是动态 SQL 查询？何时需要使用动态 SQL 查询？我使用的是 SQL Server 2005。 最佳答案 这里有几篇文章: Introduction to Dynamic SQL Dynami
include "mysql.php"; $query= "SELECT ID,name,displayname,established,summary,searchlink,im
我有一个查询要“转换”为 mysql。这是查询: select top 5 * from (select id, firstName, lastName, sum(fileSize) as To
通过我的研究，我发现至少从 EF 4.1 开始，EF 查询上的 .ToString() 方法将返回要运行的 SQL。事实上，这对我来说非常有用，使用 Entity Framework 5 和 6。 但
我在构造查询来执行以下操作时遇到问题: 按activity_type_id过滤联系人，仅显示最近事件具有所需activity_type_id或为NULL(无事件)的联系人 表格结构如下: 一个联系人可
如何让我输入数据库的信息在输入数据 5 分钟后自行更新？ 假设我有一张 table : +--+--+-----+ |id|ip|count| +--+--+-----+ |
我正在尝试搜索正好是 4 位数字的 ID，我知道我需要使用 LENGTH() 字符串函数，但找不到如何使用它的示例。我正在尝试以下(和其他变体)但它们不起作用。 SELECT max(car_id)
我有一个在 mysql 上运行良好的 sql 查询(查询 + 连接): select sum(pa.price) from user u , purchase pu , pack pa where (
我是一名优秀的程序员,十分优秀！