Re: LATERAL query extreme slow due to partition

On Tue, Sep 8, 2015 at 3:24 PM, Tom Smith <tomsmith1989sk(at)gmail(dot)com> wrote:
> On Tue, Sep 8, 2015 at 3:51 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> On Mon, Sep 7, 2015 at 12:07 AM, Tom Smith <tomsmith1989sk(at)gmail(dot)com>
>> > How to force query planner "dynamically" generate plan to
>> > for each lateral select query as "t" changes.
>>
>> I think you're asking to much of the planner here. The query is
>> planned first and executed second. Because of that partitioning
>> generally depends on specific values, not dynamic ones, for exclusion
>> to take effect. I would consider rewriting to loop and see if that
>> helps.
>
> Almost all lateral query would be "dynamic sql" since it will vary as left
> value changes.
> perhaps query planner can mark it as "deferred" and during execution,
> replacing the original planning with a newly generated plan.
> Or we have to say lateral feature is not suitable for partitioned table.

Lateral works fine with partitioned tables. The underlying problem is
how constraint exclusion works; it depends on being able to examine
the query without executing it and from there plan it out. There are
a lot of ways to break this besides lateral. For example, suppose you
have a table partitioned on id:

SELECT * FROM foo WHERE id = 1; <- CE works
SELECT * FROM foo JOIN bar USING (id); <- CE will not work, even if
bar only has one record with id = 1

The rule that planning shall not depend on execution is very unlikely
to change.

merlin