LATERAL query extreme slow due to partition

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

Responses

Browse pgsql-general by date

  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