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)
