public inbox for [email protected]  

help / color / mirror / Atom feed

From: Greg Sabino Mullane <[email protected]>
To: yudhi s <[email protected]>
Cc: Philip Semanchuk <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Suggestion for memory parameters
Date: Tue, 1 Oct 2024 08:00:07 -0400
Message-ID: <CAKAnmmJSfw92MDa6TDxJ3e63AE=1gaZFJinFdHbkFQh=KrA7BQ@mail.gmail.com> (raw)
In-Reply-To: <CAEzWdqc1eTH43ok0xuv-kTrWeEjVxXc2rcEEAcz5FeG2HBoFWw@mail.gmail.com>
References: <CAEzWdqfUQuKtpqGAwf86dwkjPq2Kkeyj6Pw31GXr92YC8M2Y5g@mail.gmail.com>
	<[email protected]>
	<CAEzWdqc1eTH43ok0xuv-kTrWeEjVxXc2rcEEAcz5FeG2HBoFWw@mail.gmail.com>

On Tue, Oct 1, 2024 at 2:52 AM yudhi s <[email protected]> wrote:

> When I execute the query with explain (analyze, buffers),I see the section
> below in the plan having "sort method" information in three places
> each showing ~75MB size, which if combined is coming <250MB. So , does that
> mean it's enough to set the work_mem as ~250MB for these queries before
> they start?
>

work_mem is set per action, so you don't need to usually combine them.
However, these are parallel workers, so you probably need to account for
the case in which no workers are available, in which case you DO want to
combine the values - but only for parallel workers all doing the same
action.


>  But yes somehow this query is finished in a few seconds when i execute
> using explain(analyze,buffers) while if i run it without using explain it
> runs for ~10minutes+. My expectation was that doing (explain analyze)
> should actually execute the query fully. Is my understanding correct here
> and if the disk spilling stats which I am seeing is accurate enough to go
> with?
>

Running explain analyze does indeed run the actual query, but it also
throws away the output. It looks like your limit is set to 300,000 rows
(why!??), which could account for some or all of the time taken - to pass
back those rows and for your client to process them. But it's hard to say
if that's the total reason for the difference without more data. It might
help to see the query, but as a rule of thumb, don't use SELECT * and keep
your LIMIT sane - only pull back the columns and rows your application
absolutely needs.

Cheers,
Greg

reply

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: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Suggestion for memory parameters
  In-Reply-To: <CAKAnmmJSfw92MDa6TDxJ3e63AE=1gaZFJinFdHbkFQh=KrA7BQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox