pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Abadie Lana <Lana.Abadie@iter.org>
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: select query does not pick up the right index
Date: Wed, 2 Jan 2019 16:28:41 +0000
Message-ID: <c2d19232595246018b277ebcd59a0046@iter.org> (raw )
Hi all
I would appreciate any hints as this problem looks to me rather strange...I tried to google it but in vain.
select t.name, c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample c, channel t where t.channel_id=c.channel_id and t.name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW' order by c.smpl_time desc limit 5;
takes 20mn to execute because it picks up the wrong index...see explain analyse below. I would expect this query to use the (channel_id,smpl_time) but it uses the smpl_time index.
I have run analyse on the sample table. I have set default_statistics_target = 1000
When I removed this index, then the query goes down to a few seconds...
Any ideas, why the planner is not taking the right index?
Postgresql server is 10.5.1 running on RHEL 7.4
More details about the table and explain...
Thanks for your help
Lana
\d+ sample
Table "public.sample"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+-----------------------------+-----------+----------+-------------+----------+--------------+-------------
channel_id | bigint | | not null | | plain | |
smpl_time | timestamp without time zone | | not null | | plain | |
nanosecs | bigint | | not null | | plain | |
severity_id | bigint | | not null | | plain | |
status_id | bigint | | not null | | plain | |
num_val | integer | | | | plain | |
float_val | double precision | | | | plain | |
str_val | character varying(120) | | | | extended | |
datatype | character(1) | | | ' '::bpchar | extended | |
array_val | bytea | | | | extended | |
Indexes:
"sample_time_1_idx" btree (channel_id, smpl_time)
"sample_time_all_idx" btree (smpl_time, channel_id)
"smpl_time_qa_idx" btree (smpl_time)
Child tables: sample_buil,
sample_ctrl,
sample_util
\d+ sample_buil
Table "public.sample_buil"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+-----------------------------+-----------+----------+-------------+----------+--------------+-------------
channel_id | bigint | | not null | | plain | |
smpl_time | timestamp without time zone | | not null | | plain | |
nanosecs | bigint | | not null | | plain | |
severity_id | bigint | | not null | | plain | |
status_id | bigint | | not null | | plain | |
num_val | integer | | | | plain | |
float_val | double precision | | | | plain | |
str_val | character varying(120) | | | | extended | |
datatype | character(1) | | | ' '::bpchar | extended | |
array_val | bytea | | | | extended | |
Indexes:
"sample_time_b1_idx" btree (smpl_time, channel_id)
"sample_time_b_idx" btree (channel_id, smpl_time)
"smpl_time_bx0_idx" btree (smpl_time)
Inherits: sample
Child tables: sample_buil_month,
sample_buil_year
\d+ sample_buil_month
Table "public.sample_buil_month"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+-----------------------------+-----------+----------+-------------+----------+--------------+-------------
channel_id | bigint | | not null | | plain | |
smpl_time | timestamp without time zone | | not null | | plain | |
nanosecs | bigint | | not null | | plain | |
severity_id | bigint | | not null | | plain | |
status_id | bigint | | not null | | plain | |
num_val | integer | | | | plain | |
float_val | double precision | | | | plain | |
str_val | character varying(120) | | | | extended | |
datatype | character(1) | | | ' '::bpchar | extended | |
array_val | bytea | | | | extended | |
Indexes:
"sample_time_bm_idx" btree (channel_id, smpl_time)
"sample_time_mb1_idx" btree (smpl_time, channel_id)
"smpl_time_bx1_idx" btree (smpl_time)
Check constraints:
"sample_buil_month_smpl_time_check" CHECK (smpl_time >= (now() - '32 days'::interval)::timestamp without time zone AND smpl_time <= now())
Inherits: sample_buil
css_archive_3_0_0=# explain analyze select t.name, c.smpl_time,c.nanosecs,c.float_val,c.num_ val,c.str_val,c.datatype,c.array_val from sample c, channel t where t.channel_id=c.channel_i d and t.name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW' order by c.smpl_time desc limit 5;
QUERY PLAN
--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
-------------
Gather (cost=1004.71..125606.08 rows=5 width=150) (actual time=38737.443..1220277.244 rows
=3 loops=1)
Workers Planned: 1
Workers Launched: 1
Single Copy: true
-> Limit (cost=4.71..124605.58 rows=5 width=150) (actual time=38731.488..1220117.046 ro
ws=3 loops=1)
-> Nested Loop (cost=4.71..240130785.25 rows=9636 width=150) (actual time=38731.4
86..1220117.040 rows=3 loops=1)
Join Filter: (c.channel_id = t.channel_id)
Rows Removed by Join Filter: 322099471
-> Merge Append (cost=4.71..235298377.47 rows=322099464 width=125) (actual
time=0.681..943623.198 rows=322099474 loops=1)
Sort Key: c.smpl_time DESC
-> Index Scan Backward using smpl_time_qa_idx on sample c (cost=0.12.
.8.14 rows=1 width=334) (actual time=0.010..0.010 rows=0 loops=1)
-> Index Scan Backward using smpl_time_bx0_idx on sample_buil c_1 (co
st=0.42..3543026.23 rows=1033169 width=328) (actual time=0.122..723.286 rows=1033169 loops=1
)
-> Index Scan Backward using smpl_time_cmx0_idx on sample_ctrl c_2 (c
ost=0.42..2891856.90 rows=942520 width=328) (actual time=0.069..712.386 rows=942520 loops=1)
-> Index Scan Backward using smpl_time_ux0_idx on sample_util c_3 (co
st=0.43..11310958.12 rows=5282177 width=328) (actual time=0.066..3688.980 rows=5282177 loops
=1)
-> Index Scan Backward using smpl_time_bx1_idx on sample_buil_month c_
4 (cost=0.43..49358435.15 rows=14768705 width=82) (actual time=0.070..9341.396 rows=1476870
5 loops=1)
-> Index Scan Backward using smpl_time_bx2_idx on sample_buil_year c_5
(cost=0.56..1897430.89 rows=50597832 width=328) (actual time=0.068..139840.439 rows=505978
34 loops=1)
-> Index Scan Backward using smpl_time_cmx1_idx on sample_ctrl_month c
_6 (cost=0.44..55253292.21 rows=18277124 width=85) (actual time=0.061..14610.389 rows=18277
123 loops=1)
-> Index Scan Backward using smpl_time_cmx2_idx on sample_ctrl_year c_
7 (cost=0.57..2987358.31 rows=79579072 width=76) (actual time=0.067..286316.865 rows=795790
75 loops=1)
-> Index Scan Backward using smpl_time_ux1_idx on sample_util_month c_
8 (cost=0.57..98830163.45 rows=70980976 width=82) (actual time=0.071..60766.643 rows=709809
80 loops=1)
-> Index Scan Backward using smpl_time_ux2_idx on sample_util_year c_9
(cost=0.57..3070642.94 rows=80637888 width=83) (actual time=0.069..307091.673 rows=8063789
1 loops=1)
-> Materialize (cost=0.00..915.83 rows=1 width=41) (actual time=0.000..0.00
0 rows=1 loops=322099474)
-> Seq Scan on channel t (cost=0.00..915.83 rows=1 width=41) (actual
time=4.683..7.885 rows=1 loops=1)
Filter: ((name)::text = 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW'::text)
Rows Removed by Filter: 33425
Planning time: 31.392 ms
Execution time: 1220277.424 ms
(26 rows)
view thread (21+ messages) latest in thread
Message-ID: <c2d19232595246018b277ebcd59a0046@iter.org>
Permalink: ../c2d19232595246018b277ebcd59a0046@iter.org/
Also on: postgresql.org/message-id/c2d19232595246018b277ebcd59a0046@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, pgsql-performance@lists.postgresql.org
Subject: Re: select query does not pick up the right index
In-Reply-To: <c2d19232595246018b277ebcd59a0046@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