pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Abadie Lana <Lana.Abadie@iter.org>
To: Justin Pryzby <pryzby@telsasoft.com>
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: Fri, 4 Jan 2019 08:17:46 +0000
Message-ID: <7e1a311617e441d2a40dc97fa9766753@iter.org> (raw )
In-Reply-To: <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 >
<20190103234743.GU25379@telsasoft.com >
-----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 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;
Hmm. Is it normal that the couple (tablename,attname ) is not unique? I'm surprised to see sample_{ctrl,util,buil} quoted twice
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' AND tablename 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)
view thread (21+ messages) latest in thread
Message-ID: <7e1a311617e441d2a40dc97fa9766753@iter.org>
Permalink: ../7e1a311617e441d2a40dc97fa9766753@iter.org/
Also on: postgresql.org/message-id/7e1a311617e441d2a40dc97fa9766753@iter.org
copy link · copy postgr.es
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: Lana.Abadie@iter.org, pryzby@telsasoft.com, david.rowley@2ndquadrant.com, pgsql-performance@lists.postgresql.org
Subject: RE: select query does not pick up the right index
In-Reply-To: <7e1a311617e441d2a40dc97fa9766753@iter.org>
* 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