pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Justin Pryzby <pryzby@telsasoft.com>
To: Abadie Lana <Lana.Abadie@iter.org>
Cc: David Rowley <david.rowley@2ndquadrant.com>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: select query does not pick up the right index
Date: Thu, 3 Jan 2019 17:47:43 -0600
Message-ID: <20190103234743.GU25379@telsasoft.com> (raw)
In-Reply-To: <d34298e7593d40fea9fcf8ab82bb0eea@iter.org>
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>

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; 




view thread (21+ messages)  latest in thread

Message-ID: <20190103234743.GU25379@telsasoft.com>
Permalink:  ../20190103234743.GU25379@telsasoft.com/
Also on:    postgresql.org/message-id/20190103234743.GU25379@telsasoft.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: pryzby@telsasoft.com, Lana.Abadie@iter.org, david.rowley@2ndquadrant.com, pgsql-performance@lists.postgresql.org
  Subject: Re: select query does not pick up the right index
  In-Reply-To: <20190103234743.GU25379@telsasoft.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox