RE: select query does not pick up the right index - Mailing list pgsql-performance

-----Original Message-----
From: Justin Pryzby <pryzby@telsasoft.com>
Sent: 04 January 2019 00:48
To: Abadie Lana <Lana.Abadie@iter.org>
Cc: David Rowley <david.rowley@2ndquadrant.com>; pgsql-performance@lists.postgresql.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,
> work_mem 4MB

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

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;  

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

css_archive_3_0_0=# 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='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 sample_util_year. Could that explain the wrong
choice? 

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='smpl_time'
ANDtablename like 'sample%' ORDER BY 1 DESC; 
  frac_mcv  |     tablename     |  attname  | null_frac | n_distinct  | n_mcv | 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 |   100 |    101
  0.0154523 | sample_buil       | smpl_time |         0 |      854250 |   100 |    101
     0.0115 | sample_util       | smpl_time |         0 |      405269 |   100 |    101
  0.0112333 | sample_util       | smpl_time |         0 |      537030 |   100 |    101
  0.0106667 | sample_util_month | smpl_time |         0 |      539001 |   100 |    101
 0.00946667 | sample_buil       | smpl_time |         0 |   -0.328554 |   100 |    101
 0.00852342 | sample            | smpl_time |         0 |  1.5125e+07 |  1000 |   1001
 0.00780001 | sample_util_year  | smpl_time |         0 | 1.73199e+06 |   100 |    101
(13 rows)