From: Abadie Lana <Lana.Abadie@iter.org>
To: Justin Pryzby <pryzby@telsasoft.com>
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
Thread-Topic: select query does not pick up the right index
Thread-Index: 
 AdSitwt5/9TyB7hoTX++E94tUlkiMgAOgIkAABh1BzAAAkhrgAACgegQ///wggD//+6z4IAAwWGA//9iMjA=
Date: Fri, 4 Jan 2019 08:17:46 +0000
Message-ID: <7e1a311617e441d2a40dc97fa9766753@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>
 <20190103234743.GU25379@telsasoft.com>
In-Reply-To: <20190103234743.GU25379@telsasoft.com>
Accept-Language: en-GB, en-US
Content-Language: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Precedence: bulk


-----Original Message-----
From: Justin Pryzby <pryzby@telsasoft.com>=20
Sent: 04 January 2019 00:48
To: Abadie Lana <Lana.Abadie@iter.org>
Cc: David Rowley <david.rowley@2ndquadrant.com>; pgsql-performance@lists.po=
stgresql.org
Subject: Re: select query does not pick up the right index

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

I doubt it will help much, but you should consider increasing work_mem, unl=
ess 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_distinc=
t.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, a=
rray_length(histogram_bounds,1) n_hist FROM pg_stats WHERE attname=3D'...' =
AND tablename=3D'...' ORDER BY 1 DESC;=20

Hmm. Is it normal that the couple (tablename,attname ) is not unique? I'm s=
urprised to see sample_{ctrl,util,buil} quoted twice

css_archive_3_0_0=3D# SELECT (SELECT sum(x) FROM unnest(most_common_freqs) =
x) frac_MCV, tablename, attname, null_frac, n_distinct, array_length(most_c=
ommon_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist FROM pg_stats =
WHERE attname=3D'channel_id' AND tablename like 'sample%' ORDER BY 1 DESC;
 frac_mcv |     tablename     |  attname   | null_frac | n_distinct | n_mcv=
 | n_hist
----------+-------------------+------------+-----------+------------+------=
-+--------
        1 | sample_buil_year  | channel_id |         0 |         16 |    16=
 |
  0.98249 | sample_ctrl       | channel_id |         0 |         26 |    17=
 |      9
 0.982333 | sample_ctrl_month | channel_id |         0 |         34 |    17=
 |     17
 0.981533 | sample_ctrl       | channel_id |         0 |         28 |    18=
 |     10
   0.9371 | sample_ctrl_year  | channel_id |         0 |         38 |    16=
 |     22
 0.928767 | sample_buil_month | channel_id |         0 |        940 |    54=
 |    101
  0.92535 | sample            | channel_id |         0 |       2144 |   167=
 |   1001
 0.907501 | sample_buil       | channel_id |         0 |        565 |    43=
 |    101
   0.8876 | sample_util_year  | channel_id |         0 |        501 |    45=
 |    101
    0.815 | sample_util       | channel_id |         0 |        557 |    82=
 |    101
 0.807667 | sample_buil       | channel_id |         0 |        164 |    31=
 |    101
 0.806267 | sample_util       | channel_id |         0 |        732 |   100=
 |    101
 0.803766 | sample_util_month | channel_id |         0 |        731 |   100=
 |    101
(13 rows)

Ah...sample_ctrl_year and sample_buil_year have n_distinct -1? Unlike sampl=
e_util_year. Could that explain the wrong choice?=20

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, a=
rray_length(histogram_bounds,1) n_hist FROM pg_stats WHERE attname=3D'smpl_=
time' AND tablename like 'sample%' ORDER BY 1 DESC;
  frac_mcv  |     tablename     |  attname  | null_frac | n_distinct  | n_m=
cv | n_hist
------------+-------------------+-----------+-----------+-------------+----=
---+--------
            | sample_ctrl_month | smpl_time |         0 |          -1 |    =
   |    101
            | sample_ctrl_year  | smpl_time |         0 |          -1 |    =
   |    101
            | sample_ctrl       | smpl_time |         0 |          -1 |    =
   |    101
            | sample_ctrl       | smpl_time |         0 |          -1 |    =
   |    101
            | sample_buil_year  | smpl_time |         0 |          -1 |    =
   |    101
  0.0154667 | sample_buil_month | smpl_time |         0 | 1.03857e+06 |   1=
00 |    101
  0.0154523 | sample_buil       | smpl_time |         0 |      854250 |   1=
00 |    101
     0.0115 | sample_util       | smpl_time |         0 |      405269 |   1=
00 |    101
  0.0112333 | sample_util       | smpl_time |         0 |      537030 |   1=
00 |    101
  0.0106667 | sample_util_month | smpl_time |         0 |      539001 |   1=
00 |    101
 0.00946667 | sample_buil       | smpl_time |         0 |   -0.328554 |   1=
00 |    101
 0.00852342 | sample            | smpl_time |         0 |  1.5125e+07 |  10=
00 |   1001
 0.00780001 | sample_util_year  | smpl_time |         0 | 1.73199e+06 |   1=
00 |    101
(13 rows)