LATERAL query extreme slow due to partition
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.
Thanks
On Mon, Sep 7, 2015 at 12:07 AM, Tom Smith <tomsmith1989sk@gmail.com> 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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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@gmail.com> wrote:
Show quoted text
On Mon, Sep 7, 2015 at 12:07 AM, Tom Smith <tomsmith1989sk@gmail.com>
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.
On Tue, Sep 8, 2015 at 3:24 PM, Tom Smith <tomsmith1989sk@gmail.com> wrote:
On Tue, Sep 8, 2015 at 3:51 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
On Mon, Sep 7, 2015 at 12:07 AM, Tom Smith <tomsmith1989sk@gmail.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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general