PostgreSQL – “多态表”vs 3表

我有权限表：

CREATE TABLE public.permissions
(
  id integer NOT NULL DEFAULT nextval('permissions_id_seq'::regclass),item_id integer NOT NULL,item_type character varying NOT NULL,created_at timestamp without time zone NOT NULL,updated_at timestamp without time zone NOT NULL,CONSTRAINT permissions_pkey PRIMARY KEY (id)
)
-- skipping indices declaration,but they would be present
-- on item_id,item_type

以及3对多对多关联的表

-companies_permissions(索引声明)

CREATE TABLE public.companies_permissions
(
  id integer NOT NULL DEFAULT nextval('companies_permissions_id_seq'::regclass),company_id integer,permission_id integer,CONSTRAINT companies_permissions_pkey PRIMARY KEY (id),CONSTRAINT fk_rails_462a923fa2 FOREIGN KEY (company_id)
      REFERENCES public.companies (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,CONSTRAINT fk_rails_9dd0d015b9 FOREIGN KEY (permission_id)
      REFERENCES public.permissions (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE INDEX index_companies_permissions_on_company_id
  ON public.companies_permissions
  USING btree
  (company_id);

CREATE INDEX index_companies_permissions_on_permission_id
  ON public.companies_permissions
  USING btree
  (permission_id);

CREATE UNIQUE INDEX index_companies_permissions_on_permission_id_and_company_id
  ON public.companies_permissions
  USING btree
  (permission_id,company_id);

-permissions_user_groups(索引声明)

CREATE TABLE public.permissions_user_groups
(
  id integer NOT NULL DEFAULT nextval('permissions_user_groups_id_seq'::regclass),user_group_id integer,CONSTRAINT permissions_user_groups_pkey PRIMARY KEY (id),CONSTRAINT fk_rails_c1743245ea FOREIGN KEY (permission_id)
      REFERENCES public.permissions (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,CONSTRAINT fk_rails_e966751863 FOREIGN KEY (user_group_id)
      REFERENCES public.user_groups (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE UNIQUE INDEX index_permissions_user_groups_on_permission_and_user_group
  ON public.permissions_user_groups
  USING btree
  (permission_id,user_group_id);

CREATE INDEX index_permissions_user_groups_on_permission_id
  ON public.permissions_user_groups
  USING btree
  (permission_id);

CREATE INDEX index_permissions_user_groups_on_user_group_id
  ON public.permissions_user_groups
  USING btree
  (user_group_id);

-permissions_users(索引声明)

CREATE TABLE public.permissions_users
(
  id integer NOT NULL DEFAULT nextval('permissions_users_id_seq'::regclass),user_id integer,CONSTRAINT permissions_users_pkey PRIMARY KEY (id),CONSTRAINT fk_rails_26289d56f4 FOREIGN KEY (user_id)
      REFERENCES public.users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,CONSTRAINT fk_rails_7ac7e9f5ad FOREIGN KEY (permission_id)
      REFERENCES public.permissions (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE INDEX index_permissions_users_on_permission_id
  ON public.permissions_users
  USING btree
  (permission_id);

CREATE UNIQUE INDEX index_permissions_users_on_permission_id_and_user_id
  ON public.permissions_users
  USING btree
  (permission_id,user_id);

CREATE INDEX index_permissions_users_on_user_id
  ON public.permissions_users
  USING btree
  (user_id);

我将不得不像这样运行SQL查询很多次：

SELECT
"permissions".*,"permissions_users".*,"companies_permissions".*,"permissions_user_groups".* 
FROM "permissions"
LEFT OUTER JOIN
  "permissions_users" ON "permissions_users"."permission_id" = "permissions"."id"
LEFT OUTER JOIN
  "companies_permissions" ON "companies_permissions"."permission_id" = "permissions"."id"
LEFT OUTER JOIN
  "permissions_user_groups" ON "permissions_user_groups"."permission_id" = "permissions"."id"
WHERE
  (companies_permissions.company_id = <company_id> OR
  permissions_users.user_id in (<user_ids> OR NULL) OR
  permissions_user_groups.user_group_id IN (<user_group_ids> OR NULL)) AND
permissions.item_type = 'Topic'

假设我们在其他表中有大约10000个权限和类似数量的记录.

我需要担心性能吗？

我的意思是…我有4个外部连接,它应该返回结果相当快(说< 200ms). 我正在考虑声明1个“多态”表,类似于：

CREATE TABLE public.permissables
(
  id integer NOT NULL DEFAULT nextval('permissables_id_seq'::regclass),resource_id integer NOT NULL,resource_type character varying NOT NULL,CONSTRAINT permissables_pkey PRIMARY KEY (id)
)
-- skipping indices declaration,but they would be present

然后我可以运行这样的查询：

SELECT
  permissions.*,permissables.*
FROM permissions
LEFT OUTER JOIN
  permissables ON permissables.permission_id = permissions.id
WHERE
  permissions.item_type = 'Topic' AND
  (permissables.owner_id IN (<user_ids>) AND permissables.owner_type = 'User') OR
  (permissables.owner_id = <company_id> AND permissables.owner_type = 'Company') OR
  (permissables.owner_id IN (<user_groups_ids>) AND permissables.owner_type = 'UserGroup')

问题：

哪些选项更好/更快？也许有更好的办法呢？

a)4个表(permissions,companies_permissions,user_groups_permissions,users_permissions)
b)2表(权限,允许)

>我需要声明不同的索引比btree在permissions.item_type？
>我需要每天运行几次真空分析表,使索引工作(两个选项)？

EDIT1：

SQLFiddle示例：

> wildplasser建议(来自评论),不工作：http://sqlfiddle.com/#!15/9723f8/1
>原始查询(4表)：http://sqlfiddle.com/#!15/9723f8/2

{我也删除了错误的地方谢谢@wildplasser}