Search Postgresql Archives

Re: LATERAL query extreme slow due to partition

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.


On Tue, Sep 8, 2015 at 3:51 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:
On Mon, Sep 7, 2015 at 12:07 AM, Tom Smith <tomsmith1989sk@xxxxxxxxx> wrote:
> Hi:
>
> I am using the wonderful  lateral query  feature like the following
>
> select * from generate_series (1,100000,5) T(t),
>    lateral (select * from P where t between  t and t +  3)
>
> P is a parent table of a hundred partitions
> the idea is to for each t value from 1 to 100000 with step of 5,
> get rows from P (in one or two of its partitions) that  between
> the current value of t and t+3,
> so each lateral select should only index scan one or two partitons
> but the query plan shows that each will scan all hundred paritions,
> I think due to its unable to determine the range since
> the query is select * from P where t between  t and t +  3
> as "t" is unknown at the time of parsing.
>
> 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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux