Date: Fri, 24 Apr 2020 14:33:23 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Arcadio Ortega Reinoso <arcadio.ortega@gmail.com>,
	Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: PostgreSQL does not choose my indexes well
Message-ID: <20200424183323.GO13712@tamriel.snowman.net>
References: <35dc90d6-e0c2-13b8-3184-e3db5d854a78@gmail.com>
 <CAMkU=1w0kSDvBO=KrRSxk8_oFc5cSR+O1J5zkp4+Ci4RF0_+Pw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="OdaeBISurJyWgUIi"
Content-Disposition: inline
In-Reply-To: 
 <CAMkU=1w0kSDvBO=KrRSxk8_oFc5cSR+O1J5zkp4+Ci4RF0_+Pw@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--OdaeBISurJyWgUIi
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Greetings,

* Jeff Janes (jeff.janes@gmail.com) wrote:
> In order to read 1409985 / 12839 = 109 rows per buffer page, the table must
> be extraordinarily well clustered on this index.  That degree of clustering
> is going to steal much of the thunder from the index-only scan.  But in my
> hands, it does still prefer the partial index with index-only scan by a
> cost estimate ratio of 3 to 1 (despite it actually being slightly slower)
> so I don't know why you don't get it being used.

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).

Thanks,

Stephen

--OdaeBISurJyWgUIi
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJeozDzAAoJEO1sijiDR2RV+uUP/ilbvIjBY+I0ujUxg4vawPoI
6J1ZLNcp7UqYbDPjrCfxk77e+37qwQBonyq2+TTMdcd0reUH3ZbdHLQtLnxhE8W4
YIsW1fkbGUdlUDG6Sln6f/qTs0ZhLTZOZYNVu8DAc5gMiW4AE5fC7muzbhcP6zLW
nDjmffzkjJSDZ8wI3NOHlq8vKkfgUKNhI7oW0dF5Ng2564qG2Oq2sOYkwYDbB2ZE
hYnOmoIBRnhd/Unn9INeb2XXn+AWID7Itkz1GdDJeX2gElvs6QgituTLGThPL+JG
/Z/uOFK8LItZgZeOTHsCU4hc7wI94h5eCKNvcxju5m2mgAsg+azjq0uK/Rz5O1a8
nz5UaGTbNDk+QoO60ozpG12b8A2BKuyeIwnp9FWSKslWthNhA8L7nfFZMdcAHKAa
Ek1jjNZsAke2LAWAFslJ3LJXzmW5Wj9l4f4By8rVS1v+qiFJlt0R/F08jdc+/ho0
82ht9cwPHwLfN0tPy+pIcCDkqBuCa7wMrh9nBiB5ZtGPFeNYEt2nMIKx72Tbd1FW
pQc+B8o/kUGQ4lLqvrSWwlylFsz9x4CHS9UZ8GGWvPOGbUqwZqVPet+PIyctWmN8
ZF7s5VPjWWnrKkXBmmOIWzAGiJbV0CFZvjL4i+9ozlUGW0d/C7GDNXeRV/mNFmga
Ckc3VHN8m1UthRCwXZL1
=f8fC
-----END PGP SIGNATURE-----

--OdaeBISurJyWgUIi--