public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jim Nasby <[email protected]>
To: Tom Lane <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Regression from 9.4-9.6
Date: Sun, 8 Oct 2017 16:07:04 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
On 10/8/17 3:37 PM, Tom Lane wrote:
> Jim Nasby <[email protected]> writes:
>> On 10/8/17 2:34 PM, Tom Lane wrote:
>>> Why has this indexscan's cost estimate changed so much?
>
>> Great question... the only thing that sticks out is the coalesce(). Let
>> me see if an analyze with a higher stats target changes anything. FWIW,
>> the 9.6 database is copied from the 9.4 one once a week and then
>> pg_upgraded. I'm pretty sure an ANALYZE is part of that process.
>
> Hm, now that I see the SubPlan in there, I wonder whether 9.6 is
> accounting more conservatively for the cost of the subplan. It
> probably is assuming that the subplan gets run for each row fetched
> from the index, although the loops and rows-removed counts show
> that the previous filter conditions reject 99% of the fetched rows.
>
> But that code looks the same in 9.4, so I don't understand why
> the 9.4 estimate isn't equally large ...
Besides the analyze issue, the other part of this is
[email protected]/20106> select
pg_size_pretty(pg_relation_size('bdata_forks'));
pg_size_pretty
----------------
106 GB
(1 row)
[email protected]/20106> select relpages::bigint*8192/reltuples from
pg_class where relname='bdata_forks';
?column?
------------------
185.559397863791
(1 row)
With an effective_cache_size of 200GB that's not really helping things.
But it's also another example of the planner's reluctance towards index
scans.
--
Jim C. Nasby, Data Architect [email protected]
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
reply
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: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Regression from 9.4-9.6
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox