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: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
Sent: 03 January 2019 14:01
To: Abadie Lana <Lana(dot)Abadie(at)iter(dot)org>
Cc: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Re: select query does not pick up the right index

On Fri, 4 Jan 2019 at 01:57, Abadie Lana <Lana(dot)Abadie(at)iter(dot)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)