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.row...@2ndquadrant.com>
Sent: 03 January 2019 14:01
To: Abadie Lana <lana.aba...@iter.org>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: select query does not pick up the right index
On Fri, 4 Jan 2019 at 01:57, Abadie Lana <lana.aba...@iter.org> wrote:
> 4) name is unique, constraint and index created. Right index is picked up and
> query time is rather constant there 40sec.
That's surprisingly slow. Can you share the EXPLAIN (ANALYZE, BUFFERS) of that?
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
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;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------
Limit (cost=13.40..20.22 rows=5 width=233) (actual time=41023.057..41027.412
rows=3 loops=1)
Buffers: shared hit=75782139 read=1834969
InitPlan 1 (returns $0)
-> Index Scan using unique_chname on channel (cost=0.41..8.43 rows=1
width=8) (actual time=2.442..2.443 rows=1 loops=
1)
Index Cond: ((name)::text = 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW'::text)
Buffers: shared read=4
-> Result (cost=4.96..8344478.65 rows=6117323 width=233) (actual
time=41023.055..41027.408 rows=3 loops=1)
Buffers: shared hit=75782139 read=1834969
-> Merge Append (cost=4.96..8283305.42 rows=6117323 width=201)
(actual time=41023.054..41027.404 rows=3 loops=1)
Sort Key: c.smpl_time DESC
Buffers: shared hit=75782139 read=1834969
-> Index Scan Backward using smpl_time_qa_idx on sample c
(cost=0.12..8.14 rows=1 width=326) (actual time=0
.008..0.009 rows=0 loops=1)
Filter: (channel_id = $0)
Buffers: shared hit=1
-> Index Scan Backward using sample_time_b_idx on sample_buil
c_1 (cost=0.42..22318.03 rows=6300 width=320)
(actual time=2.478..2.478 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared read=7
-> Index Scan Backward using sample_time_c_idx on sample_ctrl
c_2 (cost=0.42..116482.81 rows=33661 width=32
0) (actual time=0.022..0.022 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared read=3
-> Index Scan Backward using sample_time_u_idx on sample_util
c_3 (cost=0.43..35366.72 rows=9483 width=320)
(actual time=0.022..0.022 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared read=3
-> Index Scan Backward using sample_time_bm_idx on
sample_buil_month c_4 (cost=0.56..60293.88 rows=15711 wi
dth=74) (actual time=5.499..9.847 rows=3 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared read=8
-> 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
-> Index Scan Backward using sample_time_um_idx on
sample_util_month c_8 (cost=0.57..360454.53 rows=97101 w
idth=74) (actual time=0.058..0.059 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared read=4
-> Index Scan Backward using sample_time_uy_idx on
sample_util_year c_9 (cost=0.57..498663.22 rows=160954 w
idth=75) (actual time=0.030..0.030 rows=0 loops=1)
Index Cond: (channel_id = $0)
Buffers: shared read=4
Planning time: 0.782 ms
Execution time: 41027.570 ms
(45 rows)
