From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Number of characters in column preventing index usage
Date: Fri, 17 Feb 2017 19:04:41 -0500
Message-ID: <31392.1487376281@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKFQuwaDX7vJSrX2Rkz9njXseFVRe8eAStOvCQzqd4pc_vhcNg@mail.gmail.com>
References: <CAM00CHH3H3SLqx8P68OnfuxoLTnkZP80b3PP+_pvbf3=3-OKYw@mail.gmail.com>
<bdba910d-f3f5-7eaa-f66d-02ff29361cec@2ndquadrant.com>
<CAKFQuwaDX7vJSrX2Rkz9njXseFVRe8eAStOvCQzqd4pc_vhcNg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Fri, Feb 17, 2017 at 3:49 PM, Tomas Vondra <tomas.vondra@2ndquadrant.com>
> wrote:
>> That may seem a bit strange, but I'd bet it finds the short value in some
>> statistic (MCV, histogram) ans so can provide very accurate estimate.
> I'm not seeing how any of the statistic columns would capture a value that
> doesn't actually appear in the table...(actual ... row=0)
I think it's the other way around. It found
'01625cfa-2bf8-45cf-bf4c-aa5f3c6fa8ea' in the stats, concluded
(accurately) that there would be five matches, and on the strength of that
decided that a seqscan over this very tiny table would be faster than an
indexscan. In the other case, the short string exists neither in the
table nor the stats, and the default estimate is turning out to be that
there's a single match, for which it likes the indexscan solution. This
is all pretty unsurprising if '01625cfa-2bf8-45cf-bf4c-aa5f3c6fa8ea'
is in the most-common-values list. Anything that's *not* in that list
is going to get a smaller rowcount estimate. (I don't think that the
string length, per se, has anything to do with it.)
I'm not sure what performance problem the OP was looking to solve,
but expecting experiments on toy-sized tables to give the same plans
as you get on large tables is a standard mistake when learning to work
with the PG planner.
Also, if toy-sized tables are all you've got, meaning the whole database
can be expected to stay RAM-resident at all times, it'd be a good idea
to reduce random_page_cost to reflect that. The default planner cost
settings are meant for data that's mostly on spinning rust.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, tomas.vondra@2ndquadrant.com
Subject: Re: Number of characters in column preventing index usage
In-Reply-To: <31392.1487376281@sss.pgh.pa.us>
* 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