Home > mailing lists

LATERAL query extreme slow due to partition - Mailing list pgsql-general

From	Tom Smith
Subject	LATERAL query extreme slow due to partition
Date	September 7, 2015 05:07:18
Msg-id	CAKwSVFFoXg_xkeVV7_z0-FyaLyKbvWQQ-_5d8L3+KVD6mZD9rA@mail.gmail.com Whole thread Raw
Responses	Re: LATERAL query extreme slow due to partition
List	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: Tom Smith
Date: 07 September 2015, 04:58:09
Subject: Re: modify postgresql.conf

From: Raymond O'Donnell
Date: 07 September 2015, 07:44:57
Subject: Re: table dependencies