Re: LIMIT OFFSET with DB view vs plain SQL

Hi Raj,

I have long time without working on pgsql performance, but you can try
materialized views or if you are already using its try apply some
performance tips...

This are some link i found in a fast search, but if you solution is going
by this way this can be a kickstart to solve your problem..

https://thoughtbot.com/blog/advanced-postgres-performance-tips

http://www.postgresqltutorial.com/postgresql-materialized-views/

Take in account that materialized views have to be filled and use
additional space..

Hope this can help you solving you issue

On Thu, Mar 28, 2019, 7: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?
>>
>>
>>
>> Thanks,
>>
>> Raj
>>
>