pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedLIMIT OFFSET with DB view vs plain SQL
9+ messages / 5 participants
[nested] [flat]
* LIMIT OFFSET with DB view vs plain SQL
@ 2019-03-28 22:41 Raj Gandhi <raj01gandhi@gmail.com>
2019-03-28 22:43 ` Re: LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
2019-03-29 13:53 ` Re: LIMIT OFFSET with DB view vs plain SQL Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 9+ messages in thread
From: Raj Gandhi @ 2019-03-28 22:41 UTC (permalink / raw)
To: pgsql-admin@postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: LIMIT OFFSET with DB view vs plain SQL
2019-03-28 22:41 LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
@ 2019-03-28 22:43 ` Raj Gandhi <raj01gandhi@gmail.com>
2019-03-29 15:28 ` Re: LIMIT OFFSET with DB view vs plain SQL Merlin Moncure <mmoncure@gmail.com>
2019-04-02 04:49 ` Re: LIMIT OFFSET with DB view vs plain SQL Ramón Bastidas <ramon.r.bastidas@gmail.com>
1 sibling, 2 replies; 9+ messages in thread
From: Raj Gandhi @ 2019-03-28 22:43 UTC (permalink / raw)
To: pgsql-admin@postgresql.org; +Cc: pgsql-performance
+ 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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: LIMIT OFFSET with DB view vs plain SQL
2019-03-28 22:41 LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
2019-03-28 22:43 ` Re: LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
@ 2019-03-29 15:28 ` Merlin Moncure <mmoncure@gmail.com>
2019-03-29 23:38 ` Re: LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Merlin Moncure @ 2019-03-29 15:28 UTC (permalink / raw)
To: Raj Gandhi <raj01gandhi@gmail.com>; +Cc: admin <pgsql-admin@postgresql.org>; pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: LIMIT OFFSET with DB view vs plain SQL
2019-03-28 22:41 LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
2019-03-28 22:43 ` Re: LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
2019-03-29 15:28 ` Re: LIMIT OFFSET with DB view vs plain SQL Merlin Moncure <mmoncure@gmail.com>
@ 2019-03-29 23:38 ` Raj Gandhi <raj01gandhi@gmail.com>
2019-04-01 13:56 ` Re: LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Raj Gandhi @ 2019-03-29 23:38 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: admin <pgsql-admin@postgresql.org>; pgsql-performance
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: LIMIT OFFSET with DB view vs plain SQL
2019-03-28 22:41 LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
2019-03-28 22:43 ` Re: LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
2019-03-29 15:28 ` Re: LIMIT OFFSET with DB view vs plain SQL Merlin Moncure <mmoncure@gmail.com>
2019-03-29 23:38 ` Re: LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
@ 2019-04-01 13:56 ` Raj Gandhi <raj01gandhi@gmail.com>
2019-04-02 02:06 ` Re: LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
2019-04-02 04:16 ` Re: LIMIT OFFSET with DB view vs plain SQL SAMEER KUMAR <sameer.kasi200x@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Raj Gandhi @ 2019-04-01 13:56 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: admin <pgsql-admin@postgresql.org>; pgsql-performance
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
>>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: LIMIT OFFSET with DB view vs plain SQL
2019-03-28 22:41 LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
2019-03-28 22:43 ` Re: LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
2019-03-29 15:28 ` Re: LIMIT OFFSET with DB view vs plain SQL Merlin Moncure <mmoncure@gmail.com>
2019-03-29 23:38 ` Re: LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
2019-04-01 13:56 ` Re: LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
@ 2019-04-02 02:06 ` Raj Gandhi <raj01gandhi@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Raj Gandhi @ 2019-04-02 02:06 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: Merlin Moncure <mmoncure@gmail.com>; admin <pgsql-admin@postgresql.org>; pgsql-performance
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;
>>
>>
>> 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
>>>
>>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: LIMIT OFFSET with DB view vs plain SQL
2019-03-28 22:41 LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
2019-03-28 22:43 ` Re: LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
2019-03-29 15:28 ` Re: LIMIT OFFSET with DB view vs plain SQL Merlin Moncure <mmoncure@gmail.com>
2019-03-29 23:38 ` Re: LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
2019-04-01 13:56 ` Re: LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
@ 2019-04-02 04:16 ` SAMEER KUMAR <sameer.kasi200x@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: SAMEER KUMAR @ 2019-04-02 04:16 UTC (permalink / raw)
To: Raj Gandhi <raj01gandhi@gmail.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; admin <pgsql-admin@postgresql.org>; pgsql-performance
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
>>>
>>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: LIMIT OFFSET with DB view vs plain SQL
2019-03-28 22:41 LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
2019-03-28 22:43 ` Re: LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
@ 2019-04-02 04:49 ` Ramón Bastidas <ramon.r.bastidas@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Ramón Bastidas @ 2019-04-02 04:49 UTC (permalink / raw)
To: Raj Gandhi <raj01gandhi@gmail.com>; +Cc: pgsql-admin@postgresql.org; pgsql-performance
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@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
>>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: LIMIT OFFSET with DB view vs plain SQL
2019-03-28 22:41 LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
@ 2019-03-29 13:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 9+ messages in thread
From: Laurenz Albe @ 2019-03-29 13:53 UTC (permalink / raw)
To: Raj Gandhi <raj01gandhi@gmail.com>; pgsql-admin@postgresql.org
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 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?
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2019-04-02 04:49 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-03-28 22:41 LIMIT OFFSET with DB view vs plain SQL Raj Gandhi <raj01gandhi@gmail.com>
2019-03-28 22:43 ` Raj Gandhi <raj01gandhi@gmail.com>
2019-03-29 15:28 ` Merlin Moncure <mmoncure@gmail.com>
2019-03-29 23:38 ` Raj Gandhi <raj01gandhi@gmail.com>
2019-04-01 13:56 ` Raj Gandhi <raj01gandhi@gmail.com>
2019-04-02 02:06 ` Raj Gandhi <raj01gandhi@gmail.com>
2019-04-02 04:16 ` SAMEER KUMAR <sameer.kasi200x@gmail.com>
2019-04-02 04:49 ` Ramón Bastidas <ramon.r.bastidas@gmail.com>
2019-03-29 13:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox