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 1ghJRH-0003ht-6R for pgsql-performance@arkaria.postgresql.org; Wed, 09 Jan 2019 19:22:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ghJRF-0002FS-M2 for pgsql-performance@arkaria.postgresql.org; Wed, 09 Jan 2019 19:22:53 +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 1ghJRF-0002FL-8c for pgsql-performance@lists.postgresql.org; Wed, 09 Jan 2019 19:22:53 +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 1ghJRB-0008Ho-Hr for pgsql-performance@lists.postgresql.org; Wed, 09 Jan 2019 19:22:52 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=iter.org; l=3668; q=dns/txt; s=20170425; t=1547061769; x=1547666569; h=from:to:cc:subject:date:message-id:references: in-reply-to:mime-version; bh=FIs0KFLbliDYyJRVb8simAVu7TGDhRynFNQvTLqFN1Q=; b=BklvdccEr5YsgO1P1/1+as1DooufVaIuNVVdWRI6zqKijJS15duEZ5nf TJDuty73UFC63atM/nzukrYqe7Lh1ePc0LI9Mx+iP8IDx1QBdlQ96hn2n VBPRCAhuQ/X21+qkFQ8jETToZUel8e1+9Uu4MpBtNIjd74sd9j96wMKwK U=; Received: from XCH-CLU1N3.iter.org (10.10.242.164) by XCH-CLU1N2.iter.org (10.10.241.164) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.1261.35; Wed, 9 Jan 2019 20:22:45 +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; Wed, 9 Jan 2019 20:22:45 +0100 From: Abadie Lana To: David Rowley CC: Justin Pryzby , "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//wibdD/9yq/AP/shstA/9i8mQD/sTxUCA== Date: Wed, 9 Jan 2019 19:22:45 +0000 Message-ID: <9036cbbff60c4fbe978e4a1b825feca0@iter.org> References: <49406fb0c239480980ffc6c2a250d4e9@iter.org> <438dcc57f91d4a9fab12ae4078592d69@iter.org> <20190103234743.GU25379@telsasoft.com> <7e1a311617e441d2a40dc97fa9766753@iter.org> <399cd3f79a704b21a47e4305cebfa727@iter.org> <20190105042357.GK25379@telsasoft.com> <87705cc87ff74d6d9089adf227c5a9fb@iter.org> <20190108081528.GF25379@telsasoft.com> <2ff1004f5faa4c9a841cb29c90473251@iter.org>, In-Reply-To: Accept-Language: en-GB, en-US Content-Language: en-GB X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.10.247.251] Content-Type: multipart/alternative; boundary="_000_9036cbbff60c4fbe978e4a1b825feca0iterorg_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_9036cbbff60c4fbe978e4a1b825feca0iterorg_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Oups wrong copy and paste. I did run your query with equal instead of in bu= t it resulted in the same plan ________________________________ From: David Rowley Sent: 09 January 2019 17:41:24 To: Abadie Lana Cc: Justin Pryzby; pgsql-performance@lists.postgresql.org Subject: Re: select query does not pick up the right index On Thu, 10 Jan 2019 at 01:55, Abadie Lana wrote: > The other query suggested by D.Rowley has the same issue : still swap act= ivity is higher. > explain analyze select 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW', c.smpl_time,c.n= anosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample = c where c.channel_id in (select channel_id from channel where name =3D'BUIL= -B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc limit 5; This is not the query I suggested. I mentioned if channel.name had a unique index, you'd be able to do WHERE c.channel_id =3D (select channel_id from channel where name =3D '...'). That's pretty different to what you have above. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services --_000_9036cbbff60c4fbe978e4a1b825feca0iterorg_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Oups wrong copy and paste. I did run your query with equal instead of = in but it resulted in the  same plan
From: David Rowley <da= vid.rowley@2ndquadrant.com>
Sent: 09 January 2019 17:41:24
To: Abadie Lana
Cc: Justin Pryzby; pgsql-performance@lists.postgresql.org
Subject: Re: select query does not pick up the right index
 
On Thu, 10 Jan 2019 at 01:55, Abadie Lana <Lana= .Abadie@iter.org> wrote:
> The other query suggested by D.Rowley has the same issue : still swap = activity is higher.
> explain analyze 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 samp= le c where c.channel_id in (select channel_id from channel where name =3D'B= UIL-B36-VA-RT-RT1:CL0001-2-ABW')   order by c.smpl_time  desc limit 5;

This is not the query I suggested.  I mentioned if channel.name had a<= br> unique index, you'd be able to do WHERE c.channel_id =3D (select
channel_id from channel where name =3D '...').  That's pretty differen= t
to what you have above.

--
 David Rowley         &nb= sp;         http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services
 --_000_9036cbbff60c4fbe978e4a1b825feca0iterorg_--