Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1jRMsE-0002HG-4n
	for pgsql-admin@arkaria.postgresql.org; Wed, 22 Apr 2020 21:25:38 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1jRMsA-0004oH-IL
	for pgsql-admin@arkaria.postgresql.org; Wed, 22 Apr 2020 21:25:34 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <rui@crazybean.net>)
	id 1jRMsA-0004oA-Ad
	for pgsql-admin@lists.postgresql.org; Wed, 22 Apr 2020 21:25:34 +0000
Received: from impout009aa.msg.chrl.nc.charter.net ([47.43.20.33]
 helo=impout009.msg.chrl.nc.charter.net)
	by makus.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <rui@crazybean.net>)
	id 1jRMs8-0003Z1-4r
	for pgsql-admin@postgresql.org; Wed, 22 Apr 2020 21:25:33 +0000
Received: from cerberus.crazybean.net ([24.151.69.73])
	by cmsmtp with ESMTP
	id RMs6j9iAc2HYJRMs6jwS97; Wed, 22 Apr 2020 21:25:30 +0000
Authentication-Results: crazybean.net; none
X-Authority-Analysis: v=2.3 cv=AKi/Lllh c=1 sm=1 tr=0
 a=YXTuGtgWN9YX+aIhe8tYuA==:117 a=YXTuGtgWN9YX+aIhe8tYuA==:17
 a=xqWC_Br6kY4A:10 a=kj9zAlcOel0A:10 a=cl8xLZFz6L8A:10 a=pGLkceISAAAA:8
 a=DPmZMpXdMaDk_aeMxbcA:9 a=CjuIK1q_8ugA:10
Received: from [IPv6:2001:470:e117:4:4b3:9e71:5035:4f7a] (<unknown>
 [2001:470:e117:4:4b3:9e71:5035:4f7a])
	by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id a99f769c
 (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO);
	Wed, 22 Apr 2020 17:25:29 -0400 (EDT)
Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.80.23.2.2\))
Subject: Re: n00b question re: indexes and constraints
From: Rui DeSousa <rui@crazybean.net>
In-Reply-To: 
 <CAOC+FBXtsauGmMUSrg65Cay0ki8y+QTkexHU4bFFSRy5fASCKg@mail.gmail.com>
Date: Wed, 22 Apr 2020 17:25:29 -0400
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
 pgsql-admin <pgsql-admin@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <CABDA054-5DF4-4C09-9DDC-F544E3DBC043@crazybean.net>
References: 
 <CAOC+FBUcDbLb=fjwUzTyTrdfhLu81M9uGdqwfK=BFG3QU5iZuA@mail.gmail.com>
 <CAKFQuwZu9+VpqMFDYuxLs8sjTPj5wOWR+it8Pug4VPbMv7riBA@mail.gmail.com>
 <CAOC+FBXtsauGmMUSrg65Cay0ki8y+QTkexHU4bFFSRy5fASCKg@mail.gmail.com>
To: Wells Oliver <wells.oliver@gmail.com>
X-Mailer: Apple Mail (2.3608.80.23.2.2)
X-CMAE-Envelope: 
 MS4wfPBvntJOgEgG7SXRgKCtQScAD75FfqjWtTWWXDwcG+4dI/r9LyAfhBWiwk+JQObJsVpVuoKnzI8+XTwaf5VYuen/tRnjUaXCu4ogYjC/LkgortnRt8M1
 eVpK1zfVB+uEht7G2xpSi711G+6sfOlbzV+dEIQIJS0h0Q273b6wus0SofacDlF+5iQP576/Fy6DofmElWLoIGvnaQkQI5Drrjq+4VGo8/2FFm0Mywnn9vQm
 PGtsXdbXhHwQ4A2kd4crJfp11O/AM6sWVqX0SVFlPk8=
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Precedence: bulk



> On Apr 22, 2020, at 2:39 PM, Wells Oliver <wells.oliver@gmail.com> =
wrote:
>=20
> Thanks, I should have been more specific, these are unique constraints =
I am concerned about: so yeah, creating an index on a set of columns =
where there's a unique constraint is redundant, correct?
>=20

Correct. Here is a view that I created to help find duplicate indexes in =
such cases where redundant indexes where created.  =20


create or replace view duplicate_index
as
select base.indrelid::regclass as table_name
 , string_agg((dup.indexrelid::regclass)::text, E'\n') as indexes
 , pg_size_pretty(avg(pg_relation_size(dup.indexrelid))) as avg_size
from pg_index base
join pg_index dup on dup.indrelid =3D base.indrelid  -- table identifier
 and dup.indkey =3D base.indkey  --  columns indexed
 and dup.indclass =3D base.indclass  -- columns types
 and (
   dup.indexprs =3D base.indexprs -- expression predicate for columns
   or  (
     dup.indexprs is null=20
     and base.indexprs is null
   )
 )         =20
 and (
   dup.indpred =3D base.indpred  -- expression predicate for where =
clause
   or (
      dup.indpred is null
      and base.indpred is null
 	)
 )
 and dup.indexrelid !=3D base.indexrelid  --index identifier
group by base.indrelid::regclass
 , concat(base.indkey::text, base.indclass::text, base.indexprs, =
base.indpred)
order by avg_size desc
 , base.indrelid::regclass
;