From: Stephen Frost <sfrost@snowman.net>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Arcadio Ortega Reinoso <arcadio.ortega@gmail.com>
Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: PostgreSQL does not choose my indexes well
Date: Fri, 24 Apr 2020 14:33:23 -0400
Message-ID: <20200424183323.GO13712@tamriel.snowman.net> (raw)
In-Reply-To: <CAMkU=1w0kSDvBO=KrRSxk8_oFc5cSR+O1J5zkp4+Ci4RF0_+Pw@mail.gmail.com>
References: <35dc90d6-e0c2-13b8-3184-e3db5d854a78@gmail.com>
<CAMkU=1w0kSDvBO=KrRSxk8_oFc5cSR+O1J5zkp4+Ci4RF0_+Pw@mail.gmail.com>
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
Attachments:
[application/pgp-signature] signature.asc (818B, ../20200424183323.GO13712@tamriel.snowman.net/2-signature.asc)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sfrost@snowman.net, jeff.janes@gmail.com, arcadio.ortega@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: PostgreSQL does not choose my indexes well
In-Reply-To: <20200424183323.GO13712@tamriel.snowman.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox