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.rowley@2ndquadrant.com> 
Sent: 03 January 2019 14:01
To: Abadie Lana <Lana.Abadie@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.Abadie@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_valfrom
samplec WHERE c.channel_id = (SELECT channel_id FROM channel WHERE name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by
c.smpl_timedesc 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=15711wi
 
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=97101w
 
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=160954w
 
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)