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