On Thu, Mar 28, 2019 at 5:44 PM Raj Gandhi <raj01gandhi@xxxxxxxxx> wrote: > > + pgsql-performance > > On Thu, Mar 28, 2019 at 6:41 PM Raj Gandhi <raj01gandhi@xxxxxxxxx> 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
