Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jSJWB-0000aa-QY for pgsql-performance@arkaria.postgresql.org; Sat, 25 Apr 2020 12:02:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jSJWA-0000tu-Ml for pgsql-performance@arkaria.postgresql.org; Sat, 25 Apr 2020 12:02:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jSJWA-0000oV-Ep for pgsql-performance@lists.postgresql.org; Sat, 25 Apr 2020 12:02:46 +0000 Received: from tamriel.snowman.net ([96.255.250.162]) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jSJW7-0001XV-De for pgsql-performance@lists.postgresql.org; Sat, 25 Apr 2020 12:02:45 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id D1AD65F7B5; Sat, 25 Apr 2020 08:02:46 -0400 (EDT) Date: Sat, 25 Apr 2020 08:02:46 -0400 From: Stephen Frost To: Tom Lane Cc: Jeff Janes , Arcadio Ortega Reinoso , Pgsql Performance Subject: Re: PostgreSQL does not choose my indexes well Message-ID: <20200425120246.GP13712@tamriel.snowman.net> References: <35dc90d6-e0c2-13b8-3184-e3db5d854a78@gmail.com> <20200424183323.GO13712@tamriel.snowman.net> <13457.1587757192@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="eBcAWRNUbPKpmSpB" Content-Disposition: inline In-Reply-To: <13457.1587757192@sss.pgh.pa.us> User-Agent: Mutt/1.5.24 (2015-08-30) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --eBcAWRNUbPKpmSpB Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Greetings, * Tom Lane (tgl@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > Turns out to be because what was provided wasn't actually what was being > > used- there's a domain in there and that seems to gum up the works and > > make it so we don't consider the partial index as being something we can > > use (see the discussion at the end of the other sub-thread). >=20 > Some simple experiments here don't find that a domain-type column prevents > use of the partial index. So it's still not entirely clear what's > happening for the OP. I concur with Jeff's suggestion to try forcing > use of the desired index, and see whether it happens at all and what > the cost estimate is. Once burned, twice shy, I suppose- considering we weren't given the actual DDL the first round, I'm guessing there's other differences. > I'm also wondering exactly which Postgres version this is. Also a good question. Thanks, Stephen --eBcAWRNUbPKpmSpB Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBCgAGBQJepCbmAAoJEO1sijiDR2RVNRcQALG1YGGl/w1RxJ8PM7H5Czuc S2Snowj8YBbebxnVdcAgDhRZ8UcZoJlsp0JVl0C3+Ef75zakn97GWRtPuR5wHAe2 UqxbTjXpPLgv6qudTNJQfUPrSt2qrByAhwPwmBpUO9VLC2902G02MT2TMlA+8O82 T8VHsvC3o7E4OHoZncLnQOZTvMYU1iy5Ws7o4YIi8oFJ6or0kfzCAo9Su1FLxR+B Rm0XsCh7M8rb12MInUFA956Y/kA25WAltYESEAXc8etSuO/1pPJHKOlOTAxC48tm OogPJ7CHVs77GUXex03FcpB8cygaKRHMsHJMUIF/W/Fl8afNuRyoxxhX97OW01/z Qjp/ugnZasOMK5be7lMx2K0uqGh1sLaUlurc19JRPjqpGhn9nBd6QI+xZe2PiJK1 yV197/RHCe5pEZ83FYaxrW2aLdKiTY/oBGrnRvlkSDMht921YbQsFwpJThGsfVmt ZzBNyCz7VJpQhAlVJZKeYTfrhEHVeIOx/ogr0/kRXbIcRTioUcr+B0SIyPiy/X9g 5q0t/fH3GGifGiHP8TNGxDIWx2KGtdkSpshxweXIvEVx6+2lF5zdrkKr0jA/Y/2D H8Q44B/JyH+V3ap/5Ha6xHx7hYCWy2knsQLEHFPY1iOmT5VrAl2O5aSPiCLBW+NF BbsGTjeZV9fKC+wMBu4k =Xqnw -----END PGP SIGNATURE----- --eBcAWRNUbPKpmSpB--