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-sql-owner+archive@lists.postgresql.org>)
	id 1kdAYT-0004dU-Bb
	for pgsql-sql@arkaria.postgresql.org; Thu, 12 Nov 2020 11:14:17 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-sql-owner+archive@lists.postgresql.org>)
	id 1kdAYS-0006YX-5W
	for pgsql-sql@arkaria.postgresql.org; Thu, 12 Nov 2020 11:14:16 +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 <depesz@depesz.com>)
	id 1kdAYR-0006YQ-T8
	for pgsql-sql@lists.postgresql.org; Thu, 12 Nov 2020 11:14:15 +0000
Received: from lana.depesz.com ([88.198.49.178] helo=depesz.com)
	by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <depesz@depesz.com>)
	id 1kdAYN-0005RM-7R
	for pgsql-sql@lists.postgresql.org; Thu, 12 Nov 2020 11:14:14 +0000
DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=depesz.com;
	 s=20170201; h=In-Reply-To:Content-Type:MIME-Version:References:Reply-To:
	Message-ID:Subject:Cc:To:Sender:From:Date:Content-Transfer-Encoding:
	Content-ID:Content-Description:Resent-Date:Resent-From:Resent-Sender:
	Resent-To:Resent-Cc:Resent-Message-ID:List-Id:List-Help:List-Unsubscribe:
	List-Subscribe:List-Post:List-Owner:List-Archive;
	bh=u4XDpICZEZMb7TzJ3C4YJp/MQ8wh4bhmf50BzxUYLfg=; b=gplYBhrppOlvO/BGH67HAYQjCV
	PqzCx4LOlmq8UdGdFdgEsePMlJlA78lLZ7O8iBK1v+hKUxkMrQs+Nu8zc7M429wOHnesqkSR7o1FG
	gOMMr87AvWpJ8X5btxFD1pQuiSw1pjlOReDEEKXGNsbcZqfLw7je5TnS6LKkubgNzFNM=;
Received: from lana.depesz.com ([88.198.49.178] helo=depesz.com)
	by depesz.com with esmtpa (Exim 4.92)
	(envelope-from <depesz@depesz.com>)
	id 1kdAYK-0000NT-79; Thu, 12 Nov 2020 12:14:08 +0100
Date: Thu, 12 Nov 2020 12:14:08 +0100
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: depesz@depesz.com
To: Yambu <hyambu@gmail.com>
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: Identify columns that need indexes
Message-ID: <20201112111408.GB4026@depesz.com>
Reply-To: depesz@depesz.com
References: 
 <CALhHtcCmDSYf6_+JYF1uqojj0nQbqwYNCm0u5++7jcWnyCFfwg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: 
 <CALhHtcCmDSYf6_+JYF1uqojj0nQbqwYNCm0u5++7jcWnyCFfwg@mail.gmail.com>
User-Agent: Mutt/1.10.1 (2018-07-13)
List-Id: <pgsql-sql.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-sql@lists.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-sql>
Precedence: bulk

On Thu, Nov 12, 2020 at 09:50:03AM +0200, Yambu wrote:
> May I know if there is a query I can run to identify the columns that need
> indexes. I know I can get tables that are having high sequential scans
> ,what I need is to identify are the columns.

Not really, as this would be in AI territory.
There can be queries that will find, for example, fkeys that are not
indexed. Or some other subset of problems.
But general "query that will tell me where to put index" - no.

The usual solution is to analyze logs, find queries that use most time,
and optimize them.

depesz