Subject: Re: Regression from 9.4-9.6
From: Jim Nasby <jim@nasby.net>
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>
Message-ID: <08b2dc5f-db10-dac4-f857-3e705ce69da6@nasby.net>
Date: Sun, 8 Oct 2017 15:33:07 -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: <e0a4a8e6-d2e6-c6a8-dbb0-92131302bcbb@nasby.net>
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:02 PM, Jim Nasby wrote:
>>
>>>> -> Index Scan using bdata_filed_departuretime on bdata_forks 
>>>> (cost=0.57..50807.51 rows=1 width=36) (actual time=979.381..3207.777 
>>>> rows=508 loops=1)
>>
>>>> -> Index Scan using bdata_filed_departuretime on bdata_forks 
>>>> (cost=0.57..14894236.06 rows=1 width=36) (actual 
>>>> time=892.664..3025.653 rows=508 loops=1)
>>
>> I think the reason it's discarding the preferable plan is that, with this
>> huge increment in the estimated cost getting added to both alternatives,
>> the two nestloop plans have fuzzily the same total cost, and it's picking
>> the one you don't want on the basis of some secondary criterion.
> 
> 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.

Turns out that analyze is the 'problem'. On the 9.4 database, pg_stats 
shows that the newest date in filed_departuretime is 3/18/2017, while 
the 9.6 database is up-to-date. If I change the query to use 2/9/2018 
instead of 7/20/2017 I get the same results.

So, the larger cost estimate is theoretically more correct. If I set 
random_page_cost = 1 I end up with a good plan.
-- 
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