Subject: Re: Regression from 9.4-9.6
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
References: <5d7c0f80-3767-1531-d911-197fcc147173@nasby.net>
 <11434.1507491291@sss.pgh.pa.us>
 <e0a4a8e6-d2e6-c6a8-dbb0-92131302bcbb@nasby.net>
 <13826.1507495079@sss.pgh.pa.us>
From: Jim Nasby <jim@nasby.net>
Message-ID: <f2dc7822-6764-135e-1a0a-6be51ef7fbe3@nasby.net>
Date: Sun, 8 Oct 2017 16:07:04 -0500
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:52.0)
 Gecko/20100101 Thunderbird/52.3.0
MIME-Version: 1.0
In-Reply-To: <13826.1507495079@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 10/8/17 3:37 PM, Tom Lane wrote:
> Jim Nasby <jim@nasby.net> 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

asdidata@graceful.hou/20106> select 
pg_size_pretty(pg_relation_size('bdata_forks'));
  pg_size_pretty
----------------
  106 GB
(1 row)

asdidata@graceful.hou/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                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance