pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Abadie Lana <Lana.Abadie@iter.org>
To: David Rowley <david.rowley@2ndquadrant.com>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: RE: select query does not pick up the right index
Date: Thu, 3 Jan 2019 13:20:30 +0000
Message-ID: <d34298e7593d40fea9fcf8ab82bb0eea@iter.org> (raw )
In-Reply-To: <CAKJS1f9UhO+=nwsJ5rzpQk_BNfSB=u3tnPoS5AJc2A9BNz136Q@mail.gmail.com >
References: <c2d19232595246018b277ebcd59a0046@iter.org >
<CAKJS1f_meBeEn2uLcB0MvNr5mmyk1b9jJK076-HkOZoXwA6GRw@mail.gmail.com >
<49406fb0c239480980ffc6c2a250d4e9@iter.org >
<CAKJS1f_h27-bR8P2sW5iAmjY9fhXs_d5DcjQ=zX0AW8WghUcZw@mail.gmail.com >
<438dcc57f91d4a9fab12ae4078592d69@iter.org >
<CAKJS1f9UhO+=nwsJ5rzpQk_BNfSB=u3tnPoS5AJc2A9BNz136Q@mail.gmail.com >
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@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 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
view thread (21+ messages) latest in thread
Message-ID: <d34298e7593d40fea9fcf8ab82bb0eea@iter.org>
Permalink: ../d34298e7593d40fea9fcf8ab82bb0eea@iter.org/
Also on: postgresql.org/message-id/d34298e7593d40fea9fcf8ab82bb0eea@iter.org
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Lana.Abadie@iter.org, david.rowley@2ndquadrant.com, pgsql-performance@lists.postgresql.org
Subject: RE: select query does not pick up the right index
In-Reply-To: <d34298e7593d40fea9fcf8ab82bb0eea@iter.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox