From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jim Nasby <jim@nasby.net>
cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Regression from 9.4-9.6
In-reply-to: <5d7c0f80-3767-1531-d911-197fcc147173@nasby.net>
References: <5d7c0f80-3767-1531-d911-197fcc147173@nasby.net>
Comments: In-reply-to Jim Nasby <jim@nasby.net>
	message dated "Sun, 08 Oct 2017 14:25:54 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <11433.1507491291.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 08 Oct 2017 15:34:51 -0400
Message-ID: <11434.1507491291@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Jim Nasby <jim@nasby.net> writes:
> I've got a query that's regressed from 9.4 to 9.6. I suspect it has=20
> something to do with the work done around bad plans from single-row=20
> estimates.

Why has this indexscan's cost estimate changed so much?

>> ->  Index Scan using bdata_filed_departuretime on bdata_forks  (cost=3D0=
.57..50807.51 rows=3D1 width=3D36) (actual time=3D979.381..3207.777 rows=3D=
508 loops=3D1)

>> ->  Index Scan using bdata_filed_departuretime on bdata_forks  (cost=3D0=
.57..14894236.06 rows=3D1 width=3D36) (actual time=3D892.664..3025.653 rows=
=3D508 loops=3D1)

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.

			regards, tom lane


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