Re: LIMIT OFFSET with DB view vs plain SQL

On Thu, Mar 28, 2019 at 5:44 PM Raj Gandhi <raj01gandhi(at)gmail(dot)com> wrote:
>
> + pgsql-performance
>
> On Thu, Mar 28, 2019 at 6:41 PM Raj Gandhi <raj01gandhi(at)gmail(dot)com> wrote:
>>
>> Hi everyone,
>>
>>
>>
>> I’m using LIMIT offset with DB view. Looks like query planner is applying the LIMIT for DB view at the end after processing all rows.
>>
>> When running same SQL that was used to create the DB view, LIMIT is applied earlier so the query is much faster.
>>
>>
>>
>> Explain plan using DB view
>>
>> https://explain.depesz.com/s/gzjQ
>>
>>
>>
>> Explain plan using raw SQL
>>
>> https://explain.depesz.com/s/KgwO
>>
>>
>>
>> In both tests LIMIT was 100 with offset = 0.
>>
>> Is there any way to force DB view to apply limit earlier?

huh. OFFSET does indeed force a materialize plan. This is a widely
used tactic to hack the planner ('OFFSET 0').

Maybe try converting your query from something like:

SELECT * FROM foo LIMIT m OFFSET N;
to
WITH data AS
(
SELECT * FROM foo LIMIT m + n
)
SELECT * FROM foo OFFSET n;

I didn't try this, and it may not help, but it's worth a shot.

merlin