Chris Mayfield <[EMAIL PROTECTED]> writes:
>>> So the long and the short of it is that the COALESCE acts as an
>>> optimization fence in the presence of outer joins. We've seen this
>>> before and there are some rough ideas about fixing it.
> You may already have this rough idea somewhere, but it
Gregory Stark <[EMAIL PROTECTED]> writes:
> "Tom Lane" <[EMAIL PROTECTED]> writes:
>> Don't hold your breath though --- it'll take major planner surgery.
> In this case isn't all the planner needs the pathkey list to give it a hint
> that that ordering might be useful?
You could maybe make that w
"Chris Mayfield" <[EMAIL PROTECTED]> writes:
> You may already have this rough idea somewhere, but it seems to me that the
> view could be flattened into the upper query as long as the join predicates
> don't depend on coalesced columns. In the examples I sent, even if the
> COALESCE is evaluated
Thank you for your prompt reply, I appreciate your insight on this.
> So the COALESCE has to be evaluated below the outer join, which means
> that the view can't be "flattened" into the upper query.
> ...
> So the long and the short of it is that the COALESCE acts as an
> optimization fence in th
"Tom Lane" <[EMAIL PROTECTED]> writes:
> In the case where you introduce the intermediate sub-select, the
> view *can* be flattened into that, producing
> SELECT id, COALESCE(opt, 0) AS opt FROM b ORDER BY id
> Again, that can't be flattened into the top query, but looking at
> it in isolati
Chris Mayfield <[EMAIL PROTECTED]> writes:
> See attached -- I've simplified my actual database quite a bit, but this
> example shows the same results.
OK, here's the problem:
> CREATE VIEW v AS
> SELECT id, COALESCE(opt, 0) AS opt FROM b;
You're using this inside the nullable side of an oute
See attached -- I've simplified my actual database quite a bit, but this
example shows the same results.
Thanks,
--Chris
--
-- Why does the optimizer insist on sorting a clustered table?
--
-- NOTE: This script requires 540 MB of disk space and about
-- 12 minutes to run (on my good old Sun-Bla
Chris Mayfield <[EMAIL PROTECTED]> writes:
> [ planner finds better plan with a forced ORDER BY ]
That shouldn't happen. Can you show the details of your case?
It may be something specific to the particular view definition...
regards, tom lane
--
Sent via pgsql-general
Hello,
I have a scenario with two tables, one with 5M rows and the other with
about 3.7M (a subset taken from the first table). Each is clustered
using its primary key (a single bigint column), and pg_stats shows that
the id's correlation is 1 for both tables. In addition, I have a view
ove
