From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Grittner <kgrittn@ymail.com>
Cc: Carson, Leonard <lcarson@sdsc.edu>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: views much slower in 9.3 than 8.4
Date: Mon, 30 Mar 2015 11:52:09 -0400
Message-ID: <15849.1427730729@sss.pgh.pa.us> (raw)
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
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: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, kgrittn@ymail.com, lcarson@sdsc.edu
Subject: Re: views much slower in 9.3 than 8.4
In-Reply-To: <15849.1427730729@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox