From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Grittner <kgrittn@ymail.com>
cc: "Carson, Leonard" <lcarson@sdsc.edu>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: views much slower in 9.3 than 8.4
In-reply-to: <1760411806.1238053.1427726764629.JavaMail.yahoo@mail.yahoo.com>
References: <1174.1427562844@sss.pgh.pa.us>
 <1760411806.1238053.1427726764629.JavaMail.yahoo@mail.yahoo.com>
Comments: In-reply-to Kevin Grittner <kgrittn@ymail.com>
 message dated "Mon, 30 Mar 2015 14:46:04 -0000"
Date: Mon, 30 Mar 2015 11:52:09 -0400
Message-ID: <15849.1427730729@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Kevin Grittner <kgrittn@ymail.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> But the other problem is that the planner considers less-than-1%
>> differences in cost estimates to be "in the noise", which means
>> that it's not going to consider cost differences of less than
>> 1480 units in the remaining join steps to be significant.  This
>> is how come we end up with the apparently brain-dead decisions to
>> use seqscans on some of the other tables such as "pi" and "ac":
>> comparing the seqscan to a potential inner indexscan, the total
>> cost of the join is "the same" according to the 1% rule,

> The 1% rule itself might be something to add to the R&D list.

Perhaps.  But it does make for a significant difference in planner speed,
and I would argue that any case where it really hurts is by definition
a cost estimation failure somewhere else.

> [ disable_cost skews the behavior pretty badly ]

True.  Your example suggests that it might be nice to have something other
than a cost-delta way of discriminating against seqscans.  In principle
this consideration could be added to add_path(), although I'm pretty
hesitant to make that function even more complex/slower.

Perhaps another way would be to generate seqscan paths last (I think
they're first at the moment), and only generate them if we didn't find
any other path for the rel.

Nestloops for join rels have the same issue and would need to be handled
similarly, whatever solution we pick.

			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