From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Raj Gandhi <raj01gandhi@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: LIMIT OFFSET with DB view vs plain SQL
Date: Fri, 29 Mar 2019 14:53:10 +0100
Message-ID: <1bbde4f2929ef4f5cf145ccffaca3d3f6e27a7bd.camel@cybertec.at> (raw)
In-Reply-To: <CALU_HCMxM5m-Sxsn3fwhqwu23b3O2q+Rviv-fxdJM1EXG=A3fg@mail.gmail.com>
References: <CALU_HCMxM5m-Sxsn3fwhqwu23b3O2q+Rviv-fxdJM1EXG=A3fg@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, raj01gandhi@gmail.com, pgsql-admin@postgresql.org
Subject: Re: LIMIT OFFSET with DB view vs plain SQL
In-Reply-To: <1bbde4f2929ef4f5cf145ccffaca3d3f6e27a7bd.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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