Re: LIMIT OFFSET with DB view vs plain SQL

Merlin, I tried the hack you suggested but that didn't work. Planner used
the same path.

The same query works much faster when using the raw SQL instead of DB view:

Here is the definition of DB View ‘job’

SELECT w.id,

w.parent_id,

w.status AS state,

w.percent_complete AS progress_percentage,

w.start_time,

w.end_time,

w.est_completion_time AS estimated_completion_time,

w.root_id,

w.internal AS is_internal,

w.order_id AS step_order,

c.resource_type,

c.resource_id,

c.id AS command_id,

c.client_cookie,

c.user_name AS "user",

c.metadata,

c.client_address,

response_body(r.*, w.*) AS response_body

FROM work_unit w

LEFT JOIN command c ON c.work_unit_id = w.id

LEFT JOIN command_response r ON r.command_id::text = c.id::text;

*Query that uses the DB view:*

SELECT id, start_time

FROM job

order by id LIMIT 101 OFFSET 0;

Explain plan: https://explain.depesz.com/s/gzjQ

*Query using the raw SQL*

<SQL from Job DB View definition>

ORDER BY id LIMIT 101 OFFSET 0;

Explain plan:https://explain.depesz.com/s/KgwO

On Fri, Mar 29, 2019 at 11:26 AM Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> 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
>