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: David Rowley <david.rowley@xxxxxxxxxxxxxxx> Sent: 03 January 2019 14:18 To: Abadie Lana <Lana.Abadie@xxxxxxxx> Cc: pgsql-performance@xxxxxxxxxxxxxxxxxxxx Subject: Re: select query does not pick up the right index > From: David Rowley <david.rowley@xxxxxxxxxxxxxxx> > 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 zone AND 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 zone AND smpl_time <= now()) Inherits: sample_buil css_archive_3_0_0=# -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
