select query does not pick up the right index

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)