LATERAL query extreme slow due to partition

Поиск

Список

Период

Сортировка

От	Tom Smith
Тема	LATERAL query extreme slow due to partition
Дата	7 сентября 2015 г. 05:07:18
Msg-id	CAKwSVFFoXg_xkeVV7_z0-FyaLyKbvWQQ-_5d8L3+KVD6mZD9rA@mail.gmail.com обсуждение исходный текст
Ответы	Re: LATERAL query extreme slow due to partition
Список	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

В списке pgsql-general по дате отправления: