pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feeddoes max_connections affect the query planner
4+ messages / 3 participants
[nested] [flat]
* does max_connections affect the query planner
@ 2019-09-17 08:40 Bob Jolliffe <bobjolliffe@gmail.com>
2019-09-17 14:13 ` Re: does max_connections affect the query planner Tom Lane <tgl@sss.pgh.pa.us>
2019-09-17 14:25 ` Re: does max_connections affect the query planner Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Bob Jolliffe @ 2019-09-17 08:40 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hi
We are trying to diagnose why postgres might be making poor decisions
regarding query plans. One theory is that it does not assume it has
the memory suggested in effective_cache_size.
We do know that max_connections is set quite high (600) when we don't
really expect more than 100. I wonder does the planner take
max_connections x work_mem into account when considering the memory it
has potentially available?
Regards
Bob
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: does max_connections affect the query planner
2019-09-17 08:40 does max_connections affect the query planner Bob Jolliffe <bobjolliffe@gmail.com>
@ 2019-09-17 14:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-09-17 14:15 ` Re: does max_connections affect the query planner Bob Jolliffe <bobjolliffe@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2019-09-17 14:13 UTC (permalink / raw)
To: Bob Jolliffe <bobjolliffe@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
Bob Jolliffe <bobjolliffe@gmail.com> writes:
> We do know that max_connections is set quite high (600) when we don't
> really expect more than 100. I wonder does the planner take
> max_connections x work_mem into account when considering the memory it
> has potentially available?
No. There have been discussions to the effect that it ought to have
a more holistic view about available memory; but nothing's been done
about that, and certainly no existing release does so.
Usually the proximate cause of bad plan choices is bad rowcount
estimates --- you can spot that by comparing estimated and actual
rowcounts in EXPLAIN ANALYZE results.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: does max_connections affect the query planner
2019-09-17 08:40 does max_connections affect the query planner Bob Jolliffe <bobjolliffe@gmail.com>
2019-09-17 14:13 ` Re: does max_connections affect the query planner Tom Lane <tgl@sss.pgh.pa.us>
@ 2019-09-17 14:15 ` Bob Jolliffe <bobjolliffe@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Bob Jolliffe @ 2019-09-17 14:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance@lists.postgresql.org
Thanks Tom. Will check that.
On Tue, 17 Sep 2019 at 14:13, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Bob Jolliffe <bobjolliffe@gmail.com> writes:
> > We do know that max_connections is set quite high (600) when we don't
> > really expect more than 100. I wonder does the planner take
> > max_connections x work_mem into account when considering the memory it
> > has potentially available?
>
> No. There have been discussions to the effect that it ought to have
> a more holistic view about available memory; but nothing's been done
> about that, and certainly no existing release does so.
>
> Usually the proximate cause of bad plan choices is bad rowcount
> estimates --- you can spot that by comparing estimated and actual
> rowcounts in EXPLAIN ANALYZE results.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: does max_connections affect the query planner
2019-09-17 08:40 does max_connections affect the query planner Bob Jolliffe <bobjolliffe@gmail.com>
@ 2019-09-17 14:25 ` Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jeff Janes @ 2019-09-17 14:25 UTC (permalink / raw)
To: Bob Jolliffe <bobjolliffe@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Tue, Sep 17, 2019 at 4:41 AM Bob Jolliffe <bobjolliffe@gmail.com> wrote:
> Hi
>
> We are trying to diagnose why postgres might be making poor decisions
> regarding query plans. One theory is that it does not assume it has
> the memory suggested in effective_cache_size.
> We do know that max_connections is set quite high (600) when we don't
> really expect more than 100. I wonder does the planner take
> max_connections x work_mem into account when considering the memory it
> has potentially available?
>
No, it doesn't try to guess how many connections might be sharing
effective_cache_size. It assumes the entire thing is available to any use
at any given time.
But it is only used for cases where a single query is going to be accessing
blocks over and over again--it estimates that the block will still be in
cache on subsequent visits. But this doesn't work for blocks visited
repeatedly in different queries, either on the same connection or different
ones. There is no notion that some objects might be hotter than others,
other than within one query.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2019-09-17 14:25 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-09-17 08:40 does max_connections affect the query planner Bob Jolliffe <bobjolliffe@gmail.com>
2019-09-17 14:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-09-17 14:15 ` Bob Jolliffe <bobjolliffe@gmail.com>
2019-09-17 14:25 ` Jeff Janes <jeff.janes@gmail.com>
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