From: | Tom Smith <tomsmith1989sk(at)gmail(dot)com> |
---|---|
To: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | LATERAL query extreme slow due to partition |
Date: | 2015-09-07 05:07:09 |
Message-ID: | CAKwSVFFoXg_xkeVV7_z0-FyaLyKbvWQQ-_5d8L3+KVD6mZD9rA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Raymond O'Donnell | 2015-09-07 07:44:31 | Re: table dependencies |
Previous Message | Tom Smith | 2015-09-07 04:58:01 | Re: modify postgresql.conf |