Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cseIw-0003yr-2v for pgsql-performance@arkaria.postgresql.org; Mon, 27 Mar 2017 23:44:06 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cseIv-0000Xf-EC for pgsql-performance@arkaria.postgresql.org; Mon, 27 Mar 2017 23:44:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cseIu-0000Wy-ES for pgsql-performance@postgresql.org; Mon, 27 Mar 2017 23:44:04 +0000 Received: from outgoing2.cpt4.host-h.net ([197.189.247.35]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cseIl-0005et-Mp for pgsql-performance@postgresql.org; Mon, 27 Mar 2017 23:44:03 +0000 Received: from www20.cpt1.host-h.net ([197.221.2.20]) by antispam1-cpt4.host-h.net with esmtpsa (TLSv1.2:AES128-SHA:128) (Exim 4.86) (envelope-from ) id 1cseIf-0007pI-0p for pgsql-performance@postgresql.org; Tue, 28 Mar 2017 01:43:51 +0200 Received: from 196-210-206-12.dynamic.isadsl.co.za ([196.210.206.12] helo=DMZHOST) by www20.cpt1.host-h.net with esmtpa (Exim 4.80) (envelope-from ) id 1cseId-0004yt-Id for pgsql-performance@postgresql.org; Tue, 28 Mar 2017 01:43:47 +0200 Received: from Win8Dev (Unknown [10.0.0.41]) by DMZHOST with ESMTPA ; Tue, 28 Mar 2017 01:43:38 +0200 From: "Riaan Stander" To: Subject: Best design for performance Date: Tue, 28 Mar 2017 01:43:37 +0200 Message-ID: <04c401d2a753$f4b78e00$de26aa00$@exa.co.za> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_04C5_01D2A764.B841BD90" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AdKnTdZxm91xfqTFQtCbfN4J2ks7qw== Content-Language: en-za X-Authenticated-Sender: smtprelay@exa.co.za X-Virus-Scanned: Clear (ClamAV 0.99.2/23243/Mon Mar 27 22:35:03 2017) X-Originating-IP: 197.221.2.20 X-SpamExperts-Domain: exa.co.za X-SpamExperts-Username: Authentication-Results: host-h.net; auth=pass (login) smtp.auth=@exa.co.za X-SpamExperts-Outgoing-Class: ham X-SpamExperts-Outgoing-Evidence: Combined (0.21) X-Recommended-Action: accept X-Filter-ID: s0sct1PQhAABKnZB5plbIWL5vMUYxb2kq1uspbfEA9+ihNzEqHRR3fXVWQ7SEawUiwQzKw+6v3Ca IMG6s7LqJJlV3j2ipgsAbbE1JvuwkkgZ4m00l3uPRdWwNfw+Po+GO76XSTAwtV4mg4i2ouCDa4AU hvIWAV5xUW/+gAh4vXo3X2oaEiyuyXQPfwpwkenURcOb18WfxGyg6Om6u4YYm6x0vEfH9Ca93RgN XDQjviiMytmwoSOAsUiZHL4elTOeW/T3iEdLQrEzANTH1RO+ZuWBOXp8nHKe0R+FkIqN7hlBaLCK i/3d82ndyQckqlk5z3irZHx8XwBM/aZ+josQAfUf9oDBqtClgM5jH/om1Q4tH6VyaEKKyFb3KuYS PtYeOngwKsK/v8O00LnH1NcGs0PkGda5olUXOJzJbtkypWIEPaEM6Ls9T603i0+693Pc8zmmkGkw S/UA+TZ3bmz/4DYYB0K6DpInazve5ezQP+U2yPRk3H1E4ANRdN1BlPI5rJaWDcne6cToXniqtJYh 2fh2wfAJPI99az2gqFCIIfZ1+KzU7SnoXsKZpOw3kjuyuSvKs/5y4DRr6BqkBN5K/ZaGzcj+Mc5i sodBI9N2YC5aCjuBDzdB/sn9JnExigkZNff1J0V4Egf9XlsVRD9nnuidX4Ts4xdG+C13IyWeZaIl DDbinOYv+dgmyYeTplF5Ok+wpVgQQ4AiySdYEKpij7z3PcmmgasjPATpR0ag5O+5X3u97YLfFuPW aicDtNrQuCQdZLNhLwRrtF5z+7s1/t/VIeLbUrGXPGnBDeG6QE4Z65tSUaCtARdCSMvuUPrCdRPo XeQjpnGJFvzCF+fyxK6y/983ZobKoSO5qw+kw4o= X-Report-Abuse-To: spam@antispammaster.host-h.net X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_04C5_01D2A764.B841BD90 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Good day At my company we're busy converting a product from using SQL Server to Postgres. One part of the old design involves filtering data for the rights a user has. The SQL Server table looked like this: CREATE TABLE [dbo].[usrUserRights] ( [UserId] [dbo].[dm_Id] NOT NULL, [SiteId] [dbo].[dm_Id] NOT NULL, [RightId] [dbo].[dm_Id] NOT NULL, CONSTRAINT [pk_usrUserRights_UserId_RightId_SiteId] PRIMARY KEY CLUSTERED([UserId],[RightId],[SiteId]) ); All data in other tables would have a SiteId. Users would be assigned rights for certain Sites. We would then be able to filter data with a join. Example: SELECT Id, Code FROM SomeTable st JOIN usrUserRights ur ON st.SiteId = ur.SiteId AND ur.UserId = @UserId AND ur.RightId = @TheRightRequired The one design flaw with this is that the table gets extremely large. At our largest client this table contains over 700mil records. For a single user with lots of rights there could be 7mil records to cover their rights. In Postgres I was thinking of going with a design like this CREATE TABLE security.user_right_site ( user_id bigint NOT NULL, right_id bigint NOT NULL, sites bigint[] ); create index on security.user_right_site(user_id, right_id); This drastically cut down on the number of records in the table. It also seems to make a massive change to the storage requirements. The old design requires 61GB vs 2.6GB. My one concern is regarding the limitations of the array type in Postgres. Is there a point at which one should not use it? Currently our largest client has 6000+ sites, meaning that the array would contain that many items. What would the upper feasible limit be in Postgres? Regarding queries to filter data against this table in Postgres. Any advice for the best method. I've done some testing myself, but just want to know if there are other alternatives. Attempt 1, using Any (250ms) select a.id, a.code, a.description from ara.asset a join security.user_right_site urs on urs.user_id = 1783 and urs.right_id = 10000 and a.site_id = any(urs.sites) where a.is_historical = true; QUERY PLAN ---------------------------------------------------------------------------- -------------------------------------------------- Nested Loop (cost=1000.42..22712.71 rows=4 width=47) Join Filter: (a.site_id = ANY (urs.sites)) -> Gather (cost=1000.00..22599.49 rows=4191 width=55) Workers Planned: 3 -> Parallel Seq Scan on asset a (cost=0.00..21180.39 rows=1352 width=55) Filter: is_historical -> Materialize (cost=0.42..8.45 rows=1 width=530) -> Index Scan using user_right_site_user_id_right_id_idx on user_right_site urs (cost=0.42..8.45 rows=1 width=530) Index Cond: ((user_id = 1783) AND (right_id = 10000)) (9 rows) Attempt 2, using CTE (65ms) with sites as ( select unnest(sites) AS site_id from security.user_right_site where user_id = 1783 and right_id = 10000 ) select a.id, a.code, a.description from ara.asset a join sites s on a.site_id = s.site_id where a.is_historical = true; QUERY PLAN ---------------------------------------------------------------------------- ------------------------------------------ Hash Join (cost=1012.19..22628.68 rows=41 width=47) Hash Cond: (a.site_id = s.site_id) CTE sites -> Index Scan using user_right_site_user_id_right_id_idx on user_right_site (cost=0.42..8.94 rows=100 width=8) Index Cond: ((user_id = 1783) AND (right_id = 10000)) -> Gather (cost=1000.00..22599.49 rows=4191 width=55) Workers Planned: 3 -> Parallel Seq Scan on asset a (cost=0.00..21180.39 rows=1352 width=55) Filter: is_historical -> Hash (cost=2.00..2.00 rows=100 width=8) -> CTE Scan on sites s (cost=0.00..2.00 rows=100 width=8) (11 rows) Attempt 3, using sub select (65ms) select a.id, a.code, a.description from (select unnest(sites) AS site_id from security.user_right_site where user_id = 1783 and right_id = 10000) sites join ara.asset a on sites.site_id = a.site_id where a.is_historical = true; QUERY PLAN ---------------------------------------------------------------------------- ---------------------------------------------------- Gather (cost=1011.19..22209.86 rows=128 width=47) Workers Planned: 3 -> Hash Join (cost=11.19..21197.06 rows=41 width=47) Hash Cond: (a.site_id = (unnest(user_right_site.sites))) -> Parallel Seq Scan on asset a (cost=0.00..21180.39 rows=1352 width=55) Filter: is_historical -> Hash (cost=9.94..9.94 rows=100 width=8) -> Index Scan using user_right_site_user_id_right_id_idx on user_right_site (cost=0.42..8.94 rows=100 width=8) Index Cond: ((user_id = 1783) AND (right_id = 10000)) (9 rows) Regards Riaan Stander ------=_NextPart_000_04C5_01D2A764.B841BD90 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Good = day

 

At my = company we’re busy converting a product from using SQL Server to = Postgres. One part of the old design involves filtering data for the = rights a user has.

 

The SQL = Server table looked like this:

CREATE TABLE = [dbo].[usrUserRights]  (
     [UserId] =   [dbo].[dm_Id] NOT NULL,
     [SiteId] =   [dbo].[dm_Id] NOT NULL,
     = [RightId]  [dbo].[dm_Id] NOT NULL,
     = CONSTRAINT [pk_usrUserRights_UserId_RightId_SiteId] PRIMARY KEY = CLUSTERED([UserId],[RightId],[SiteId])

);

All data in = other tables would have a SiteId. Users would be assigned rights for = certain Sites. We would then be able to filter data with a = join.

Example:

SELECT Id, = Code FROM SomeTable st

JOIN usrUserRights ur ON st.SiteId = =3D ur.SiteId AND ur.UserId =3D @UserId AND ur.RightId =3D = @TheRightRequired

 

The one = design flaw with this is that the table gets extremely large. At our = largest client this table contains over 700mil records. For a single = user with lots of rights there could be 7mil records to cover their = rights.

 

In Postgres = I was thinking of going with a design like this

CREATE TABLE = security.user_right_site
(
    user_id bigint NOT = NULL,
    right_id bigint NOT = NULL,
    sites bigint[]
);
create index on = security.user_right_site(user_id, right_id);

 

This drastically cut down on the = number of records in the table. It also seems to make a massive change = to the storage requirements.

The old = design requires 61GB vs 2.6GB.

 

My one concern is regarding the = limitations of the array type in Postgres. Is there a point at which one = should not use it? Currently our largest client has 6000+ sites, meaning = that the array would contain that many items. What would the upper = feasible limit be in Postgres?

 

Regarding queries to filter data = against this table in Postgres. Any advice for the best method. = I’ve done some testing myself, but just want to know if there are = other alternatives.

 

Attempt 1, = using Any (250ms)

select a.id, a.code, a.description = from ara.asset a
    join security.user_right_site urs = on urs.user_id =3D 1783 and urs.right_id =3D 10000 and a.site_id =3D = any(urs.sites) 
where a.is_historical =3D = true;

           &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;          QUERY = PLAN

-------------------------------------------------------------------= -----------------------------------------------------------

= Nested Loop  (cost=3D1000.42..22712.71 rows=3D4 = width=3D47)

   Join Filter: (a.site_id = =3D ANY (urs.sites))

   ->  = Gather  (cost=3D1000.00..22599.49 rows=3D4191 = width=3D55)

         Workers Planned: = 3

         ->  = Parallel Seq Scan on asset a  (cost=3D0.00..21180.39 rows=3D1352 = width=3D55)

           &= nbsp;   Filter: is_historical

   = ->  Materialize  (cost=3D0.42..8.45 rows=3D1 = width=3D530)

         ->  Index = Scan using user_right_site_user_id_right_id_idx on user_right_site = urs  (cost=3D0.42..8.45 rows=3D1 = width=3D530)

           &= nbsp;   Index Cond: ((user_id =3D 1783) AND (right_id =3D = 10000))

(9 rows)

 

Attempt 2, using CTE = (65ms)

with sites = as
(
    select unnest(sites) AS site_id from = security.user_right_site where user_id =3D 1783 and right_id =3D = 10000
)
select a.id, a.code, a.description from ara.asset = a
join sites s on a.site_id =3D s.site_id
where a.is_historical = =3D true;


           &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;      QUERY PLAN

-------------------------------------------------------------------= ---------------------------------------------------

=

Hash Join  = (cost=3D1012.19..22628.68 rows=3D41 width=3D47)

   Hash Cond: (a.site_id = =3D s.site_id)

   CTE = sites

     ->  = Index Scan using user_right_site_user_id_right_id_idx on = user_right_site  (cost=3D0.42..8.94 rows=3D100 = width=3D8)

           Index = Cond: ((user_id =3D 1783) AND (right_id =3D = 10000))

   ->  = Gather  (cost=3D1000.00..22599.49 rows=3D4191 = width=3D55)

         Workers Planned: = 3

         ->  = Parallel Seq Scan on asset a  (cost=3D0.00..21180.39 rows=3D1352 = width=3D55)

           &= nbsp;   Filter: is_historical

   ->  Hash  = (cost=3D2.00..2.00 rows=3D100 width=3D8)

         ->  CTE = Scan on sites s  (cost=3D0.00..2.00 rows=3D100 = width=3D8)

(11 rows)

 

Attempt 3, using sub select = (65ms)

select a.id, a.code, a.description = from
(select unnest(sites) AS site_id from security.user_right_site = where user_id =3D 1783 and right_id =3D 10000) sites
join ara.asset a = on sites.site_id =3D a.site_id
where a.is_historical =3D = true;

 

           &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;           QUERY = PLAN

-------------------------------------------------------------------= -------------------------------------------------------------<= /span>

Gather  = (cost=3D1011.19..22209.86 rows=3D128 width=3D47)

   Workers Planned: = 3

   ->  Hash = Join  (cost=3D11.19..21197.06 rows=3D41 = width=3D47)

         Hash Cond: = (a.site_id =3D (unnest(user_right_site.sites)))

         ->  = Parallel Seq Scan on asset a  (cost=3D0.00..21180.39 rows=3D1352 = width=3D55)

           &= nbsp;   Filter: is_historical

         ->  = Hash  (cost=3D9.94..9.94 rows=3D100 = width=3D8)

           &= nbsp;   ->  Index Scan using = user_right_site_user_id_right_id_idx on user_right_site  = (cost=3D0.42..8.94 rows=3D100 width=3D8)

           &= nbsp;         Index Cond: = ((user_id =3D 1783) AND (right_id =3D 10000))

(9 rows)

 

 

Regards

Riaan = Stander

------=_NextPart_000_04C5_01D2A764.B841BD90--