Re: [PERFORM] Best design for performance

2017-03-28 Thread Riaan Stander
On 2017-03-28 07:15 PM, Claudio Freire wrote: On Tue, Mar 28, 2017 at 9:41 AM, Riaan Stander wrote: CREATE TABLE [dbo].[usrUserRights] ( [UserId] [dbo].[dm_Id] NOT NULL, [SiteId] [dbo].[dm_Id] NOT NULL, [RightId] [dbo].[dm_Id] NOT NULL,

Re: [PERFORM] Best design for performance

2017-03-28 Thread Claudio Freire
On Tue, Mar 28, 2017 at 9:41 AM, Riaan Stander wrote: > CREATE TABLE [dbo].[usrUserRights] ( > [UserId] [dbo].[dm_Id] NOT NULL, > [SiteId] [dbo].[dm_Id] NOT NULL, > [RightId] [dbo].[dm_Id] NOT NULL, > CONSTRAINT

Re: [PERFORM] Best design for performance

2017-03-28 Thread Riaan Stander
On 28 Mar 2017 4:22 AM, Claudio Freire wrote: From: Claudio Freire [mailto:klaussfre...@gmail.com] How did you query the table's size? You're probably failing to account for TOAST tables. I'd suggest using pg_total_relation_size. ... On Mon, Mar 27, 2017 at 10:17 PM, Riaan Stander

Re: [PERFORM] Best design for performance

2017-03-27 Thread Claudio Freire
> From: Claudio Freire [mailto:klaussfre...@gmail.com] > > How did you query the table's size? You're probably failing to account for > TOAST tables. > > I'd suggest using pg_total_relation_size. ... On Mon, Mar 27, 2017 at 10:17 PM, Riaan Stander wrote: > I'm using the first

Re: [PERFORM] Best design for performance

2017-03-27 Thread Riaan Stander
udio Freire [mailto:klaussfre...@gmail.com] Sent: 28 March 2017 02:42 AM To: Riaan Stander <rstan...@exa.co.za> Cc: postgres performance list <pgsql-performance@postgresql.org> Subject: Re: [PERFORM] Best design for performance On Mon, Mar 27, 2017 at 8:43 PM, Riaan Stander <rstan.

Re: [PERFORM] Best design for performance

2017-03-27 Thread Claudio Freire
On Mon, Mar 27, 2017 at 8:43 PM, Riaan Stander wrote: > 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