Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gfLJq-0000sx-RO for pgsql-performance@arkaria.postgresql.org; Fri, 04 Jan 2019 08:59:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gfLJp-0000Ge-Jh for pgsql-performance@arkaria.postgresql.org; Fri, 04 Jan 2019 08:59:05 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gfLJp-0008TW-3c for pgsql-performance@lists.postgresql.org; Fri, 04 Jan 2019 08:59:05 +0000 Received: from smtp2.iter.org ([193.51.56.8]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gfLJl-00027T-5y for pgsql-performance@lists.postgresql.org; Fri, 04 Jan 2019 08:59:03 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=iter.org; l=11867; q=dns/txt; s=20170425; t=1546592341; x=1547197141; h=from:to:cc:subject:date:message-id:references: in-reply-to:content-transfer-encoding:mime-version; bh=PkEcf5X2jnKDysxf86sv08Ah20PPO8GE2SbmroCLbCk=; b=FEuJKcCWli6FudYUZCRF60GPR5yRtWqOOcw3q+oZFgtluSFLdSfdwCQB 4+DmPvpZ3PB8ZYs9ysA2sr1zh/YI6o1d6g2TObrULQ0EIMpfSTF1HaMRj MVJMUCwxW9FMfSgWBi7ViHvaPwHRamKawHoZbh7wNaQL6Elg+wdpQKTT1 Q=; Received: from XCH-CLU1N3.iter.org (10.10.242.164) by XCH-CLU1N1.iter.org (10.10.240.164) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.1261.35; Fri, 4 Jan 2019 09:58:57 +0100 Received: from XCH-CLU1N3.iter.org ([fe80::f903:4e8a:c365:4c74]) by XCH-CLU1N3.iter.org ([fe80::f903:4e8a:c365:4c74%16]) with mapi id 15.01.1261.036; Fri, 4 Jan 2019 09:58:57 +0100 From: Abadie Lana To: Abadie Lana , Justin Pryzby CC: David Rowley , "pgsql-performance@lists.postgresql.org" Subject: RE: select query does not pick up the right index Thread-Topic: select query does not pick up the right index Thread-Index: AdSitwt5/9TyB7hoTX++E94tUlkiMgAOgIkAABh1BzAAAkhrgAACgegQ///wggD//+6z4IAAwWGA//9iMjD//rrHQA== Date: Fri, 4 Jan 2019 08:58:57 +0000 Message-ID: <399cd3f79a704b21a47e4305cebfa727@iter.org> References: <49406fb0c239480980ffc6c2a250d4e9@iter.org> <438dcc57f91d4a9fab12ae4078592d69@iter.org> <20190103234743.GU25379@telsasoft.com> <7e1a311617e441d2a40dc97fa9766753@iter.org> In-Reply-To: <7e1a311617e441d2a40dc97fa9766753@iter.org> Accept-Language: en-GB, en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.14.32.57] Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk -----Original Message----- From: pgsql-performance-owner+M22888-112441@lists.postgresql.org On Behalf Of Abadie Lana Sent: 04 January 2019 09:18 To: Justin Pryzby Cc: David Rowley ; pgsql-performance@lists.po= stgresql.org Subject: [Possible Spoof] RE: select query does not pick up the right index Warning: This message was sent by pgsql-performance-owner+M22888-112441@lis= ts.postgresql.org supposedly on behalf of Abadie Lana . Please contact -----Original Message----- From: Justin Pryzby Sent: 04 January 2019 00:48 To: Abadie Lana Cc: David Rowley ; pgsql-performance@lists.po= stgresql.org Subject: Re: select query does not pick up the right index On Thu, Jan 03, 2019 at 12:57:27PM +0000, Abadie Lana wrote: > Main parameters : effective_cache_size : 4GB, shared_buffers 4GB,=20 > work_mem 4MB I doubt it will help much, but you should consider increasing work_mem, unl= ess you have many expensive queries running at once. Could you also send the rest of the pg_statistic for that table ? https://wiki.postgresql.org/wiki/Slow_Query_Questions#Statistics:_n_distinc= t.2C_MCV.2C_histogram SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename= , attname, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, a= rray_length(histogram_bounds,1) n_hist FROM pg_stats WHERE attname=3D'...' = AND tablename=3D'...' ORDER BY 1 DESC;=20 Hmm. Is it normal that the couple (tablename,attname ) is not unique? I'm s= urprised to see sample_{ctrl,util,buil} quoted twice css_archive_3_0_0=3D# SELECT (SELECT sum(x) FROM unnest(most_common_freqs) = x) frac_MCV, tablename, attname, null_frac, n_distinct, array_length(most_c= ommon_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist FROM pg_stats = WHERE attname=3D'channel_id' AND tablename like 'sample%' ORDER BY 1 DESC; frac_mcv | tablename | attname | null_frac | n_distinct | n_mcv= | n_hist ----------+-------------------+------------+-----------+------------+------= -+-------- 1 | sample_buil_year | channel_id | 0 | 16 | 16= | 0.98249 | sample_ctrl | channel_id | 0 | 26 | 17= | 9 0.982333 | sample_ctrl_month | channel_id | 0 | 34 | 17= | 17 0.981533 | sample_ctrl | channel_id | 0 | 28 | 18= | 10 0.9371 | sample_ctrl_year | channel_id | 0 | 38 | 16= | 22 0.928767 | sample_buil_month | channel_id | 0 | 940 | 54= | 101 0.92535 | sample | channel_id | 0 | 2144 | 167= | 1001 0.907501 | sample_buil | channel_id | 0 | 565 | 43= | 101 0.8876 | sample_util_year | channel_id | 0 | 501 | 45= | 101 0.815 | sample_util | channel_id | 0 | 557 | 82= | 101 0.807667 | sample_buil | channel_id | 0 | 164 | 31= | 101 0.806267 | sample_util | channel_id | 0 | 732 | 100= | 101 0.803766 | sample_util_month | channel_id | 0 | 731 | 100= | 101 (13 rows) Ah...sample_ctrl_year and sample_buil_year have n_distinct -1? Unlike sampl= e_util_year. Could that explain the wrong choice?=20 SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename= , attname, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, a= rray_length(histogram_bounds,1) n_hist FROM pg_stats WHERE attname=3D'smpl_= time' AND tablename like 'sample%' ORDER BY 1 DESC; frac_mcv | tablename | attname | null_frac | n_distinct | n_m= cv | n_hist ------------+-------------------+-----------+-----------+-------------+----= ---+-------- | sample_ctrl_month | smpl_time | 0 | -1 | = | 101 | sample_ctrl_year | smpl_time | 0 | -1 | = | 101 | sample_ctrl | smpl_time | 0 | -1 | = | 101 | sample_ctrl | smpl_time | 0 | -1 | = | 101 | sample_buil_year | smpl_time | 0 | -1 | = | 101 0.0154667 | sample_buil_month | smpl_time | 0 | 1.03857e+06 | 1= 00 | 101 0.0154523 | sample_buil | smpl_time | 0 | 854250 | 1= 00 | 101 0.0115 | sample_util | smpl_time | 0 | 405269 | 1= 00 | 101 0.0112333 | sample_util | smpl_time | 0 | 537030 | 1= 00 | 101 0.0106667 | sample_util_month | smpl_time | 0 | 539001 | 1= 00 | 101 0.00946667 | sample_buil | smpl_time | 0 | -0.328554 | 1= 00 | 101 0.00852342 | sample | smpl_time | 0 | 1.5125e+07 | 10= 00 | 1001 0.00780001 | sample_util_year | smpl_time | 0 | 1.73199e+06 | 1= 00 | 101 (13 rows) Based on your feedback...i rerun analyse directly on the two table sample_c= trl_year and sample_buil_year The new values are SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename= , attname, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, a= rray_length(histogram_bounds,1) n_hist FROM pg_stats WHERE attname=3D'chann= el_id' AND tablename like 'sample%' ORDER BY 1 DESC; frac_mcv | tablename | attname | null_frac | n_distinct | n_mcv= | n_hist ----------+-------------------+------------+-----------+------------+------= -+-------- 0.99987 | sample_buil_year | channel_id | 0 | 76 | 16= | 60 0.999632 | sample_ctrl_year | channel_id | 0 | 132 | 31= | 101 0.999628 | sample_ctrl_month | channel_id | 0 | 84 | 23= | 61 0.999627 | sample_ctrl | channel_id | 0 | 132 | 31= | 101 0.999599 | sample_ctrl | channel_id | 0 | 42 | 22= | 20 0.998074 | sample_buil | channel_id | 0 | 493 | 122= | 371 0.997693 | sample_util | channel_id | 0 | 1379 | 509= | 870 0.991841 | sample_buil | channel_id | 0 | 9867 | 107= | 9740 0.991567 | sample_util_month | channel_id | 0 | 5716 | 504= | 5209 0.990369 | sample_util_year | channel_id | 0 | 4946 | 255= | 4689 0.990062 | sample_util | channel_id | 0 | 5804 | 641= | 5160 0.972386 | sample_buil_month | channel_id | 0 | 19946 | 148= | 10001 0.967391 | sample | channel_id | 0 | 7597 | 409= | 7178 (13 rows) Now when running the query again, only for sample_buil_year table the wrong= index is picked up... 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 =3D (SELECT channel_id FROM channel WHERE name= =3D'BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc limit 5; = QUERY PLAN ---------------------------------------------------------------------------= ------------------------------------------------- ----------------------------------------------------------- Limit (cost=3D13.40..30.01 rows=3D5 width=3D112) (actual time=3D13554.536= ..13554.570 rows=3D3 loops=3D1) Buffers: shared hit=3D26626389 read=3D17 InitPlan 1 (returns $0) -> Index Scan using unique_chname on channel (cost=3D0.41..8.43 rows= =3D1 width=3D8) (actual time=3D26.858..26.860 rows=3D1 loop s=3D1) Index Cond: ((name)::text =3D 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW':= :text) Buffers: shared hit=3D2 read=3D2 -> Result (cost=3D4.96..5131208.65 rows=3D1544048 width=3D112) (actual= time=3D13554.534..13554.567 rows=3D3 loops=3D1) Buffers: shared hit=3D26626389 read=3D17 -> Merge Append (cost=3D4.96..5115768.17 rows=3D1544048 width=3D= 80) (actual time=3D13554.531..13554.562 rows=3D3 loops=3D1) Sort Key: c.smpl_time DESC Buffers: shared hit=3D26626389 read=3D17 -> Index Scan Backward using smpl_time_qa_idx on sample c = (cost=3D0.12..8.14 rows=3D1 width=3D326) (actual time=3D0 .005..0.005 rows=3D0 loops=3D1) Filter: (channel_id =3D $0) Buffers: shared hit=3D1 -> Index Scan Backward using sample_time_b_idx on sample_bu= il c_1 (cost=3D0.42..7775.26 rows=3D2096 width=3D320) (actual time=3D38.931..38.932 rows=3D0 loops=3D1) Index Cond: (channel_id =3D $0) Buffers: shared hit=3D3 read=3D4 -> Index Scan Backward using sample_time_c_idx on sample_ct= rl c_2 (cost=3D0.42..77785.57 rows=3D22441 width=3D320 ) (actual time=3D0.010..0.010 rows=3D0 loops=3D1) Index Cond: (channel_id =3D $0) Buffers: shared hit=3D3 -> Index Scan Backward using sample_time_u_idx on sample_ut= il c_3 (cost=3D0.43..14922.72 rows=3D3830 width=3D320) (actual time=3D8.939..8.939 rows=3D0 loops=3D1) Index Cond: (channel_id =3D $0) Buffers: shared hit=3D1 read=3D2 -> Index Scan Backward using sample_time_bm_idx on sample_b= uil_month c_4 (cost=3D0.56..2967.10 rows=3D740 width =3D74) (actual time=3D260.282..260.311 rows=3D3 loops=3D1) Index Cond: (channel_id =3D $0) Buffers: shared hit=3D3 read=3D5 -> Index Scan Backward using smpl_time_bx2_idx on sample_bu= il_year c_5 (cost=3D0.56..2023054.76 rows=3D665761 w idth=3D75) (actual time=3D13216.589..13216.589 rows=3D0 loops=3D1) Filter: (channel_id =3D $0) Rows Removed by Filter: 50597834 Buffers: shared hit=3D26626368 -> Index Scan Backward using sample_time_cm_idx on sample_c= trl_month c_6 (cost=3D0.56..759241.36 rows=3D217585 width=3D75) (actual time=3D0.019..0.019 rows=3D0 loops=3D1) Index Cond: (channel_id =3D $0) Buffers: shared hit=3D4 -> Index Scan Backward using sample_time_cy_idx on sample_c= trl_year c_7 (cost=3D0.57..2097812.02 rows=3D602872 width=3D76) (actual time=3D0.007..0.007 rows=3D0 loops=3D1) Index Cond: (channel_id =3D $0) Buffers: shared hit=3D4 -> Index Scan Backward using sample_time_um_idx on sample_u= til_month c_8 (cost=3D0.57..48401.65 rows=3D12418 wi dth=3D75) (actual time=3D18.999..19.000 rows=3D0 loops=3D1) Index Cond: (channel_id =3D $0) Buffers: shared hit=3D1 read=3D3 -> Index Scan Backward using sample_time_uy_idx on sample_u= til_year c_9 (cost=3D0.57..54293.22 rows=3D16304 wid th=3D74) (actual time=3D10.739..10.739 rows=3D0 loops=3D1) Index Cond: (channel_id =3D $0) Buffers: shared hit=3D1 read=3D3 Planning time: 0.741 ms Execution time: 13554.666 ms (44 rows) Looking more closely to the sample_buil_year table select count(distinct channel_id),count(*) from sample_buil_year; count | count -------+---------- 100 | 50597834 (1 row) Now, the channel name I gave has no entries in sample_buil_year...(and when= I run the query directly against sample_buil_year the right index is picke= d up).... So maybe something related with the partitioning? 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_buil_year c WHERE c= .channel_id =3D (SELECT channel_id FROM channel WHERE name=3D'BUIL-B36-VA-R= T-RT1:CL0001-2-ABW') order by c.smpl_time desc limit 5; ?column? | smpl_time | nanosecs | float_val | num_val | str_val | datatype= | array_val ----------+-----------+----------+-----------+---------+---------+---------= -+----------- (0 rows) css_archive_3_0_0=3D# explain analyze select 'BUIL-B36-VA-RT-RT1:CL0001-2-A= BW',c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.arr= ay_val from sample_buil_year c WHERE c.channel_id =3D (SELECT channel_id FR= OM channel WHERE name=3D'BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_= time desc limit 5; = QUERY PLAN ---------------------------------------------------------------------------= ------------------------------------------------- --------------------------------------- Limit (cost=3D9.00..21.31 rows=3D5 width=3D107) (actual time=3D0.055..0.0= 55 rows=3D0 loops=3D1) InitPlan 1 (returns $0) -> Index Scan using unique_chname on channel (cost=3D0.41..8.43 rows= =3D1 width=3D8) (actual time=3D0.038..0.040 rows=3D1 loops=3D 1) Index Cond: ((name)::text =3D 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW':= :text) -> Index Scan Backward using sample_time_by_idx on sample_buil_year c = (cost=3D0.56..1639944.37 rows=3D665761 width=3D107) (ac tual time=3D0.054..0.054 rows=3D0 loops=3D1) Index Cond: (channel_id =3D $0) Planning time: 0.178 ms Execution time: 0.088 ms (8 rows)