Date: Thu, 3 Jan 2019 17:47:43 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Abadie Lana <Lana.Abadie@iter.org>
Cc: David Rowley <david.rowley@2ndquadrant.com>,
	"pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: select query does not pick up the right index
Message-ID: <20190103234743.GU25379@telsasoft.com>
References: <c2d19232595246018b277ebcd59a0046@iter.org>
 <CAKJS1f_meBeEn2uLcB0MvNr5mmyk1b9jJK076-HkOZoXwA6GRw@mail.gmail.com>
 <49406fb0c239480980ffc6c2a250d4e9@iter.org>
 <CAKJS1f_h27-bR8P2sW5iAmjY9fhXs_d5DcjQ=zX0AW8WghUcZw@mail.gmail.com>
 <438dcc57f91d4a9fab12ae4078592d69@iter.org>
 <CAKJS1f9UhO+=nwsJ5rzpQk_BNfSB=u3tnPoS5AJc2A9BNz136Q@mail.gmail.com>
 <d34298e7593d40fea9fcf8ab82bb0eea@iter.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <d34298e7593d40fea9fcf8ab82bb0eea@iter.org>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

On Thu, Jan 03, 2019 at 12:57:27PM +0000, Abadie Lana wrote:
> Main parameters : effective_cache_size : 4GB, shared_buffers 4GB, work_mem 4MB

I doubt it will help much, but you should consider increasing work_mem, unless
you have many expensive queries running at once.

Could you also send the rest of the pg_statistic for that table ?

https://wiki.postgresql.org/wiki/Slow_Query_Questions#Statistics:_n_distinct.2C_MCV.2C_histogram

SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist FROM pg_stats WHERE attname='...' AND tablename='...' ORDER BY 1 DESC;