On Thu, Aug 29, 2013 at 8:39 AM, wrote:
> Can anyone offer suggestions on how I can optimize a query that contains
> the LIMIT OFFSET clause?
>
> The explain plan of the query is included in the notepad attachment.
>
> thanks
>
>
Before I write anything, I should warn that it has been a while sin
On Wed, Aug 28, 2013 at 3:39 PM, wrote:
> Can anyone offer suggestions on how I can optimize a query that contains the
> LIMIT OFFSET clause?
>
> The explain plan of the query is included in the notepad attachment.
OFFSET is working as designed (that is, slowly). Managing pagination
with OFFSET
Hi Greg,The labor_task_report table is already Partitioned by this_.work_date_time and this table contains approx. 15 billion rows. The other table labor_tasks is not partitioned. I'm thinking that the size of the external sort is part of the problem. if I remove the labor_tasks table from the SQL,
Two solutions come to mind. First possibility is table partitioning on the
column you're sorting. Second, depending on your application, is to use a
cursor. Cursor won't help with web applications however a stateful
application could benefit.
HTH
-Greg
On Wed, Aug 28, 2013 at 2:39 PM, wrote:
Can anyone offer suggestions on how I can optimize a query that contains the LIMIT OFFSET clause?The explain plan of the query is included in the notepad attachment.thanksThe rows skipped by an OFFSET clause still have to be computed inside the
server; therefore a large OFFSET might be inefficient
Rafael Martinez writes:
> We have a SQL statement that with 9.1 takes ca 4000ms to finnish and
> with 9.2 over 22000ms.
> ...
> We can see that the query plan is very different between versions and
> that 9.2 is really wrong with the number of rows involved. Why is 9.2
> taking so wrong about the
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 08/28/2013 06:10 AM, Jeff Janes wrote:
> On Monday, August 26, 2013, Rafael Martinez wrote:
Hei
>
> Could you do explain (analyze, buffers) of these?
>
With 9.1:
http://explain.depesz.com/s/FMe
with 9.2:
http://explain.depesz.com/s/Z1j
>
>
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 08/27/2013 11:27 PM, Tomas Vondra wrote:
[]
> I don't immediately see where's the problem - maybe some other
> hacker on this list can. Can you prepare a testcase for this? I.e.
> a structure of the tables + data so that we can reproduc
