RE: select query does not pick up the right index

Lana ABADIE
Database Engineer
CODAC Section

ITER Organization, Building 72/4108, SCOD, Control System Division
Route de Vinon-sur-Verdon - CS 90 046 - 13067 St Paul Lez Durance Cedex - France
Phone: +33 4 42 17 84 02
Get the latest ITER news on http://www.iter.org/whatsnew

-----Original Message-----
From: pgsql-performance-owner+M22888-112298@lists.postgresql.org
<pgsql-performance-owner+M22888-112298@lists.postgresql.org>On Behalf Of Abadie Lana
 
Sent: 03 January 2019 14:21
To: David Rowley <david.rowley@2ndquadrant.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: [Possible Spoof] RE: select query does not pick up the right index

Warning: This message was sent by pgsql-performance-owner+M22888-112298@lists.postgresql.org supposedly on behalf of
AbadieLana <Lana.Abadie@iter.org>. Please contact
 




Lana ABADIE
Database Engineer
CODAC Section

ITER Organization, Building 72/4108, SCOD, Control System Division Route de Vinon-sur-Verdon - CS 90 046 - 13067 St
PaulLez Durance Cedex - France
 
Phone: +33 4 42 17 84 02
Get the latest ITER news on http://www.iter.org/whatsnew

-----Original Message-----
From: David Rowley <david.rowley@2ndquadrant.com>
Sent: 03 January 2019 14:18
To: Abadie Lana <Lana.Abadie@iter.org>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: select query does not pick up the right index

> 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_val from sample c WHERE 
> c.channel_id = (SELECT channel_id FROM channel WHERE
> name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc 
> 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.
 


These indexes exist already
\d sample_ctrl_year
                        Table "public.sample_ctrl_year"
   Column    |            Type             | Collation | Nullable |   Default
-------------+-----------------------------+-----------+----------+-----
-------------+-----------------------------+-----------+----------+-----
-------------+-----------------------------+-----------+----------+---
 channel_id  | bigint                      |           | not null |
 smpl_time   | timestamp without time zone |           | not null |
 nanosecs    | bigint                      |           | not null |
 severity_id | bigint                      |           | not null |
 status_id   | bigint                      |           | not null |
 num_val     | integer                     |           |          |
 float_val   | double precision            |           |          |
 str_val     | character varying(120)      |           |          |
 datatype    | character(1)                |           |          | ' '::bpchar
 array_val   | bytea                       |           |          |
Indexes:
    "sample_time_cy_idx" btree (channel_id, smpl_time)
    "sample_time_yc1_idx" btree (smpl_time, channel_id)
    "smpl_time_cmx2_idx" btree (smpl_time) Check constraints:
    "sample_ctrl_year_smpl_time_check" CHECK (smpl_time >= (now() - '1 year 1 mon'::interval)::timestamp without time
zoneAND smpl_time <= now())
 
Inherits: sample_ctrl

css_archive_3_0_0=# \d sample_buil_year
                        Table "public.sample_buil_year"
   Column    |            Type             | Collation | Nullable |   Default
-------------+-----------------------------+-----------+----------+-----
-------------+-----------------------------+-----------+----------+-----
-------------+-----------------------------+-----------+----------+---
 channel_id  | bigint                      |           | not null |
 smpl_time   | timestamp without time zone |           | not null |
 nanosecs    | bigint                      |           | not null |
 severity_id | bigint                      |           | not null |
 status_id   | bigint                      |           | not null |
 num_val     | integer                     |           |          |
 float_val   | double precision            |           |          |
 str_val     | character varying(120)      |           |          |
 datatype    | character(1)                |           |          | ' '::bpchar
 array_val   | bytea                       |           |          |
Indexes:
    "sample_time_by_idx" btree (channel_id, smpl_time)
    "sample_time_yb1_idx" btree (smpl_time, channel_id)
    "smpl_time_bx2_idx" btree (smpl_time) Check constraints:
    "sample_buil_year_smpl_time_check" CHECK (smpl_time >= (now() - '1 year 1 mon'::interval)::timestamp without time
zoneAND smpl_time <= now())
 
Inherits: sample_buil

css_archive_3_0_0=#

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

In case I'm also posting the explain analyse of the other query
explain (analyze,buffers) select t.name, c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val
fromsample 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+INTERVAL '0 sec' desc limit 5;

                                                                           QUERY PLAN


----------------------------------------------------------------------------------------------------------------------------
-------------------------------------
 Limit  (cost=2746650.57..2746650.59 rows=5 width=158) (actual time=119.927..119.929 rows=3 loops=1)
   Buffers: shared hit=3 read=531
   ->  Sort  (cost=2746650.57..2746674.66 rows=9636 width=158) (actual time=119.925..119.926 rows=3 loops=1)
         Sort Key: ((c.smpl_time + '00:00:00'::interval)) DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=3 read=531
         ->  Nested Loop  (cost=0.00..2746490.52 rows=9636 width=158) (actual time=46.946..119.897 rows=3 loops=1)
               Buffers: shared hit=3 read=531
               ->  Seq Scan on channel t  (cost=0.00..915.83 rows=1 width=41) (actual time=16.217..18.257 rows=1
loops=1)
                     Filter: ((name)::text = 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW'::text)
                     Rows Removed by Filter: 33425
                     Buffers: shared hit=1 read=497
               ->  Append  (cost=0.00..2684377.38 rows=6117323 width=125) (actual time=30.717..101.624 rows=3 loops=1)
                     Buffers: shared hit=2 read=34
                     ->  Seq Scan on sample c  (cost=0.00..0.00 rows=1 width=334) (actual time=0.002..0.002 rows=0
loops=1)
                           Filter: (t.channel_id = channel_id)
                     ->  Bitmap Heap Scan on sample_buil c_1  (cost=149.25..10404.32 rows=6300 width=328) (actual
time=9.241
..9.242 rows=0 loops=1)
                           Recheck Cond: (channel_id = t.channel_id)
                           Buffers: shared read=3
                           ->  Bitmap Index Scan on sample_time_b_idx  (cost=0.00..147.68 rows=6300 width=0) (actual
time=9.
237..9.237 rows=0 loops=1)
                                 Index Cond: (channel_id = t.channel_id)
                                 Buffers: shared read=3
                     ->  Bitmap Heap Scan on sample_ctrl c_2  (cost=781.30..11912.06 rows=33661 width=328) (actual
time=0.02
0..0.020 rows=0 loops=1)
                           Recheck Cond: (channel_id = t.channel_id)
                           Buffers: shared read=3
                           ->  Bitmap Index Scan on sample_time_c_idx  (cost=0.00..772.88 rows=33661 width=0) (actual
time=0
.018..0.018 rows=0 loops=1)
                                 Index Cond: (channel_id = t.channel_id)
                                 Buffers: shared read=3
                     ->  Bitmap Heap Scan on sample_util c_3  (cost=221.93..25401.37 rows=9483 width=328) (actual
time=7.888
..7.888 rows=0 loops=1)
                           Recheck Cond: (channel_id = t.channel_id)
                           Buffers: shared read=3
                           ->  Bitmap Index Scan on sample_time_u_idx  (cost=0.00..219.56 rows=9483 width=0) (actual
time=7.
886..7.886 rows=0 loops=1)
                                 Index Cond: (channel_id = t.channel_id)
                                 Buffers: shared read=3
                     ->  Bitmap Heap Scan on sample_buil_month c_4  (cost=366.32..47118.08 rows=15711 width=82) (actual
time
=13.556..24.870 rows=3 loops=1)
                           Recheck Cond: (channel_id = t.channel_id)
                           Heap Blocks: exact=3
                           Buffers: shared read=7
                           ->  Bitmap Index Scan on sample_time_bm_idx  (cost=0.00..362.39 rows=15711 width=0) (actual
time=
6.712..6.712 rows=3 loops=1)
                                 Index Cond: (channel_id = t.channel_id)
                                 Buffers: shared read=4
                     ->  Bitmap Heap Scan on sample_buil_year c_5  (cost=73216.89..687718.44 rows=3162364 width=328)
(actual
 time=18.015..18.015 rows=0 loops=1)
                           Recheck Cond: (channel_id = t.channel_id)
                           Buffers: shared read=4
                           ->  Bitmap Index Scan on sample_time_by_idx  (cost=0.00..72426.29 rows=3162364 width=0)
(actualt
 
ime=18.011..18.011 rows=0 loops=1)
                                 Index Cond: (channel_id = t.channel_id)
                                 Buffers: shared read=4
                     ->  Bitmap Heap Scan on sample_ctrl_month c_6  (cost=12446.67..226848.19 rows=537562 width=85)
(actual
time=0.029..0.029 rows=0 loops=1)
                           Recheck Cond: (channel_id = t.channel_id)
                           Buffers: shared read=4
                           ->  Bitmap Index Scan on sample_time_cm_idx  (cost=0.00..12312.28 rows=537562 width=0)
(actualti
 
me=0.026..0.026 rows=0 loops=1)
                                 Index Cond: (channel_id = t.channel_id)
                                 Buffers: shared read=4
                     ->  Bitmap Heap Scan on sample_ctrl_year c_7  (cost=48486.51..978945.83 rows=2094186 width=76)
(actual
time=23.088..23.088 rows=0 loops=1)
                           Recheck Cond: (channel_id = t.channel_id)
                           Buffers: shared read=4
                           ->  Bitmap Index Scan on sample_time_cy_idx  (cost=0.00..47962.96 rows=2094186 width=0)
(actualt
 
ime=23.086..23.086 rows=0 loops=1)
                                 Index Cond: (channel_id = t.channel_id)
                                 Buffers: shared read=4
                     ->  Bitmap Heap Scan on sample_util_month c_8  (cost=2249.10..277115.63 rows=97101 width=82)
(actualti
 
me=7.623..7.623 rows=0 loops=1)
                           Recheck Cond: (channel_id = t.channel_id)
                           Buffers: shared hit=1 read=3
                           ->  Bitmap Index Scan on sample_time_um_idx  (cost=0.00..2224.82 rows=97101 width=0) (actual
time
=7.619..7.619 rows=0 loops=1)
                                 Index Cond: (channel_id = t.channel_id)
                                 Buffers: shared hit=1 read=3
                     ->  Bitmap Heap Scan on sample_util_year c_9  (cost=3727.96..418913.45 rows=160954 width=83)
(actualti
 
me=10.815..10.815 rows=0 loops=1)
                           Recheck Cond: (channel_id = t.channel_id)
                           Buffers: shared hit=1 read=3
                           ->  Bitmap Index Scan on sample_time_uy_idx  (cost=0.00..3687.72 rows=160954 width=0)
(actualtim
 
e=10.811..10.811 rows=0 loops=1)
                                 Index Cond: (channel_id = t.channel_id)
                                 Buffers: shared hit=1 read=3
 Planning time: 15.656 ms
 Execution time: 120.062 ms
(73 rows)

css_archive_3_0_0=#