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

> From: David Rowley <david.rowley@2ndquadrant.com>
> Sent: 03 January 2019 14:01
> That's surprisingly slow. Can you share the EXPLAIN (ANALYZE, BUFFERS) of that?
>
> explain (analyze,buffers) select
'BUIL-B36-VA-RT-RT1:CL0001-2-ABW',c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_valfrom
samplec WHERE c.channel_id = (SELECT channel_id FROM channel WHERE name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by
c.smpl_timedesc limit 5; 


>                ->  Index Scan Backward using smpl_time_bx2_idx on sample_buil_year c_5  (cost=0.56..2023925.30
rows=3162364
> width=320) (actual time=15167.330..15167.330 rows=0 loops=1)
>                      Filter: (channel_id = $0)
>                      Rows Removed by Filter: 50597834
>                      Buffers: shared hit=25913147 read=713221
>                ->  Index Scan Backward using sample_time_cm_idx on sample_ctrl_month c_6  (cost=0.56..1862587.12
rows=537562
>  width=77) (actual time=0.048..0.048 rows=0 loops=1)
>                      Index Cond: (channel_id = $0)
>                      Buffers: shared read=4
>                ->  Index Scan Backward using smpl_time_cmx2_idx on sample_ctrl_year c_7  (cost=0.57..3186305.67
rows=2094186
>  width=68) (actual time=25847.549..25847.549 rows=0 loops=1)
>                      Filter: (channel_id = $0)
>                      Rows Removed by Filter: 79579075
>                      Buffers: shared hit=49868991 read=1121715

Right, so you need to check your indexes on sample_ctrl_year and
sample_buil_year. You need an index on (channel_id, smpl_time) on
those.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services