Обсуждение: LIMIT OFFSET with DB view vs plain SQL

Поиск
Список
Период
Сортировка

LIMIT OFFSET with DB view vs plain SQL

От
Raj Gandhi
Дата:

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

Re: LIMIT OFFSET with DB view vs plain SQL

От
Raj Gandhi
Дата:
+ pgsql-performance 

On Thu, Mar 28, 2019 at 6:41 PM Raj Gandhi <raj01gandhi@gmail.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

Re: LIMIT OFFSET with DB view vs plain SQL

От
Raj Gandhi
Дата:
+ pgsql-performance 

On Thu, Mar 28, 2019 at 6:41 PM Raj Gandhi <raj01gandhi@gmail.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

Re: LIMIT OFFSET with DB view vs plain SQL

От
Laurenz Albe
Дата:
Raj Gandhi wrote:
> I’m using LIMIT offset with DB view. Looks like query planner is applying the LIMIT for DB view at the end after
processingall 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?

Please show

- the view definition
- the query on the view
- the query without the view

Yours,
Laurenz Albe
-- 
+43-670-6056265
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Web: https://www.cybertec-postgresql.com

Re: LIMIT OFFSET with DB view vs plain SQL

От
Merlin Moncure
Дата:
On Thu, Mar 28, 2019 at 5:44 PM Raj Gandhi <raj01gandhi@gmail.com> wrote:
>
> + pgsql-performance
>
> On Thu, Mar 28, 2019 at 6:41 PM Raj Gandhi <raj01gandhi@gmail.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
processingall 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

Re: LIMIT OFFSET with DB view vs plain SQL

От
Merlin Moncure
Дата:
On Thu, Mar 28, 2019 at 5:44 PM Raj Gandhi <raj01gandhi@gmail.com> wrote:
>
> + pgsql-performance
>
> On Thu, Mar 28, 2019 at 6:41 PM Raj Gandhi <raj01gandhi@gmail.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
processingall 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

Re: LIMIT OFFSET with DB view vs plain SQL

От
Raj Gandhi
Дата:
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@gmail.com> wrote:
On Thu, Mar 28, 2019 at 5:44 PM Raj Gandhi <raj01gandhi@gmail.com> wrote:
>
> + pgsql-performance
>
> On Thu, Mar 28, 2019 at 6:41 PM Raj Gandhi <raj01gandhi@gmail.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

Re: LIMIT OFFSET with DB view vs plain SQL

От
Raj Gandhi
Дата:
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@gmail.com> wrote:
On Thu, Mar 28, 2019 at 5:44 PM Raj Gandhi <raj01gandhi@gmail.com> wrote:
>
> + pgsql-performance
>
> On Thu, Mar 28, 2019 at 6:41 PM Raj Gandhi <raj01gandhi@gmail.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

Re: LIMIT OFFSET with DB view vs plain SQL

От
Raj Gandhi
Дата:
Any other idea how to resolve the performance issue with the database view?

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

Re: LIMIT OFFSET with DB view vs plain SQL

От
Raj Gandhi
Дата:
Any other idea how to resolve the performance issue with the database view?

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

Re: LIMIT OFFSET with DB view vs plain SQL

От
Raj Gandhi
Дата:
Thanks Rui. The performance of using function is close to the plain SQL.

Why Query planner is choosing different path with DB view?


explain analyze select foo(101,0);
                                           QUERY PLAN                                          
------------------------------------------------------------------------------------------------
 ProjectSet  (cost=0.00..5.27 rows=1000 width=32) (actual time=10.340..10.374 rows=101 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
 Planning time: 0.035 ms
 Execution time: 10.436 ms
(4 rows)



On Mon, Apr 1, 2019 at 4:14 PM Rui DeSousa <rui@crazybean.net> wrote:
Try using a function that returns the result set.

i.e. 

create or replace function foo(_limit int, _offset int)
  returns setof sample_table
as $$
begin
  return query
    select * 
    from sample_table 
    order by created_date
    limit _limit 
    offset _offset
  ;
end;
$$ language plpgsql
  volatile
;  


Given your query; return a table instead of a set. i.e.: 

returns table (
   id int
  , parent_id int
  .
  .
  . 
  , response_body text
)
as $$


Query example: 

select * from foo(100, 50);


On Apr 1, 2019, at 9:56 AM, Raj Gandhi <raj01gandhi@gmail.com> wrote:

Any other idea how to resolve the performance issue with the database view?

On Fri, Mar 29, 2019 at 7:38 PM Raj Gandhi <raj01gandhi@gmail.com> wrote:
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;

 


 Query using the raw SQL
<SQL from Job DB View definition>
ORDER BY id LIMIT 101 OFFSET 0;

 


 


On Fri, Mar 29, 2019 at 11:26 AM Merlin Moncure <mmoncure@gmail.com> wrote:
On Thu, Mar 28, 2019 at 5:44 PM Raj Gandhi <raj01gandhi@gmail.com> wrote:
>
> + pgsql-performance
>
> On Thu, Mar 28, 2019 at 6:41 PM Raj Gandhi <raj01gandhi@gmail.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

Re: LIMIT OFFSET with DB view vs plain SQL

От
Raj Gandhi
Дата:
Thanks Rui. The performance of using function is close to the plain SQL.

Why Query planner is choosing different path with DB view?


explain analyze select foo(101,0);
                                           QUERY PLAN                                          
------------------------------------------------------------------------------------------------
 ProjectSet  (cost=0.00..5.27 rows=1000 width=32) (actual time=10.340..10.374 rows=101 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
 Planning time: 0.035 ms
 Execution time: 10.436 ms
(4 rows)



On Mon, Apr 1, 2019 at 4:14 PM Rui DeSousa <rui@crazybean.net> wrote:
Try using a function that returns the result set.

i.e. 

create or replace function foo(_limit int, _offset int)
  returns setof sample_table
as $$
begin
  return query
    select * 
    from sample_table 
    order by created_date
    limit _limit 
    offset _offset
  ;
end;
$$ language plpgsql
  volatile
;  


Given your query; return a table instead of a set. i.e.: 

returns table (
   id int
  , parent_id int
  .
  .
  . 
  , response_body text
)
as $$


Query example: 

select * from foo(100, 50);


On Apr 1, 2019, at 9:56 AM, Raj Gandhi <raj01gandhi@gmail.com> wrote:

Any other idea how to resolve the performance issue with the database view?

On Fri, Mar 29, 2019 at 7:38 PM Raj Gandhi <raj01gandhi@gmail.com> wrote:
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;

 


 Query using the raw SQL
<SQL from Job DB View definition>
ORDER BY id LIMIT 101 OFFSET 0;

 


 


On Fri, Mar 29, 2019 at 11:26 AM Merlin Moncure <mmoncure@gmail.com> wrote:
On Thu, Mar 28, 2019 at 5:44 PM Raj Gandhi <raj01gandhi@gmail.com> wrote:
>
> + pgsql-performance
>
> On Thu, Mar 28, 2019 at 6:41 PM Raj Gandhi <raj01gandhi@gmail.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

Re: LIMIT OFFSET with DB view vs plain SQL

От
SAMEER KUMAR
Дата:


Thanks,
Sameer
+65 81100350
Please consider the environment before printing this e-mail!



On Mon, Apr 1, 2019 at 9:57 PM Raj Gandhi <raj01gandhi@gmail.com> wrote:
Any other idea how to resolve the performance issue with the database view?

On Fri, Mar 29, 2019 at 7:38 PM Raj Gandhi <raj01gandhi@gmail.com> wrote:
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


I think the row count on both you explain plan does not go well with what was anticipated by the planner. 

can you run analyze on all the tables in your view query and try both the queries again?

 


 


On Fri, Mar 29, 2019 at 11:26 AM Merlin Moncure <mmoncure@gmail.com> wrote:
On Thu, Mar 28, 2019 at 5:44 PM Raj Gandhi <raj01gandhi@gmail.com> wrote:
>
> + pgsql-performance
>
> On Thu, Mar 28, 2019 at 6:41 PM Raj Gandhi <raj01gandhi@gmail.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

Re: LIMIT OFFSET with DB view vs plain SQL

От
SAMEER KUMAR
Дата:


Thanks,
Sameer
+65 81100350
Please consider the environment before printing this e-mail!



On Mon, Apr 1, 2019 at 9:57 PM Raj Gandhi <raj01gandhi@gmail.com> wrote:
Any other idea how to resolve the performance issue with the database view?

On Fri, Mar 29, 2019 at 7:38 PM Raj Gandhi <raj01gandhi@gmail.com> wrote:
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


I think the row count on both you explain plan does not go well with what was anticipated by the planner. 

can you run analyze on all the tables in your view query and try both the queries again?

 


 


On Fri, Mar 29, 2019 at 11:26 AM Merlin Moncure <mmoncure@gmail.com> wrote:
On Thu, Mar 28, 2019 at 5:44 PM Raj Gandhi <raj01gandhi@gmail.com> wrote:
>
> + pgsql-performance
>
> On Thu, Mar 28, 2019 at 6:41 PM Raj Gandhi <raj01gandhi@gmail.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

Re: LIMIT OFFSET with DB view vs plain SQL

От
Ramón Bastidas
Дата:
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.. 



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@gmail.com> wrote:
+ pgsql-performance 

On Thu, Mar 28, 2019 at 6:41 PM Raj Gandhi <raj01gandhi@gmail.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

Re: LIMIT OFFSET with DB view vs plain SQL

От
Ramón Bastidas
Дата:
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.. 



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@gmail.com> wrote:
+ pgsql-performance 

On Thu, Mar 28, 2019 at 6:41 PM Raj Gandhi <raj01gandhi@gmail.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