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 1ggXTa-0007yc-EU for pgsql-performance@arkaria.postgresql.org; Mon, 07 Jan 2019 16:10: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 1ggXTX-0002Sq-Op for pgsql-performance@arkaria.postgresql.org; Mon, 07 Jan 2019 16:10:03 +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 1ggXTX-0002Pf-6W for pgsql-performance@lists.postgresql.org; Mon, 07 Jan 2019 16:10:03 +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 1ggXTO-0007aR-H7 for pgsql-performance@lists.postgresql.org; Mon, 07 Jan 2019 16:10:02 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=iter.org; l=23380; q=dns/txt; s=20170425; t=1546877394; x=1547482194; h=from:to:cc:subject:date:message-id:references: in-reply-to:content-transfer-encoding:mime-version; bh=7ctU8IdcALbx/vndMFryl5tAwpllL8vLQXqfurLH/Ac=; b=h7Qm1o9qKkoAlI/udIDyeWMwD7swDRclVLD4PJBU5Uu6BtySPccbLYVy ht6ttNsyamWZfOwzp0bPiPwIkTNLMLDoasaj8as0Od7S2geKqrpDIvgtc 8Pp5C1Lj3BYh3/RCHyxhT6oRyPr5dcpeji1dWNB2/B7whuYdBV/7ySuWj g=; Received: from XCH-CLU1N3.iter.org (10.10.242.164) by XCH-CLU1N3.iter.org (10.10.242.164) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.1261.35; Mon, 7 Jan 2019 17:09:50 +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; Mon, 7 Jan 2019 17:09:50 +0100 From: Abadie Lana To: 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//rrHQAB4UUyA//wibdA= Date: Mon, 7 Jan 2019 16:09:50 +0000 Message-ID: <87705cc87ff74d6d9089adf227c5a9fb@iter.org> References: <49406fb0c239480980ffc6c2a250d4e9@iter.org> <438dcc57f91d4a9fab12ae4078592d69@iter.org> <20190103234743.GU25379@telsasoft.com> <7e1a311617e441d2a40dc97fa9766753@iter.org> <399cd3f79a704b21a47e4305cebfa727@iter.org> <20190105042357.GK25379@telsasoft.com> In-Reply-To: <20190105042357.GK25379@telsasoft.com> 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: Justin Pryzby =20 Sent: 05 January 2019 05:24 To: Abadie Lana Cc: David Rowley ; pgsql-performance@lists.po= stgresql.org Subject: Re: select query does not pick up the right index On Fri, Jan 04, 2019 at 08:58:57AM +0000, Abadie Lana wrote: > SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV,=20 > tablename, attname, null_frac, n_distinct,=20 > array_length(most_common_vals,1) n_mcv,=20 > array_length(histogram_bounds,1) n_hist FROM pg_stats WHERE=20 > attname=3D'...' AND tablename=3D'...' ORDER BY 1 DESC; >=20 > Hmm. Is it normal that the couple (tablename,attname ) is not unique?=20 > I'm surprised to see sample_{ctrl,util,buil} quoted twice One of the rows is for "inherited stats" (including child tables) stats and= one is "noninherited stats". The unique index on the table behind that view is: "pg_statistic_relid_att_inh_index" UNIQUE, btree (starelid, staattnum, = stainherit) On the wiki, I added inherited and correlation columns. Would you rerun th= at query ? https://wiki.postgresql.org/wiki/Slow_Query_Questions#Statistics:_n_distinc= t.2C_MCV.2C_histogram /*********************REPLY************************************************= **********/ css_archive_3_0_0=3D# SELECT (SELECT sum(x) FROM unnest(most_common_freqs) = x) frac_MCV, tablename, attname, inherited, null_frac, n_distinct, array_le= ngth(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist, co= rrelation FROM pg_stats WHERE attname=3D'smpl_time' AND tablename like 'sam= ple%' ORDER BY 1 DESC; frac_mcv | tablename | attname | inherited | null_frac | n_di= stinct | n_mcv | n_hist | correlation -------------+-------------------+-----------+-----------+-----------+-----= -------+-------+--------+------------- 0.124457 | sample_buil | smpl_time | f | 0 | -0.= 752503 | 10000 | 10001 | 0.0802559 0.100454 | sample_util | smpl_time | f | 0 | -0.= 323349 | 10000 | 10001 | 0.614187 0.0393624 | sample_buil_month | smpl_time | f | 0 | -0.= 617567 | 10000 | 10001 | 0.181361 0.0305711 | sample_util_month | smpl_time | f | 0 | -0.= 169437 | 10000 | 10001 | 0.781718 0.0194441 | sample_util_year | smpl_time | f | 0 | -0.= 428909 | 10000 | 10001 | 0.999893 0.0172493 | sample_util | smpl_time | t | 0 | -0.= 179957 | 10000 | 10001 | -0.563603 0.0117653 | sample | smpl_time | t | 0 | -0.= 235397 | 10000 | 10001 | 0.0880253 0.0116284 | sample_buil | smpl_time | t | 0 | -0.= 743071 | 10000 | 10001 | -0.100979 2.66667e-05 | sample_ctrl_month | smpl_time | f | 0 | -0.= 999848 | 32 | 10001 | -0.356626 8.48788e-06 | sample_ctrl | smpl_time | f | 0 | -0.= 999996 | 4 | 10001 | 0.331492 6.33333e-06 | sample_ctrl_year | smpl_time | f | 0 | -0.= 999835 | 9 | 10001 | 0.999971 5.33333e-06 | sample_ctrl | smpl_time | t | 0 | -0.= 999827 | 8 | 10001 | 0.0492292 5e-06 | sample_buil_year | smpl_time | f | 0 | -0.= 999918 | 7 | 10001 | 0.999978 (13 rows) css_archive_3_0_0=3D# SELECT (SELECT sum(x) FROM unnest(most_common_freqs) = x) frac_MCV, tablename, attname, inherited, null_frac, n_distinct, array_le= ngth(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist, co= rrelation FROM pg_stats WHERE attname=3D'channel_id' AND tablename like 'sa= mple%' ORDER BY 1 DESC; frac_mcv | tablename | attname | inherited | null_frac | n_dist= inct | n_mcv | n_hist | correlation ----------+-------------------+------------+-----------+-----------+-------= -----+-------+--------+------------- 0.99987 | sample_buil_year | channel_id | f | 0 | = 76 | 16 | 60 | 0.207932 0.999632 | sample_ctrl_year | channel_id | f | 0 | = 132 | 31 | 101 | 0.201352 0.999628 | sample_ctrl_month | channel_id | f | 0 | = 84 | 23 | 61 | 0.104656 0.999627 | sample_ctrl | channel_id | t | 0 | = 132 | 31 | 101 | 0.143691 0.999599 | sample_ctrl | channel_id | f | 0 | = 42 | 22 | 20 | 0.0874279 0.998074 | sample_buil | channel_id | f | 0 | = 493 | 122 | 371 | 0.0206452 0.997693 | sample_util | channel_id | f | 0 | = 1379 | 509 | 870 | 0.079591 0.991841 | sample_buil | channel_id | t | 0 | = 9867 | 107 | 9740 | 0.00540782 0.991567 | sample_util_month | channel_id | f | 0 | = 5716 | 504 | 5209 | 0.216868 0.990369 | sample_util_year | channel_id | f | 0 | = 4946 | 255 | 4689 | 0.547934 0.990062 | sample_util | channel_id | t | 0 | = 5804 | 641 | 5160 | -0.31778 0.972386 | sample_buil_month | channel_id | f | 0 | 1= 9946 | 148 | 10001 | 0.0932767 0.967391 | sample | channel_id | t | 0 | = 7597 | 409 | 7178 | 0.501865 (13 rows) css_archive_3_0_0=3D /**********************ENDREPLY********************************************= ****/ I'm also interested to see \d and channel_id statistics for the channel tab= le. /***********************REPLY**********************************************= */ \d channel Table "public.channel" Column | Type | Collation | Nullable | = Default --------------+------------------------+-----------+----------+------------= ----------------------- channel_id | bigint | | not null | nextval('ch= annel_chid'::regclass) name | character varying(100) | | not null | descr | character varying(100) | | | grp_id | bigint | | | smpl_mode_id | bigint | | | smpl_val | double precision | | | smpl_per | double precision | | | retent_id | bigint | | | 1 retent_val | double precision | | | Indexes: "channel_pkey" PRIMARY KEY, btree (channel_id) "unique_chname" UNIQUE CONSTRAINT, btree (name) "channel_name_channel_id_idx" btree (name, channel_id) SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename= , attname, inherited, null_frac, n_distinct, array_length(most_common_vals,= 1) n_mcv, array_length(histogram_bounds,1) n_hist, correlation FROM pg_stat= s WHERE attname in ('name','channel_id') AND tablename =3D'channel' ORDER B= Y 1 DESC; frac_mcv | tablename | attname | inherited | null_frac | n_distinct | n= _mcv | n_hist | correlation ----------+-----------+------------+-----------+-----------+------------+--= -----+--------+------------- | channel | channel_id | f | 0 | -1 | = | 10001 | 0.0200338 | channel | name | f | 0 | -1 | = | 10001 | -0.257645 /*********************ENDREPLY*********************************************= *******************/ > explain (analyze, buffers) select=20 > '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=20 > c.channel_id =3D (SELECT channel_id FROM channel WHERE=20 > name=3D'BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc=20 > limit 5; You originally wrote this as a implicit comma join. Does the original quer= y still have an issue ? The =3D(subselect query) doesn't allow the planner= to optimize for the given channel, which seems to be a fundamental problem= . /****************************REPLY*****************************************= **********/ Yes the original query still picks up the wrong index. This query actually = was suggested by David Rowley and actually with this one the planner is tak= ing the wring index for only sample_ctrl_year and sample_buil_year tables. = With some proper analyse, now only sample_ctrl_year. /*****************************ENDREPLY*************************************= *************/ On Fri, Jan 04, 2019 at 08:58:57AM +0000, Abadie Lana wrote: > Based on your feedback...i rerun analyse directly on the two table=20 > sample_ctrl_year and sample_buil_year [...] Now when running the query ag= ain, only for sample_buil_year table the wrong index is picked up... It looks like statistics on your tables were completely wrong; not just sam= ple_ctrl_year and sample_buil_year. Right ? /*****************************REPLY****************************************= ***************/ I would say that when you have a partitioned table, running analyse on the = parent table (which includes the children) does not give the same result as= running analyse on each individual child table. I don't know if it is an e= xpected behaviour? /********************************ENDREPLY**********************************= ******************/ Autoanalyze would normally handle this on nonempty tables (children or otherwise) and you should manually run ANALZYE on the parents (both levels = of them) whenever statistics change, like after running a big DELETE or DROP o= r after a significant interval of time has passed relative to the range of = time in the table's timestamp columns. Do you know why autoanalze didn't handle the nonempty tables on its own ? /******************************REPLY***************************************= ************************/ This database has been loaded via a dump. After there was no change in the = actual tables'content apart from creating/droping. indexes.=20 So I guess that's why autoanalyze didn't run (also I didn't change the defa= ult configuration for this part in postgresql.conf) /*******************************ENDREPLY***********************************= ***********************/ > Now, the channel name I gave has no entries in sample_buil_year...(and wh= en I run the query directly against sample_buil_year the right index is pic= ked up).... So maybe something related with the partitioning? > -> Index Scan Backward using smpl_time_bx2_idx on sample_= buil_year c_5 (cost=3D0.56..2023054.76 rows=3D665761 width=3D75) (actual t= ime=3D13216.589..13216.589 rows=3D0 loops=3D1) > Filter: (channel_id =3D $0) > Rows Removed by Filter: 50597834 > Buffers: shared hit=3D26626368 So it scanned the entire index expecting to find 5 matching channel IDs "pr= etty soon", based on the generic distribution of channel IDs, without the b= enefit of knowing that this channel ID doesn't exist at all (due to =3D(sub= query)). /*********************************REPLY************************************= ******************/ Exactly it took hearethe wrong index smpl_time_bx2_idx instead of sample_ti= me_by_idx. /*********************************ENDREPLY*********************************= *****************/ 26e6 buffers is 200GB, apparently accessing some pages many times (even if = cached). /**********************************REPLY***********************************= *********************/ Yes this is what I observed when running iotop...more than 17GB was read fr= om disk. I'm surprised as I would expect that the max. would be the index s= ize...~7GB. We also get an swap alert...because it uses swap... /********************************ENDREPLY**********************************= ****************/ table_name | index_name | table_size | index_size = = = =20 sample_buil_year | smpl_time_bx2_idx | 4492 MB | 1084 MB = = = =20 General comments: On Wed, Jan 02, 2019 at 04:28:41PM +0000, Abadie Lana wrote: > "sample_time_bm_idx" btree (channel_id, smpl_time) > "sample_time_mb1_idx" btree (smpl_time, channel_id) > "smpl_time_bx1_idx" btree (smpl_time) The smpl_time index is loosely redundant with index on (smpl_time,channel_i= d). You might consider dropping it, or otherwise dropping the smpl_time,channel= _id index and making two separate indices on smpl_time and channel. That w= ould allow bitmap ANDing them together. /******************************REPLY***************************************= ********************/ Yes I know. The thing is I had to find a quick solution to fix as my appli= cation was taking ages - two types of queries (one which requires channeld_= id=3DXX + order by time and another one by time range (all channels between= T1 and T2). As the smpl_time_bx1_idx was slowing down the first query, I created sample= _time_mb1_idx and drop smpl_time_bx1_idx. Now it has been recreated as I wanted to understand why the planner picked = up the wrong indexes.=20 /*****************************ENDREPLY*************************************= *********************/ Or possibly (depending on detail of your data loading) leaving the composit= e index and changing smpl_time to a BRIN index - it's nice to be able to CL= USTER on the btree index to maximize the efficiency of the brin index. >Check constraints: > "sample_buil_month_smpl_time_check" CHECK (smpl_time >=3D (now() -=20 >'32 days'::interval)::timestamp without time zone AND smpl_time <=3D=20 >now()) I'm surprised that works, and not really sure what it's doing..but in any c= ase it's maybe not doing what you wanted(??). I'm guessing you never get c= onstraint exclusion (which is irrelevant for this query but still). /*********************************REPLY************************************= ************/ I know that the partitioning is not exclusive in this one. In fact the inse= rt is done at sample_{util/buil/ctrl} table. The data is in this table. The= n there are some scripts which moves data from sample -> sample_month and t= hen sample_month-> sample_year.=20 I'm not the owner of this schema...so cannot comment why it has been done l= ike that...=20 And same for indexes. I cannot change them.=20 I did it in that case, because I did a copy of the database and launched th= e apps on this one (part of annual maintenance activities). I created the BRIN index on smpl_time and now the original query runs fine = because it uses the right index, the one on (channel_id,smpl_time) explain analyze select t.name, c.smpl_time,c.nanosecs,c.float_val,c.num_val= ,c.str_val,c.datatype,c.array_val from sample c, channel t where t.channel_= id=3Dc.channel_id and t.name=3D'BUIL-B36-VA-RT-RT1:CL0001-2-ABW' order by= c.smpl_time desc limit 5; = QUERY PLAN ---------------------------------------------------------------------------= ------------------------------------------------ ----------------------------------------------- Limit (cost=3D1869725.53..1869725.54 rows=3D5 width=3D113) (actual time= =3D3.898..3.900 rows=3D3 loops=3D1) -> Sort (cost=3D1869725.53..1869749.62 rows=3D9636 width=3D113) (actua= l time=3D3.896..3.897 rows=3D3 loops=3D1) Sort Key: c.smpl_time DESC Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=3D0.00..1869565.48 rows=3D9636 width=3D113)= (actual time=3D2.270..3.878 rows=3D3 loops=3D1) -> Seq Scan on channel t (cost=3D0.00..915.83 rows=3D1 wid= th=3D41) (actual time=3D2.212..3.773 rows=3D1 loops=3D1) Filter: ((name)::text =3D 'BUIL-B36-VA-RT-RT1:CL0001-2= -ABW'::text) Rows Removed by Filter: 33425 -> Append (cost=3D0.00..1853209.17 rows=3D1544048 width=3D= 88) (actual time=3D0.053..0.099 rows=3D3 loops=3D1) -> Seq Scan on sample c (cost=3D0.00..0.00 rows=3D1 = width=3D334) (actual time=3D0.002..0.002 rows=3D0 loops=3D1) Filter: (t.channel_id =3D channel_id) -> Bitmap Heap Scan on sample_buil c_1 (cost=3D52.67= ..5440.29 rows=3D2096 width=3D328) (actual time=3D0.016. .0.016 rows=3D0 loops=3D1) Recheck Cond: (channel_id =3D t.channel_id) -> Bitmap Index Scan on sample_time_b_idx (cos= t=3D0.00..52.14 rows=3D2096 width=3D0) (actual time=3D0. 008..0.008 rows=3D0 loops=3D1) Index Cond: (channel_id =3D t.channel_id) -> Bitmap Heap Scan on sample_ctrl c_2 (cost=3D522.3= 4..11512.86 rows=3D22441 width=3D328) (actual time=3D0.0 05..0.006 rows=3D0 loops=3D1) Recheck Cond: (channel_id =3D t.channel_id) -> Bitmap Index Scan on sample_time_c_idx (cos= t=3D0.00..516.73 rows=3D22441 width=3D0) (actual time=3D 0.005..0.005 rows=3D0 loops=3D1) Index Cond: (channel_id =3D t.channel_id) -> Bitmap Heap Scan on sample_util c_3 (cost=3D90.11= ..12215.14 rows=3D3830 width=3D328) (actual time=3D0.009 ..0.009 rows=3D0 loops=3D1) Recheck Cond: (channel_id =3D t.channel_id) -> Bitmap Index Scan on sample_time_u_idx (cos= t=3D0.00..89.16 rows=3D3830 width=3D0) (actual time=3D0. 006..0.006 rows=3D0 loops=3D1) Index Cond: (channel_id =3D t.channel_id) -> Bitmap Heap Scan on sample_buil_month c_4 (cost= =3D18.29..2836.29 rows=3D740 width=3D82) (actual time=3D0. 017..0.021 rows=3D3 loops=3D1) Recheck Cond: (channel_id =3D t.channel_id) Heap Blocks: exact=3D3 -> Bitmap Index Scan on sample_time_bm_idx (co= st=3D0.00..18.11 rows=3D740 width=3D0) (actual time=3D0. 012..0.012 rows=3D3 loops=3D1) Index Cond: (channel_id =3D t.channel_id) -> Bitmap Heap Scan on sample_buil_year c_5 (cost=3D= 15416.21..627094.50 rows=3D665761 width=3D83) (actual time=3D0.008..0.008 rows=3D0 loops=3D1) Recheck Cond: (channel_id =3D t.channel_id) -> Bitmap Index Scan on sample_time_by_idx (co= st=3D0.00..15249.77 rows=3D665761 width=3D0) (actual t ime=3D0.007..0.007 rows=3D0 loops=3D1) Index Cond: (channel_id =3D t.channel_id) -> Bitmap Heap Scan on sample_ctrl_month c_6 (cost= =3D5038.85..223721.75 rows=3D217585 width=3D83) (actual time=3D0.006..0.007 rows=3D0 loops=3D1) Recheck Cond: (channel_id =3D t.channel_id) -> Bitmap Index Scan on sample_time_cm_idx (co= st=3D0.00..4984.45 rows=3D217585 width=3D0) (actual ti me=3D0.006..0.006 rows=3D0 loops=3D1) Index Cond: (channel_id =3D t.channel_id) -> Bitmap Heap Scan on sample_ctrl_year c_7 (cost=3D= 13960.83..870933.00 rows=3D602872 width=3D84) (actual time=3D0.006..0.006 rows=3D0 loops=3D1) Recheck Cond: (channel_id =3D t.channel_id) -> Bitmap Index Scan on sample_time_cy_idx (co= st=3D0.00..13810.11 rows=3D602872 width=3D0) (actual t ime=3D0.005..0.015 rows=3D0 loops=3D1) Index Cond: (channel_id =3D t.channel_id) -> Bitmap Heap Scan on sample_util_month c_8 (cost= =3D288.81..45162.12 rows=3D12418 width=3D83) (actual tim e=3D0.008..0.008 rows=3D0 loops=3D1) Recheck Cond: (channel_id =3D t.channel_id) -> Bitmap Index Scan on sample_time_um_idx (co= st=3D0.00..285.70 rows=3D12418 width=3D0) (actual time =3D0.007..0.007 rows=3D0 loops=3D1) Index Cond: (channel_id =3D t.channel_id) -> Index Scan using sample_time_uy_idx on sample_util= _year c_9 (cost=3D0.57..54293.22 rows=3D16304 width =3D82) (actual time=3D0.010..0.010 rows=3D0 loops=3D1) Index Cond: (channel_id =3D t.channel_id) Planning time: 1.752 ms Execution time: 4.004 But not the other query...still time-consuming because still using the wron= g index in case of sample_buil_year (but curiously not the BRIN index) 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.54 rows=3D5 width=3D112) (actual time=3D63411.725= ..63411.744 rows=3D3 loops=3D1) Buffers: shared hit=3D38 read=3D193865 InitPlan 1 (returns $0) -> Index Scan using unique_chname on channel (cost=3D0.41..8.43 rows= =3D1 width=3D8) (actual time=3D0.039..0.040 rows=3D1 loops =3D1) Index Cond: ((name)::text =3D 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW':= :text) Buffers: shared hit=3D4 -> Result (cost=3D4.96..5294364.58 rows=3D1544048 width=3D112) (actual= time=3D63411.723..63411.740 rows=3D3 loops=3D1) Buffers: shared hit=3D38 read=3D193865 -> Merge Append (cost=3D4.96..5278924.10 rows=3D1544048 width=3D= 80) (actual time=3D63411.719..63411.735 rows=3D3 loops=3D1) Sort Key: c.smpl_time DESC Buffers: shared hit=3D38 read=3D193865 -> Index Scan Backward using sample_time_all_idx on sample = c (cost=3D0.12..8.14 rows=3D1 width=3D326) (actual ti me=3D0.048..0.048 rows=3D0 loops=3D1) Index Cond: (channel_id =3D $0) Buffers: shared hit=3D5 -> 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=3D0.008..0.009 rows=3D0 loops=3D1) Index Cond: (channel_id =3D $0) Buffers: shared hit=3D3 -> Index Scan Backward using sample_time_c_idx on sample_ct= rl c_2 (cost=3D0.42..77785.57 rows=3D22441 width=3D32 0) (actual time=3D0.006..0.006 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=3D0.008..0.008 rows=3D0 loops=3D1) Index Cond: (channel_id =3D $0) Buffers: shared hit=3D3 -> Index Scan Backward using sample_time_bm_idx on sample_b= uil_month c_4 (cost=3D0.56..2967.10 rows=3D740 widt h=3D74) (actual time=3D0.011..0.025 rows=3D3 loops=3D1) Index Cond: (channel_id =3D $0) Buffers: shared hit=3D8 -> Index Scan Backward using sample_time_yb1_idx on sample_= buil_year c_5 (cost=3D0.56..2186210.68 rows=3D66576 1 width=3D75) (actual time=3D63411.573..63411.574 rows=3D0 loops=3D1) Index Cond: (channel_id =3D $0) Buffers: shared read=3D193865 -> 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.030..0.030 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.009..0.009 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 w idth=3D75) (actual time=3D0.009..0.009 rows=3D0 loops=3D1) Index Cond: (channel_id =3D $0) Buffers: shared hit=3D4 -> Index Scan Backward using sample_time_uy_idx on sample_u= til_year c_9 (cost=3D0.57..54293.22 rows=3D16304 wi dth=3D74) (actual time=3D0.009..0.009 rows=3D0 loops=3D1) Index Cond: (channel_id =3D $0) Buffers: shared hit=3D4 Planning time: 0.727 ms Execution time: 63411.858 ms (43 rows) \d sample_buil_year Table "public.sample_buil_year" Column | Type | Collation | Nullable | Defau= lt -------------+-----------------------------+-----------+----------+--------= ----- 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) | | | ' '::bp= char array_val | bytea | | | Indexes: "sample_time_by_idx" btree (channel_id, smpl_time) "sample_time_yb1_idx" btree (smpl_time, channel_id) "smpl__by_brin_idx" brin (smpl_time) WITH (pages_per_range=3D'128') Check constraints: "sample_buil_year_smpl_time_check" CHECK (smpl_time >=3D (now() - '1 ye= ar 1 mon'::interval)::timestamp without time zone AND smpl_time <=3D now()) Inherits: sample_buil It works when I dropped the other index sample_time_yb1_idx The BRIN works well with the other query. Thanks for the tip I will look in= to more details on this BRIN. Thanks for your help /********************************ENDREPLY**********************************= ***********/ Justin