Request a Demo
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
Msg-id CAKwSVFFoXg_xkeVV7_z0-FyaLyKbvWQQ-_5d8L3+KVD6mZD9rA@mail.gmail.com
Whole thread
Responses Re: LATERAL query extreme slow due to partition
List pgsql-general
Tree view
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 by date:

Previous
From: Tom Smith
Date:
Subject: Re: modify postgresql.conf
Next
From: Raymond O'Donnell
Date:
Subject: Re: table dependencies