($INBOX_DIR/description missing)
help / color / mirror / Atom feedStartup cost of sequential scan
21+ messages / 9 participants
[nested] [flat]
* Startup cost of sequential scan
@ 2018-08-30 13:56 Alexander Korotkov <[email protected]>
0 siblings, 1 reply; 21+ messages in thread
From: Alexander Korotkov @ 2018-08-30 13:56 UTC (permalink / raw)
To: pgsql-hackers
Hi!
Our customer have a bad plan problem, which could be reduced to the
following example.
create table t1 (id int primary key, k int);
create table t2 (id int);
insert into t1 (select i, i from generate_series(1,1000000) i);
insert into t2 (select 0 from generate_series(1,100)i);
insert into t2 values (500000);
For the following query the plan is OK despite number of resulting
rows is very much overestimated.
# explain select * from
t1 join t2 x1 on x1.id = t1.k
join t2 x2 on x2.id = t1.k
join t2 x3 on x3.id = t1.k
join t2 x4 on x4.id = t1.k
join t2 x5 on x5.id = t1.k
join t2 x6 on x6.id = t1.k
join t2 x7 on x7.id = t1.k
join t2 x8 on x8.id = t1.k
join t2 x9 on x9.id = t1.k
where t1.id = 500000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=265.98..104071348014760.11 rows=9240411823550620 width=44)
Hash Cond: (x1.id = x8.id)
-> Hash Join (cost=22.80..25.20 rows=942425865760 width=36)
Hash Cond: (x1.id = t1.k)
-> Seq Scan on t2 x1 (cost=0.00..2.01 rows=101 width=4)
-> Hash (cost=22.79..22.79 rows=1 width=32)
-> Hash Join (cost=20.39..22.79 rows=1 width=32)
Hash Cond: (x7.id = t1.k)
-> Seq Scan on t2 x7 (cost=0.00..2.01 rows=101 width=4)
-> Hash (cost=20.38..20.38 rows=1 width=28)
-> Hash Join (cost=17.98..20.38 rows=1 width=28)
Hash Cond: (x6.id = t1.k)
-> Seq Scan on t2 x6
(cost=0.00..2.01 rows=101 width=4)
-> Hash (cost=17.96..17.96 rows=1 width=24)
-> Hash Join
(cost=15.57..17.96 rows=1 width=24)
Hash Cond: (x5.id = t1.k)
-> Seq Scan on t2 x5
(cost=0.00..2.01 rows=101 width=4)
-> Hash
(cost=15.55..15.55 rows=1 width=20)
-> Hash Join
(cost=13.15..15.55 rows=1 width=20)
Hash Cond:
(x4.id = t1.k)
-> Seq Scan
on t2 x4 (cost=0.00..2.01 rows=101 width=4)
-> Hash
(cost=13.14..13.14 rows=1 width=16)
->
Hash Join (cost=10.74..13.14 rows=1 width=16)
Hash Cond: (x3.id = t1.k)
-> Seq Scan on t2 x3 (cost=0.00..2.01 rows=101 width=4)
-> Hash (cost=10.73..10.73 rows=1 width=12)
-> Hash Join (cost=8.46..10.73 rows=1 width=12)
Hash Cond: (x2.id = t1.k)
-> Seq Scan on t2 x2 (cost=0.00..2.01 rows=101 width=4)
-> Hash (cost=8.44..8.44 rows=1 width=8)
-> Index Scan using t1_pkey on t1 (cost=0.42..8.44
rows=1 width=8)
Index Cond: (id = 500000)
-> Hash (cost=118.17..118.17 rows=10001 width=8)
-> Hash Join (cost=3.27..118.17 rows=10001 width=8)
Hash Cond: (x8.id = x9.id)
-> Seq Scan on t2 x8 (cost=0.00..2.01 rows=101 width=4)
-> Hash (cost=2.01..2.01 rows=101 width=4)
-> Seq Scan on t2 x9 (cost=0.00..2.01 rows=101 width=4)
(38 rows)
But when you add LIMIT clause, index scan on t1 turns out into sequential scan.
# explain select * from
t1 join t2 x1 on x1.id = t1.k
join t2 x2 on x2.id = t1.k
join t2 x3 on x3.id = t1.k
join t2 x4 on x4.id = t1.k
join t2 x5 on x5.id = t1.k
join t2 x6 on x6.id = t1.k
join t2 x7 on x7.id = t1.k
join t2 x8 on x8.id = t1.k
join t2 x9 on x9.id = t1.k
where t1.id = 500000 limit 100;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..1.55 rows=100 width=44)
-> Nested Loop (cost=0.00..142805795187416.44
rows=9240411823550620 width=44)
Join Filter: (x1.id = x9.id)
-> Nested Loop (cost=0.00..1427775203576.57
rows=93318825071840 width=40)
Join Filter: (x1.id = x8.id)
-> Nested Loop (cost=0.00..16947.91 rows=942425865760 width=36)
Join Filter: (t1.k = x1.id)
-> Nested Loop (cost=0.00..16944.63 rows=1 width=32)
Join Filter: (t1.k = x7.id)
-> Nested Loop (cost=0.00..16941.36
rows=1 width=28)
Join Filter: (t1.k = x6.id)
-> Nested Loop (cost=0.00..16938.09
rows=1 width=24)
Join Filter: (t1.k = x5.id)
-> Nested Loop
(cost=0.00..16934.82 rows=1 width=20)
Join Filter: (t1.k = x4.id)
-> Nested Loop
(cost=0.00..16931.54 rows=1 width=16)
Join Filter: (t1.k = x3.id)
-> Nested Loop
(cost=0.00..16928.27 rows=1 width=12)
Join Filter:
(t1.k = x2.id)
-> Seq Scan
on t1 (cost=0.00..16925.00 rows=1 width=8)
Filter:
(id = 500000)
-> Seq Scan
on t2 x2 (cost=0.00..2.01 rows=101 width=4)
-> Seq Scan on t2
x3 (cost=0.00..2.01 rows=101 width=4)
-> Seq Scan on t2 x4
(cost=0.00..2.01 rows=101 width=4)
-> Seq Scan on t2 x5
(cost=0.00..2.01 rows=101 width=4)
-> Seq Scan on t2 x6
(cost=0.00..2.01 rows=101 width=4)
-> Seq Scan on t2 x7 (cost=0.00..2.01
rows=101 width=4)
-> Seq Scan on t2 x1 (cost=0.00..2.01 rows=101 width=4)
-> Materialize (cost=0.00..2.51 rows=101 width=4)
-> Seq Scan on t2 x8 (cost=0.00..2.01 rows=101 width=4)
-> Materialize (cost=0.00..2.51 rows=101 width=4)
-> Seq Scan on t2 x9 (cost=0.00..2.01 rows=101 width=4)
(32 rows)
Obviously, that happens because selectivity for join with t2 is
overestimated and multiplied many times. And improvements in this
area seems quite hard for me.
But I think there is another issue in sequential scan cost. We have
zero startup cost for sequential scan. But why? As I get sequential
scan should estimate amount of resources we need to spend in order to
start returning rows. So, in our example when we expect finding 1 row
from the table, in average we have to scan half of table before find
this row. Thus, startup_cost should be about half of total cost.
Attached POC patch implements that. In the given example sequential
scan turns out back to index scan.
Any thoughts?
------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachments:
[application/octet-stream] seq_scan_startup_cost.path (472B, ../../CAPpHfdvfDAXPXhFQT3Ww=kZ4tpyAsD07_oz8-fh0=p6eckEBKQ@mail.gmail.com/2-seq_scan_startup_cost.path)
download
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Startup cost of sequential scan
@ 2018-08-30 14:04 Tom Lane <[email protected]>
parent: Alexander Korotkov <[email protected]>
0 siblings, 2 replies; 21+ messages in thread
From: Tom Lane @ 2018-08-30 14:04 UTC (permalink / raw)
To: Alexander Korotkov <[email protected]>; +Cc: pgsql-hackers
Alexander Korotkov <[email protected]> writes:
> But I think there is another issue in sequential scan cost. We have
> zero startup cost for sequential scan. But why?
Because it's what the mental model of startup cost says it should be.
Also, I do not think we can change that without a whole lot of unpleasant
side effects on cases that work well today. Have you even checked to
see how much of the regression tests break with this change?
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Startup cost of sequential scan
@ 2018-08-30 14:31 Alexander Korotkov <[email protected]>
parent: Tom Lane <[email protected]>
1 sibling, 1 reply; 21+ messages in thread
From: Alexander Korotkov @ 2018-08-30 14:31 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-hackers
--00000000000056ddc00574a7ef70
Content-Type: text/plain; charset="UTF-8"
On Thu, Aug 30, 2018 at 5:05 PM Tom Lane <[email protected]> wrote:
> Alexander Korotkov <[email protected]> writes:
> > But I think there is another issue in sequential scan cost. We have
> > zero startup cost for sequential scan. But why?
>
> Because it's what the mental model of startup cost says it should be.
Right. So as I understand. The model is that we start sequential
scan immediately, and then find one row uniformly distributed over the
whole table.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Startup cost of sequential scan
@ 2018-08-30 14:58 Tom Lane <[email protected]>
parent: Alexander Korotkov <[email protected]>
0 siblings, 2 replies; 21+ messages in thread
From: Tom Lane @ 2018-08-30 14:58 UTC (permalink / raw)
To: Alexander Korotkov <[email protected]>; +Cc: pgsql-hackers
Alexander Korotkov <[email protected]> writes:
> On Thu, Aug 30, 2018 at 5:05 PM Tom Lane <[email protected]> wrote:
>> Because it's what the mental model of startup cost says it should be.
> From this model we make a conclusion that we're starting getting rows
> from sequential scan sooner than from index scan. And this conclusion
> doesn't reflect reality.
No, startup cost is not the "time to find the first row". It's overhead
paid before you even get to start examining rows.
I'm disinclined to consider fundamental changes to our costing model
on the basis of this example. The fact that the rowcount estimates are
so far off reality means that you're basically looking at "garbage in,
garbage out" for the cost calculations --- and applying a small LIMIT
just magnifies that.
It'd be more useful to think first about how to make the selectivity
estimates better; after that, we might or might not still think there's
a costing issue.
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Startup cost of sequential scan
@ 2018-08-30 15:08 Konstantin Knizhnik <[email protected]>
parent: Tom Lane <[email protected]>
1 sibling, 2 replies; 21+ messages in thread
From: Konstantin Knizhnik @ 2018-08-30 15:08 UTC (permalink / raw)
To: [email protected]
On 30.08.2018 17:58, Tom Lane wrote:
> Alexander Korotkov <[email protected]> writes:
>> On Thu, Aug 30, 2018 at 5:05 PM Tom Lane <[email protected]> wrote:
>>> Because it's what the mental model of startup cost says it should be.
>> From this model we make a conclusion that we're starting getting rows
>> from sequential scan sooner than from index scan. And this conclusion
>> doesn't reflect reality.
> No, startup cost is not the "time to find the first row". It's overhead
> paid before you even get to start examining rows.
But it seems to me that calculation of cost in LIMIT node contradicts
with this statement:
pathnode->path.startup_cost +=
(subpath->total_cost - subpath->startup_cost)
* offset_rows / subpath->rows;
>
> I'm disinclined to consider fundamental changes to our costing model
> on the basis of this example. The fact that the rowcount estimates are
> so far off reality means that you're basically looking at "garbage in,
> garbage out" for the cost calculations --- and applying a small LIMIT
> just magnifies that.
>
> It'd be more useful to think first about how to make the selectivity
> estimates better; after that, we might or might not still think there's
> a costing issue.
>
> regards, tom lane
>
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Startup cost of sequential scan
@ 2018-08-30 15:23 Alexander Korotkov <[email protected]>
parent: Konstantin Knizhnik <[email protected]>
1 sibling, 0 replies; 21+ messages in thread
From: Alexander Korotkov @ 2018-08-30 15:23 UTC (permalink / raw)
To: konstantin knizhnik <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Thu, Aug 30, 2018 at 6:08 PM Konstantin Knizhnik
<[email protected]> wrote:
> On 30.08.2018 17:58, Tom Lane wrote:
> > Alexander Korotkov <[email protected]> writes:
> >> On Thu, Aug 30, 2018 at 5:05 PM Tom Lane <[email protected]> wrote:
> >>> Because it's what the mental model of startup cost says it should be.
> >> From this model we make a conclusion that we're starting getting rows
> >> from sequential scan sooner than from index scan. And this conclusion
> >> doesn't reflect reality.
> > No, startup cost is not the "time to find the first row". It's overhead
> > paid before you even get to start examining rows.
> But it seems to me that calculation of cost in LIMIT node contradicts
> with this statement:
>
> pathnode->path.startup_cost +=
> (subpath->total_cost - subpath->startup_cost)
> * offset_rows / subpath->rows;
Why does it contradict? It just assumes that skipping OFFSET rows to
be preliminary work before returning results rows...
------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Startup cost of sequential scan
@ 2018-08-30 15:23 Andrew Gierth <[email protected]>
parent: Konstantin Knizhnik <[email protected]>
1 sibling, 1 reply; 21+ messages in thread
From: Andrew Gierth @ 2018-08-30 15:23 UTC (permalink / raw)
To: Konstantin Knizhnik <[email protected]>; +Cc: [email protected]
>>>>> "Konstantin" == Konstantin Knizhnik <[email protected]> writes:
>> No, startup cost is not the "time to find the first row". It's
>> overhead paid before you even get to start examining rows.
Konstantin> But it seems to me that calculation of cost in LIMIT node
Konstantin> contradicts with this statement:
The model (assuming I understand it rightly) is that what we're actually
tracking is a startup cost and a per-output-row cost, but for comparison
purposes we actually store the rows and the computed total, rather than
just the per-row cost:
rows
startup_cost
total_cost = startup_cost + (rows * per_row_cost)
So what Limit is doing the for the offset count is recovering the
subpath's per_row_cost from (total_cost - startup_cost)/rows, and then
scaling that by the number of rows in the offset (which are being
discarded), and adding that to the startup cost. So this is saying: the
startup cost for OFFSET N is the startup cost of the subplan, plus the
cost of fetching N rows from the subplan. (And after fetching those N
rows, we still haven't found the first row that we will actually
return.)
For LIMIT N, we instead replace the old total cost with a new one
calculated from the startup cost plus N times the subplan's per-row
cost.
--
Andrew (irc:RhodiumToad)
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Startup cost of sequential scan
@ 2018-08-30 15:33 Tom Lane <[email protected]>
parent: Andrew Gierth <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Tom Lane @ 2018-08-30 15:33 UTC (permalink / raw)
To: Andrew Gierth <[email protected]>; +Cc: Konstantin Knizhnik <[email protected]>; [email protected]
Andrew Gierth <[email protected]> writes:
> The model (assuming I understand it rightly) is that what we're actually
> tracking is a startup cost and a per-output-row cost, but for comparison
> purposes we actually store the rows and the computed total, rather than
> just the per-row cost:
> rows
> startup_cost
> total_cost = startup_cost + (rows * per_row_cost)
Right. I tend to think of it differently: the cost to retrieve the
first K rows out of a total of N is
startup_cost + (total_cost - startup_cost) * K/N
but that comes out to the same thing as what Andrew said.
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Startup cost of sequential scan
@ 2018-08-30 15:38 Alexander Korotkov <[email protected]>
parent: Tom Lane <[email protected]>
1 sibling, 1 reply; 21+ messages in thread
From: Alexander Korotkov @ 2018-08-30 15:38 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-hackers
On Thu, Aug 30, 2018 at 5:58 PM Tom Lane <[email protected]> wrote:
>
> Alexander Korotkov <[email protected]> writes:
> > On Thu, Aug 30, 2018 at 5:05 PM Tom Lane <[email protected]> wrote:
> >> Because it's what the mental model of startup cost says it should be.
>
> > From this model we make a conclusion that we're starting getting rows
> > from sequential scan sooner than from index scan. And this conclusion
> > doesn't reflect reality.
>
> No, startup cost is not the "time to find the first row". It's overhead
> paid before you even get to start examining rows.
>
> I'm disinclined to consider fundamental changes to our costing model
> on the basis of this example. The fact that the rowcount estimates are
> so far off reality means that you're basically looking at "garbage in,
> garbage out" for the cost calculations --- and applying a small LIMIT
> just magnifies that.
>
> It'd be more useful to think first about how to make the selectivity
> estimates better; after that, we might or might not still think there's
> a costing issue.
I understand that startup cost is not "time to find the first row".
But I think this example highlight not one but two issues.
1) Row count estimates for joins are wrong.
2) Rows are assumed to be continuous while in reality they are
discrete. So, if we reverse the assumptions made in LIMIT clause
estimation, we may say that it's basically assuming that we need to
fetch only fraction of row from the sequential scan node. And in the
case we really fetch 101 rows in each join with t2, this logic would
still bring us to the bad plan. And now I'm not proposing go rush
redesigning planner to fix that. I just think it's probably something
worth discussion.
------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Startup cost of sequential scan
@ 2018-08-30 15:55 Tom Lane <[email protected]>
parent: Alexander Korotkov <[email protected]>
0 siblings, 1 reply; 21+ messages in thread
From: Tom Lane @ 2018-08-30 15:55 UTC (permalink / raw)
To: Alexander Korotkov <[email protected]>; +Cc: pgsql-hackers
Alexander Korotkov <[email protected]> writes:
> I understand that startup cost is not "time to find the first row".
> But I think this example highlight not one but two issues.
> 1) Row count estimates for joins are wrong.
Yup.
> 2) Rows are assumed to be continuous while in reality they are
> discrete.
Where do you get that from?
The problem this example is highlighting is mostly just the bad
join size estimate, imo. It's not at all clear that the planner
would still do the wrong thing if that were fixed. In fact,
if I replace the contents of t2 with some other distribution,
such as
insert into t2 (select i from generate_series(1,100)i);
I get a much better join size estimate *and* a sane plan, even
in the LIMIT case. So the problem here is just with the join
size estimate.
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Startup cost of sequential scan
@ 2018-08-30 19:43 Robert Haas <[email protected]>
parent: Tom Lane <[email protected]>
1 sibling, 1 reply; 21+ messages in thread
From: Robert Haas @ 2018-08-30 19:43 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Alexander Korotkov <[email protected]>; pgsql-hackers
On Thu, Aug 30, 2018 at 10:04 AM, Tom Lane <[email protected]> wrote:
> Alexander Korotkov <[email protected]> writes:
>> But I think there is another issue in sequential scan cost. We have
>> zero startup cost for sequential scan. But why?
>
> Because it's what the mental model of startup cost says it should be.
Whose mental model? I guess the Tom Lane mind is the canonical one
for this project, but I'm not sure that it entirely agrees with mine.
IIRC, it was previously proposed that we ought to charge
random_page_cost for the first block of a sequential scan, because at
present the cost of fetching 1 block differs depending on whether we
are fetching it from a heap or an index, which seems unprincipled.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Startup cost of sequential scan
@ 2018-08-30 21:38 Tom Lane <[email protected]>
parent: Robert Haas <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Tom Lane @ 2018-08-30 21:38 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Alexander Korotkov <[email protected]>; pgsql-hackers
Robert Haas <[email protected]> writes:
> Whose mental model? I guess the Tom Lane mind is the canonical one
> for this project, but I'm not sure that it entirely agrees with mine.
Since the fact that we have a notion of startup cost at all is entirely my
fault, I don't feel shy about claiming to have the authoritative view of
what it means.
(Whether that's adequately documented is another question :-()
> IIRC, it was previously proposed that we ought to charge
> random_page_cost for the first block of a sequential scan, because at
> present the cost of fetching 1 block differs depending on whether we
> are fetching it from a heap or an index, which seems unprincipled.
That might well be a sane thing to do ... but it'd still be part of run
cost not startup cost.
regards, tom lane
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: Startup cost of sequential scan
@ 2018-08-31 10:50 Alexander Korotkov <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Alexander Korotkov @ 2018-08-31 10:50 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-hackers
On Thu, Aug 30, 2018 at 6:55 PM Tom Lane <[email protected]> wrote:
> Alexander Korotkov <[email protected]> writes:
> > I understand that startup cost is not "time to find the first row".
> > But I think this example highlight not one but two issues.
> > 1) Row count estimates for joins are wrong.
>
> Yup.
>
> > 2) Rows are assumed to be continuous while in reality they are
> > discrete.
>
> Where do you get that from?
I made a similar example, where estimates are good. It's quite
artificial, because it selects small limit from enormous virtual table
constructed by cross joins. But it illustrates how our model,
assuming tuples to be continuous, can differ from reality.
create table t1 (id int primary key, k int);
create table t2 (id int);
insert into t1 (select i, i from generate_series(1,1000000) i);
insert into t2 (select 0 from generate_series(1,100) i);
vacuum analyze t1, t2;
By default, the query is processed using sequential scan for t1.
# explain analyze select * from t1,t2 x1,t2 x2,t2 x3,t2 x4,t2 x5 where
t1.id = 500000 limit 100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..1.26 rows=100 width=28) (actual
time=32.879..32.926 rows=100 loops=1)
-> Nested Loop (cost=0.00..126279562.00 rows=10000000000
width=28) (actual time=32.878..32.912 rows=100 loops=1)
-> Nested Loop (cost=0.00..1279559.75 rows=100000000
width=24) (actual time=32.873..32.873 rows=1 loops=1)
-> Nested Loop (cost=0.00..29557.50 rows=1000000
width=20) (actual time=32.868..32.868 rows=1 loops=1)
-> Nested Loop (cost=0.00..17055.25 rows=10000
width=16) (actual time=32.864..32.864 rows=1 loops=1)
-> Nested Loop (cost=0.00..16928.00
rows=100 width=12) (actual time=32.856..32.856 rows=1 loops=1)
-> Seq Scan on t1
(cost=0.00..16925.00 rows=1 width=8) (actual time=32.841..32.841
rows=1 loops=1)
Filter: (id = 500000)
Rows Removed by Filter: 501983
-> Seq Scan on t2 x1
(cost=0.00..2.00 rows=100 width=4) (actual time=0.011..0.011 rows=1
loops=1)
-> Materialize (cost=0.00..2.50 rows=100
width=4) (actual time=0.007..0.007 rows=1 loops=1)
-> Seq Scan on t2 x2
(cost=0.00..2.00 rows=100 width=4) (actual time=0.003..0.003 rows=1
loops=1)
-> Materialize (cost=0.00..2.50 rows=100
width=4) (actual time=0.003..0.003 rows=1 loops=1)
-> Seq Scan on t2 x3 (cost=0.00..2.00
rows=100 width=4) (actual time=0.002..0.003 rows=1 loops=1)
-> Materialize (cost=0.00..2.50 rows=100 width=4)
(actual time=0.003..0.003 rows=1 loops=1)
-> Seq Scan on t2 x4 (cost=0.00..2.00 rows=100
width=4) (actual time=0.002..0.003 rows=1 loops=1)
-> Materialize (cost=0.00..2.50 rows=100 width=4) (actual
time=0.004..0.024 rows=100 loops=1)
-> Seq Scan on t2 x5 (cost=0.00..2.00 rows=100
width=4) (actual time=0.003..0.010 rows=100 loops=1)
Planning Time: 0.372 ms
Execution Time: 32.987 ms
(20 rows)
But if we disable sequential scan and bitmap scan then it would be
index scan, which is much faster.
# set enable_seqscan = off;
# set enable_bitmapscan = off;
# explain analyze select * from t1,t2 x1,t2 x2,t2 x3,t2 x4,t2 x5 where
t1.id = 500000 limit 100;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=50000000000.43..50000000001.69 rows=100 width=28)
(actual time=0.041..0.092 rows=100 loops=1)
-> Nested Loop (cost=50000000000.43..50126262645.44
rows=10000000000 width=28) (actual time=0.040..0.079 rows=100 loops=1)
-> Nested Loop (cost=40000000000.43..40001262643.19
rows=100000000 width=24) (actual time=0.036..0.036 rows=1 loops=1)
-> Nested Loop (cost=30000000000.42..30000012640.94
rows=1000000 width=20) (actual time=0.033..0.033 rows=1 loops=1)
-> Nested Loop
(cost=20000000000.42..20000000138.69 rows=10000 width=16) (actual
time=0.029..0.029 rows=1 loops=1)
-> Nested Loop
(cost=10000000000.42..10000000011.44 rows=100 width=12) (actual
time=0.023..0.023 rows=1 loops=1)
-> Index Scan using t1_pkey on t1
(cost=0.42..8.44 rows=1 width=8) (actual time=0.015..0.015 rows=1
loops=1)
Index Cond: (id = 500000)
-> Seq Scan on t2 x1
(cost=10000000000.00..10000000002.00 rows=100 width=4) (actual
time=0.007..0.007 rows=1 loops=1)
-> Materialize
(cost=10000000000.00..10000000002.50 rows=100 width=4) (actual
time=0.004..0.005 rows=1 loops=1)
-> Seq Scan on t2 x2
(cost=10000000000.00..10000000002.00 rows=100 width=4) (actual
time=0.003..0.003 rows=1 loops=1)
-> Materialize
(cost=10000000000.00..10000000002.50 rows=100 width=4) (actual
time=0.003..0.003 rows=1 loops=1)
-> Seq Scan on t2 x3
(cost=10000000000.00..10000000002.00 rows=100 width=4) (actual
time=0.003..0.003 rows=1 loops=1)
-> Materialize (cost=10000000000.00..10000000002.50
rows=100 width=4) (actual time=0.003..0.003 rows=1 loops=1)
-> Seq Scan on t2 x4
(cost=10000000000.00..10000000002.00 rows=100 width=4) (actual
time=0.002..0.003 rows=1 loops=1)
-> Materialize (cost=10000000000.00..10000000002.50
rows=100 width=4) (actual time=0.003..0.026 rows=100 loops=1)
-> Seq Scan on t2 x5
(cost=10000000000.00..10000000002.00 rows=100 width=4) (actual
time=0.003..0.010 rows=100 loops=1)
Planning Time: 0.274 ms
Execution Time: 0.150 ms
(19 rows)
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH 1/2] pg_settings_get_flags(): add DEFAULT_COMPILE and DEFAULT_INITDB ..
@ 2022-07-19 13:31 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2022-07-19 13:31 UTC (permalink / raw)
for settings which vary by ./configure or platform or initdb
Note that this is independent from PGC_S_DYNAMIC_DEFAULT.
---
doc/src/sgml/func.sgml | 15 ++++++
src/backend/utils/misc/guc_funcs.c | 6 ++-
src/backend/utils/misc/guc_tables.c | 76 ++++++++++++++++++-----------
src/include/utils/guc.h | 2 +
4 files changed, 69 insertions(+), 30 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index d599b243b10..66451a6c759 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24825,6 +24825,21 @@ SELECT collation for ('foo' COLLATE "de_DE");
<row><entry>Flag</entry><entry>Description</entry></row>
</thead>
<tbody>
+
+ <row>
+ <entry><literal>DEFAULT_COMPILE</literal></entry>
+ <entry>Parameters with this flag have default values which vary by
+ platform, or compile-time options.
+ </entry>
+ </row>
+
+ <row>
+ <entry><literal>DEFAULT_INITDB</literal></entry>
+ <entry>Parameters with this flag have default values which are
+ determined dynamically during initdb.
+ </entry>
+ </row>
+
<row>
<entry><literal>EXPLAIN</literal></entry>
<entry>Parameters with this flag are included in
diff --git a/src/backend/utils/misc/guc_funcs.c b/src/backend/utils/misc/guc_funcs.c
index 2ce1e53ec54..70fdce3868d 100644
--- a/src/backend/utils/misc/guc_funcs.c
+++ b/src/backend/utils/misc/guc_funcs.c
@@ -577,7 +577,7 @@ ShowAllGUCConfig(DestReceiver *dest)
Datum
pg_settings_get_flags(PG_FUNCTION_ARGS)
{
-#define MAX_GUC_FLAGS 6
+#define MAX_GUC_FLAGS 8
char *varname = TextDatumGetCString(PG_GETARG_DATUM(0));
struct config_generic *record;
int cnt = 0;
@@ -590,6 +590,10 @@ pg_settings_get_flags(PG_FUNCTION_ARGS)
if (record == NULL)
PG_RETURN_NULL();
+ if (record->flags & GUC_DEFAULT_COMPILE)
+ flags[cnt++] = CStringGetTextDatum("DEFAULT_COMPILE");
+ if (record->flags & GUC_DEFAULT_INITDB)
+ flags[cnt++] = CStringGetTextDatum("DEFAULT_INITDB");
if (record->flags & GUC_EXPLAIN)
flags[cnt++] = CStringGetTextDatum("EXPLAIN");
if (record->flags & GUC_NO_RESET)
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 2c3061b7359..0d6c782a1df 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -1233,7 +1233,7 @@ struct config_bool ConfigureNamesBool[] =
{"debug_assertions", PGC_INTERNAL, PRESET_OPTIONS,
gettext_noop("Shows whether the running server has assertion checks enabled."),
NULL,
- GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
+ GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE | GUC_DEFAULT_COMPILE
},
&assert_enabled,
DEFAULT_ASSERT_ENABLED,
@@ -1425,7 +1425,8 @@ struct config_bool ConfigureNamesBool[] =
{
{"update_process_title", PGC_SUSET, PROCESS_TITLE,
gettext_noop("Updates the process title to show the active SQL command."),
- gettext_noop("Enables updating of the process title every time a new SQL command is received by the server.")
+ gettext_noop("Enables updating of the process title every time a new SQL command is received by the server."),
+ GUC_DEFAULT_COMPILE
},
&update_process_title,
DEFAULT_UPDATE_PROCESS_TITLE,
@@ -2170,7 +2171,8 @@ struct config_int ConfigureNamesInt[] =
{
{"max_connections", PGC_POSTMASTER, CONN_AUTH_SETTINGS,
gettext_noop("Sets the maximum number of concurrent connections."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
},
&MaxConnections,
100, 1, MAX_BACKENDS,
@@ -2218,7 +2220,7 @@ struct config_int ConfigureNamesInt[] =
{"shared_buffers", PGC_POSTMASTER, RESOURCES_MEM,
gettext_noop("Sets the number of shared memory buffers used by the server."),
NULL,
- GUC_UNIT_BLOCKS
+ GUC_UNIT_BLOCKS | GUC_DEFAULT_INITDB
},
&NBuffers,
16384, 16, INT_MAX / 2,
@@ -2261,7 +2263,8 @@ struct config_int ConfigureNamesInt[] =
{
{"port", PGC_POSTMASTER, CONN_AUTH_SETTINGS,
gettext_noop("Sets the TCP port the server listens on."),
- NULL
+ NULL,
+ GUC_DEFAULT_COMPILE
},
&PostPortNumber,
DEF_PGPORT, 1, 65535,
@@ -2290,7 +2293,8 @@ struct config_int ConfigureNamesInt[] =
"to be a numeric mode specification in the form "
"accepted by the chmod and umask system calls. "
"(To use the customary octal format the number must "
- "start with a 0 (zero).)")
+ "start with a 0 (zero).)"),
+ GUC_DEFAULT_INITDB
},
&Log_file_mode,
0600, 0000, 0777,
@@ -2723,7 +2727,7 @@ struct config_int ConfigureNamesInt[] =
{"checkpoint_flush_after", PGC_SIGHUP, WAL_CHECKPOINTS,
gettext_noop("Number of pages after which previously performed writes are flushed to disk."),
NULL,
- GUC_UNIT_BLOCKS
+ GUC_UNIT_BLOCKS | GUC_DEFAULT_COMPILE
},
&checkpoint_flush_after,
DEFAULT_CHECKPOINT_FLUSH_AFTER, 0, WRITEBACK_MAX_PENDING_FLUSHES,
@@ -2941,7 +2945,7 @@ struct config_int ConfigureNamesInt[] =
{"bgwriter_flush_after", PGC_SIGHUP, RESOURCES_BGWRITER,
gettext_noop("Number of pages after which previously performed writes are flushed to disk."),
NULL,
- GUC_UNIT_BLOCKS
+ GUC_UNIT_BLOCKS | GUC_DEFAULT_COMPILE
},
&bgwriter_flush_after,
DEFAULT_BGWRITER_FLUSH_AFTER, 0, WRITEBACK_MAX_PENDING_FLUSHES,
@@ -2954,7 +2958,7 @@ struct config_int ConfigureNamesInt[] =
RESOURCES_ASYNCHRONOUS,
gettext_noop("Number of simultaneous requests that can be handled efficiently by the disk subsystem."),
NULL,
- GUC_EXPLAIN
+ GUC_EXPLAIN | GUC_DEFAULT_COMPILE
},
&effective_io_concurrency,
DEFAULT_EFFECTIVE_IO_CONCURRENCY,
@@ -2968,7 +2972,7 @@ struct config_int ConfigureNamesInt[] =
RESOURCES_ASYNCHRONOUS,
gettext_noop("A variant of effective_io_concurrency that is used for maintenance work."),
NULL,
- GUC_EXPLAIN
+ GUC_EXPLAIN | GUC_DEFAULT_COMPILE
},
&maintenance_io_concurrency,
DEFAULT_MAINTENANCE_IO_CONCURRENCY,
@@ -2981,7 +2985,7 @@ struct config_int ConfigureNamesInt[] =
{"backend_flush_after", PGC_USERSET, RESOURCES_ASYNCHRONOUS,
gettext_noop("Number of pages after which previously performed writes are flushed to disk."),
NULL,
- GUC_UNIT_BLOCKS
+ GUC_UNIT_BLOCKS | GUC_DEFAULT_COMPILE
},
&backend_flush_after,
DEFAULT_BACKEND_FLUSH_AFTER, 0, WRITEBACK_MAX_PENDING_FLUSHES,
@@ -3436,7 +3440,7 @@ struct config_int ConfigureNamesInt[] =
{"debug_discard_caches", PGC_SUSET, DEVELOPER_OPTIONS,
gettext_noop("Aggressively flush system caches for debugging purposes."),
NULL,
- GUC_NOT_IN_SAMPLE
+ GUC_NOT_IN_SAMPLE | GUC_DEFAULT_COMPILE
},
&debug_discard_caches,
#ifdef DISCARD_CACHES_ENABLED
@@ -3930,7 +3934,8 @@ struct config_string ConfigureNamesString[] =
{
{"log_timezone", PGC_SIGHUP, LOGGING_WHAT,
gettext_noop("Sets the time zone to use in log messages."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
},
&log_timezone_string,
"GMT",
@@ -3942,7 +3947,7 @@ struct config_string ConfigureNamesString[] =
gettext_noop("Sets the display format for date and time values."),
gettext_noop("Also controls interpretation of ambiguous "
"date inputs."),
- GUC_LIST_INPUT | GUC_REPORT
+ GUC_LIST_INPUT | GUC_REPORT | GUC_DEFAULT_INITDB
},
&datestyle_string,
"ISO, MDY",
@@ -4056,7 +4061,8 @@ struct config_string ConfigureNamesString[] =
{
{"lc_messages", PGC_SUSET, CLIENT_CONN_LOCALE,
gettext_noop("Sets the language in which messages are displayed."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
},
&locale_messages,
"",
@@ -4066,7 +4072,8 @@ struct config_string ConfigureNamesString[] =
{
{"lc_monetary", PGC_USERSET, CLIENT_CONN_LOCALE,
gettext_noop("Sets the locale for formatting monetary amounts."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
},
&locale_monetary,
"C",
@@ -4076,7 +4083,8 @@ struct config_string ConfigureNamesString[] =
{
{"lc_numeric", PGC_USERSET, CLIENT_CONN_LOCALE,
gettext_noop("Sets the locale for formatting numbers."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
},
&locale_numeric,
"C",
@@ -4086,7 +4094,8 @@ struct config_string ConfigureNamesString[] =
{
{"lc_time", PGC_USERSET, CLIENT_CONN_LOCALE,
gettext_noop("Sets the locale for formatting date and time values."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
},
&locale_time,
"C",
@@ -4245,7 +4254,8 @@ struct config_string ConfigureNamesString[] =
{"TimeZone", PGC_USERSET, CLIENT_CONN_LOCALE,
gettext_noop("Sets the time zone for displaying and interpreting time stamps."),
NULL,
- GUC_REPORT
+ GUC_REPORT | GUC_DEFAULT_INITDB
+
},
&timezone_string,
"GMT",
@@ -4276,7 +4286,7 @@ struct config_string ConfigureNamesString[] =
{"unix_socket_directories", PGC_POSTMASTER, CONN_AUTH_SETTINGS,
gettext_noop("Sets the directories where Unix-domain sockets will be created."),
NULL,
- GUC_LIST_INPUT | GUC_LIST_QUOTE | GUC_SUPERUSER_ONLY
+ GUC_LIST_INPUT | GUC_LIST_QUOTE | GUC_SUPERUSER_ONLY | GUC_DEFAULT_COMPILE
},
&Unix_socket_directories,
DEFAULT_PGSOCKET_DIR,
@@ -4357,7 +4367,7 @@ struct config_string ConfigureNamesString[] =
{"ssl_library", PGC_INTERNAL, PRESET_OPTIONS,
gettext_noop("Shows the name of the SSL library."),
NULL,
- GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
+ GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE | GUC_DEFAULT_COMPILE
},
&ssl_library,
#ifdef USE_SSL
@@ -4432,7 +4442,9 @@ struct config_string ConfigureNamesString[] =
{
{"default_text_search_config", PGC_USERSET, CLIENT_CONN_LOCALE,
gettext_noop("Sets default text search configuration."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
+
},
&TSCurrentConfig,
"pg_catalog.simple",
@@ -4443,7 +4455,7 @@ struct config_string ConfigureNamesString[] =
{"ssl_ciphers", PGC_SIGHUP, CONN_AUTH_SSL,
gettext_noop("Sets the list of allowed SSL ciphers."),
NULL,
- GUC_SUPERUSER_ONLY
+ GUC_SUPERUSER_ONLY | GUC_DEFAULT_COMPILE
},
&SSLCipherSuites,
#ifdef USE_OPENSSL
@@ -4458,7 +4470,7 @@ struct config_string ConfigureNamesString[] =
{"ssl_ecdh_curve", PGC_SIGHUP, CONN_AUTH_SSL,
gettext_noop("Sets the curve to use for ECDH."),
NULL,
- GUC_SUPERUSER_ONLY
+ GUC_SUPERUSER_ONLY | GUC_DEFAULT_COMPILE
},
&SSLECDHCurve,
#ifdef USE_SSL
@@ -4717,7 +4729,8 @@ struct config_enum ConfigureNamesEnum[] =
{
{"syslog_facility", PGC_SIGHUP, LOGGING_WHERE,
gettext_noop("Sets the syslog \"facility\" to be used when syslog enabled."),
- NULL
+ NULL,
+ GUC_DEFAULT_COMPILE
},
&syslog_facility,
DEFAULT_SYSLOG_FACILITY,
@@ -4826,7 +4839,9 @@ struct config_enum ConfigureNamesEnum[] =
{
{"dynamic_shared_memory_type", PGC_POSTMASTER, RESOURCES_MEM,
gettext_noop("Selects the dynamic shared memory implementation used."),
- NULL
+ NULL,
+ /* platform-specific default, which is also overriden during initdb */
+ GUC_DEFAULT_COMPILE | GUC_DEFAULT_INITDB
},
&dynamic_shared_memory_type,
DEFAULT_DYNAMIC_SHARED_MEMORY_TYPE, dynamic_shared_memory_options,
@@ -4836,7 +4851,8 @@ struct config_enum ConfigureNamesEnum[] =
{
{"shared_memory_type", PGC_POSTMASTER, RESOURCES_MEM,
gettext_noop("Selects the shared memory implementation used for the main shared memory region."),
- NULL
+ NULL,
+ GUC_DEFAULT_COMPILE
},
&shared_memory_type,
DEFAULT_SHARED_MEMORY_TYPE, shared_memory_options,
@@ -4846,7 +4862,8 @@ struct config_enum ConfigureNamesEnum[] =
{
{"wal_sync_method", PGC_SIGHUP, WAL_SETTINGS,
gettext_noop("Selects the method used for forcing WAL updates to disk."),
- NULL
+ NULL,
+ GUC_DEFAULT_COMPILE
},
&sync_method,
DEFAULT_SYNC_METHOD, sync_method_options,
@@ -4910,7 +4927,8 @@ struct config_enum ConfigureNamesEnum[] =
{
{"password_encryption", PGC_USERSET, CONN_AUTH_AUTH,
gettext_noop("Chooses the algorithm for encrypting passwords."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
},
&Password_encryption,
PASSWORD_TYPE_SCRAM_SHA_256, password_encryption_options,
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index 15c61992f06..2b17cd08fd9 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -222,6 +222,8 @@ typedef enum
#define GUC_DISALLOW_IN_AUTO_FILE \
0x002000 /* can't set in PG_AUTOCONF_FILENAME */
#define GUC_RUNTIME_COMPUTED 0x004000 /* delay processing in 'postgres -C' */
+#define GUC_DEFAULT_COMPILE 0x008000 /* default is determined at compile time */
+#define GUC_DEFAULT_INITDB 0x010000 /* default is determined at during initdb */
#define GUC_UNIT_KB 0x01000000 /* value is in kilobytes */
#define GUC_UNIT_BLOCKS 0x02000000 /* value is in blocks */
--
2.34.1
--gwf9g/xA8/k5MLj4
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0002-test-GUC-default-values-in-postgresql.conf.sample.patch"
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH 1/2] pg_settings_get_flags(): add DEFAULT_COMPILE and DEFAULT_INITDB ..
@ 2022-07-19 13:31 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2022-07-19 13:31 UTC (permalink / raw)
for settings which vary by ./configure or platform or initdb
Note that this is independent from PGC_S_DYNAMIC_DEFAULT.
---
doc/src/sgml/func.sgml | 15 ++++++
src/backend/utils/misc/guc_funcs.c | 6 ++-
src/backend/utils/misc/guc_tables.c | 76 ++++++++++++++++++-----------
src/include/utils/guc.h | 2 +
4 files changed, 69 insertions(+), 30 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e13fce6f6b1..17069d2249e 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24945,6 +24945,21 @@ SELECT collation for ('foo' COLLATE "de_DE");
<row><entry>Flag</entry><entry>Description</entry></row>
</thead>
<tbody>
+
+ <row>
+ <entry><literal>DEFAULT_COMPILE</literal></entry>
+ <entry>Parameters with this flag have default values which vary by
+ platform, or compile-time options.
+ </entry>
+ </row>
+
+ <row>
+ <entry><literal>DEFAULT_INITDB</literal></entry>
+ <entry>Parameters with this flag have default values which are
+ determined dynamically during initdb.
+ </entry>
+ </row>
+
<row>
<entry><literal>EXPLAIN</literal></entry>
<entry>Parameters with this flag are included in
diff --git a/src/backend/utils/misc/guc_funcs.c b/src/backend/utils/misc/guc_funcs.c
index 52c361e9755..183943c1973 100644
--- a/src/backend/utils/misc/guc_funcs.c
+++ b/src/backend/utils/misc/guc_funcs.c
@@ -551,7 +551,7 @@ ShowAllGUCConfig(DestReceiver *dest)
Datum
pg_settings_get_flags(PG_FUNCTION_ARGS)
{
-#define MAX_GUC_FLAGS 6
+#define MAX_GUC_FLAGS 8
char *varname = TextDatumGetCString(PG_GETARG_DATUM(0));
struct config_generic *record;
int cnt = 0;
@@ -564,6 +564,10 @@ pg_settings_get_flags(PG_FUNCTION_ARGS)
if (record == NULL)
PG_RETURN_NULL();
+ if (record->flags & GUC_DEFAULT_COMPILE)
+ flags[cnt++] = CStringGetTextDatum("DEFAULT_COMPILE");
+ if (record->flags & GUC_DEFAULT_INITDB)
+ flags[cnt++] = CStringGetTextDatum("DEFAULT_INITDB");
if (record->flags & GUC_EXPLAIN)
flags[cnt++] = CStringGetTextDatum("EXPLAIN");
if (record->flags & GUC_NO_RESET)
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 2f42cebaf62..94b0aa24a98 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -1233,7 +1233,7 @@ struct config_bool ConfigureNamesBool[] =
{"debug_assertions", PGC_INTERNAL, PRESET_OPTIONS,
gettext_noop("Shows whether the running server has assertion checks enabled."),
NULL,
- GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
+ GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE | GUC_DEFAULT_COMPILE
},
&assert_enabled,
DEFAULT_ASSERT_ENABLED,
@@ -1425,7 +1425,8 @@ struct config_bool ConfigureNamesBool[] =
{
{"update_process_title", PGC_SUSET, PROCESS_TITLE,
gettext_noop("Updates the process title to show the active SQL command."),
- gettext_noop("Enables updating of the process title every time a new SQL command is received by the server.")
+ gettext_noop("Enables updating of the process title every time a new SQL command is received by the server."),
+ GUC_DEFAULT_COMPILE
},
&update_process_title,
DEFAULT_UPDATE_PROCESS_TITLE,
@@ -2180,7 +2181,8 @@ struct config_int ConfigureNamesInt[] =
{
{"max_connections", PGC_POSTMASTER, CONN_AUTH_SETTINGS,
gettext_noop("Sets the maximum number of concurrent connections."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
},
&MaxConnections,
100, 1, MAX_BACKENDS,
@@ -2228,7 +2230,7 @@ struct config_int ConfigureNamesInt[] =
{"shared_buffers", PGC_POSTMASTER, RESOURCES_MEM,
gettext_noop("Sets the number of shared memory buffers used by the server."),
NULL,
- GUC_UNIT_BLOCKS
+ GUC_UNIT_BLOCKS | GUC_DEFAULT_INITDB
},
&NBuffers,
16384, 16, INT_MAX / 2,
@@ -2282,7 +2284,8 @@ struct config_int ConfigureNamesInt[] =
{
{"port", PGC_POSTMASTER, CONN_AUTH_SETTINGS,
gettext_noop("Sets the TCP port the server listens on."),
- NULL
+ NULL,
+ GUC_DEFAULT_COMPILE
},
&PostPortNumber,
DEF_PGPORT, 1, 65535,
@@ -2311,7 +2314,8 @@ struct config_int ConfigureNamesInt[] =
"to be a numeric mode specification in the form "
"accepted by the chmod and umask system calls. "
"(To use the customary octal format the number must "
- "start with a 0 (zero).)")
+ "start with a 0 (zero).)"),
+ GUC_DEFAULT_INITDB
},
&Log_file_mode,
0600, 0000, 0777,
@@ -2744,7 +2748,7 @@ struct config_int ConfigureNamesInt[] =
{"checkpoint_flush_after", PGC_SIGHUP, WAL_CHECKPOINTS,
gettext_noop("Number of pages after which previously performed writes are flushed to disk."),
NULL,
- GUC_UNIT_BLOCKS
+ GUC_UNIT_BLOCKS | GUC_DEFAULT_COMPILE
},
&checkpoint_flush_after,
DEFAULT_CHECKPOINT_FLUSH_AFTER, 0, WRITEBACK_MAX_PENDING_FLUSHES,
@@ -2962,7 +2966,7 @@ struct config_int ConfigureNamesInt[] =
{"bgwriter_flush_after", PGC_SIGHUP, RESOURCES_BGWRITER,
gettext_noop("Number of pages after which previously performed writes are flushed to disk."),
NULL,
- GUC_UNIT_BLOCKS
+ GUC_UNIT_BLOCKS | GUC_DEFAULT_COMPILE
},
&bgwriter_flush_after,
DEFAULT_BGWRITER_FLUSH_AFTER, 0, WRITEBACK_MAX_PENDING_FLUSHES,
@@ -2975,7 +2979,7 @@ struct config_int ConfigureNamesInt[] =
RESOURCES_ASYNCHRONOUS,
gettext_noop("Number of simultaneous requests that can be handled efficiently by the disk subsystem."),
NULL,
- GUC_EXPLAIN
+ GUC_EXPLAIN | GUC_DEFAULT_COMPILE
},
&effective_io_concurrency,
DEFAULT_EFFECTIVE_IO_CONCURRENCY,
@@ -2989,7 +2993,7 @@ struct config_int ConfigureNamesInt[] =
RESOURCES_ASYNCHRONOUS,
gettext_noop("A variant of effective_io_concurrency that is used for maintenance work."),
NULL,
- GUC_EXPLAIN
+ GUC_EXPLAIN | GUC_DEFAULT_COMPILE
},
&maintenance_io_concurrency,
DEFAULT_MAINTENANCE_IO_CONCURRENCY,
@@ -3002,7 +3006,7 @@ struct config_int ConfigureNamesInt[] =
{"backend_flush_after", PGC_USERSET, RESOURCES_ASYNCHRONOUS,
gettext_noop("Number of pages after which previously performed writes are flushed to disk."),
NULL,
- GUC_UNIT_BLOCKS
+ GUC_UNIT_BLOCKS | GUC_DEFAULT_COMPILE
},
&backend_flush_after,
DEFAULT_BACKEND_FLUSH_AFTER, 0, WRITEBACK_MAX_PENDING_FLUSHES,
@@ -3457,7 +3461,7 @@ struct config_int ConfigureNamesInt[] =
{"debug_discard_caches", PGC_SUSET, DEVELOPER_OPTIONS,
gettext_noop("Aggressively flush system caches for debugging purposes."),
NULL,
- GUC_NOT_IN_SAMPLE
+ GUC_NOT_IN_SAMPLE | GUC_DEFAULT_COMPILE
},
&debug_discard_caches,
#ifdef DISCARD_CACHES_ENABLED
@@ -3951,7 +3955,8 @@ struct config_string ConfigureNamesString[] =
{
{"log_timezone", PGC_SIGHUP, LOGGING_WHAT,
gettext_noop("Sets the time zone to use in log messages."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
},
&log_timezone_string,
"GMT",
@@ -3963,7 +3968,7 @@ struct config_string ConfigureNamesString[] =
gettext_noop("Sets the display format for date and time values."),
gettext_noop("Also controls interpretation of ambiguous "
"date inputs."),
- GUC_LIST_INPUT | GUC_REPORT
+ GUC_LIST_INPUT | GUC_REPORT | GUC_DEFAULT_INITDB
},
&datestyle_string,
"ISO, MDY",
@@ -4077,7 +4082,8 @@ struct config_string ConfigureNamesString[] =
{
{"lc_messages", PGC_SUSET, CLIENT_CONN_LOCALE,
gettext_noop("Sets the language in which messages are displayed."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
},
&locale_messages,
"",
@@ -4087,7 +4093,8 @@ struct config_string ConfigureNamesString[] =
{
{"lc_monetary", PGC_USERSET, CLIENT_CONN_LOCALE,
gettext_noop("Sets the locale for formatting monetary amounts."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
},
&locale_monetary,
"C",
@@ -4097,7 +4104,8 @@ struct config_string ConfigureNamesString[] =
{
{"lc_numeric", PGC_USERSET, CLIENT_CONN_LOCALE,
gettext_noop("Sets the locale for formatting numbers."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
},
&locale_numeric,
"C",
@@ -4107,7 +4115,8 @@ struct config_string ConfigureNamesString[] =
{
{"lc_time", PGC_USERSET, CLIENT_CONN_LOCALE,
gettext_noop("Sets the locale for formatting date and time values."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
},
&locale_time,
"C",
@@ -4266,7 +4275,8 @@ struct config_string ConfigureNamesString[] =
{"TimeZone", PGC_USERSET, CLIENT_CONN_LOCALE,
gettext_noop("Sets the time zone for displaying and interpreting time stamps."),
NULL,
- GUC_REPORT
+ GUC_REPORT | GUC_DEFAULT_INITDB
+
},
&timezone_string,
"GMT",
@@ -4297,7 +4307,7 @@ struct config_string ConfigureNamesString[] =
{"unix_socket_directories", PGC_POSTMASTER, CONN_AUTH_SETTINGS,
gettext_noop("Sets the directories where Unix-domain sockets will be created."),
NULL,
- GUC_LIST_INPUT | GUC_LIST_QUOTE | GUC_SUPERUSER_ONLY
+ GUC_LIST_INPUT | GUC_LIST_QUOTE | GUC_SUPERUSER_ONLY | GUC_DEFAULT_COMPILE
},
&Unix_socket_directories,
DEFAULT_PGSOCKET_DIR,
@@ -4378,7 +4388,7 @@ struct config_string ConfigureNamesString[] =
{"ssl_library", PGC_INTERNAL, PRESET_OPTIONS,
gettext_noop("Shows the name of the SSL library."),
NULL,
- GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
+ GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE | GUC_DEFAULT_COMPILE
},
&ssl_library,
#ifdef USE_SSL
@@ -4453,7 +4463,9 @@ struct config_string ConfigureNamesString[] =
{
{"default_text_search_config", PGC_USERSET, CLIENT_CONN_LOCALE,
gettext_noop("Sets default text search configuration."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
+
},
&TSCurrentConfig,
"pg_catalog.simple",
@@ -4464,7 +4476,7 @@ struct config_string ConfigureNamesString[] =
{"ssl_ciphers", PGC_SIGHUP, CONN_AUTH_SSL,
gettext_noop("Sets the list of allowed SSL ciphers."),
NULL,
- GUC_SUPERUSER_ONLY
+ GUC_SUPERUSER_ONLY | GUC_DEFAULT_COMPILE
},
&SSLCipherSuites,
#ifdef USE_OPENSSL
@@ -4479,7 +4491,7 @@ struct config_string ConfigureNamesString[] =
{"ssl_ecdh_curve", PGC_SIGHUP, CONN_AUTH_SSL,
gettext_noop("Sets the curve to use for ECDH."),
NULL,
- GUC_SUPERUSER_ONLY
+ GUC_SUPERUSER_ONLY | GUC_DEFAULT_COMPILE
},
&SSLECDHCurve,
#ifdef USE_SSL
@@ -4738,7 +4750,8 @@ struct config_enum ConfigureNamesEnum[] =
{
{"syslog_facility", PGC_SIGHUP, LOGGING_WHERE,
gettext_noop("Sets the syslog \"facility\" to be used when syslog enabled."),
- NULL
+ NULL,
+ GUC_DEFAULT_COMPILE
},
&syslog_facility,
DEFAULT_SYSLOG_FACILITY,
@@ -4847,7 +4860,9 @@ struct config_enum ConfigureNamesEnum[] =
{
{"dynamic_shared_memory_type", PGC_POSTMASTER, RESOURCES_MEM,
gettext_noop("Selects the dynamic shared memory implementation used."),
- NULL
+ NULL,
+ /* platform-specific default, which is also overriden during initdb */
+ GUC_DEFAULT_COMPILE | GUC_DEFAULT_INITDB
},
&dynamic_shared_memory_type,
DEFAULT_DYNAMIC_SHARED_MEMORY_TYPE, dynamic_shared_memory_options,
@@ -4857,7 +4872,8 @@ struct config_enum ConfigureNamesEnum[] =
{
{"shared_memory_type", PGC_POSTMASTER, RESOURCES_MEM,
gettext_noop("Selects the shared memory implementation used for the main shared memory region."),
- NULL
+ NULL,
+ GUC_DEFAULT_COMPILE
},
&shared_memory_type,
DEFAULT_SHARED_MEMORY_TYPE, shared_memory_options,
@@ -4867,7 +4883,8 @@ struct config_enum ConfigureNamesEnum[] =
{
{"wal_sync_method", PGC_SIGHUP, WAL_SETTINGS,
gettext_noop("Selects the method used for forcing WAL updates to disk."),
- NULL
+ NULL,
+ GUC_DEFAULT_COMPILE
},
&sync_method,
DEFAULT_SYNC_METHOD, sync_method_options,
@@ -4931,7 +4948,8 @@ struct config_enum ConfigureNamesEnum[] =
{
{"password_encryption", PGC_USERSET, CONN_AUTH_AUTH,
gettext_noop("Chooses the algorithm for encrypting passwords."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
},
&Password_encryption,
PASSWORD_TYPE_SCRAM_SHA_256, password_encryption_options,
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index ba89d013e64..2b98595b3ea 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -221,6 +221,8 @@ typedef enum
#define GUC_DISALLOW_IN_AUTO_FILE \
0x002000 /* can't set in PG_AUTOCONF_FILENAME */
#define GUC_RUNTIME_COMPUTED 0x004000 /* delay processing in 'postgres -C' */
+#define GUC_DEFAULT_COMPILE 0x008000 /* default is determined at compile time */
+#define GUC_DEFAULT_INITDB 0x010000 /* default is determined at during initdb */
#define GUC_UNIT_KB 0x01000000 /* value is in kilobytes */
#define GUC_UNIT_BLOCKS 0x02000000 /* value is in blocks */
--
2.34.1
--8KqcO45QTmavqrvZ
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0002-WIP-test-GUC-default-values-in-postgresql.conf.sampl.patch"
^ permalink raw reply [nested|flat] 21+ messages in thread
* [PATCH 1/2] pg_settings_get_flags(): add DEFAULT_COMPILE and DEFAULT_INITDB ..
@ 2022-07-19 13:31 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Justin Pryzby @ 2022-07-19 13:31 UTC (permalink / raw)
for settings which vary by ./configure or platform or initdb
Note that this is independent from PGC_S_DYNAMIC_DEFAULT.
---
doc/src/sgml/func.sgml | 15 ++++++
src/backend/utils/misc/guc_funcs.c | 6 ++-
src/backend/utils/misc/guc_tables.c | 76 ++++++++++++++++++-----------
src/include/utils/guc.h | 2 +
4 files changed, 69 insertions(+), 30 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 82fba48d5f7..67c68fc9292 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24377,6 +24377,21 @@ SELECT collation for ('foo' COLLATE "de_DE");
<row><entry>Flag</entry><entry>Description</entry></row>
</thead>
<tbody>
+
+ <row>
+ <entry><literal>DEFAULT_COMPILE</literal></entry>
+ <entry>Parameters with this flag have default values which vary by
+ platform, or compile-time options.
+ </entry>
+ </row>
+
+ <row>
+ <entry><literal>DEFAULT_INITDB</literal></entry>
+ <entry>Parameters with this flag have default values which are
+ determined dynamically during initdb.
+ </entry>
+ </row>
+
<row>
<entry><literal>EXPLAIN</literal></entry>
<entry>Parameters with this flag are included in
diff --git a/src/backend/utils/misc/guc_funcs.c b/src/backend/utils/misc/guc_funcs.c
index 108b3bd1290..2b666e8d014 100644
--- a/src/backend/utils/misc/guc_funcs.c
+++ b/src/backend/utils/misc/guc_funcs.c
@@ -538,7 +538,7 @@ ShowAllGUCConfig(DestReceiver *dest)
Datum
pg_settings_get_flags(PG_FUNCTION_ARGS)
{
-#define MAX_GUC_FLAGS 6
+#define MAX_GUC_FLAGS 8
char *varname = TextDatumGetCString(PG_GETARG_DATUM(0));
struct config_generic *record;
int cnt = 0;
@@ -551,6 +551,10 @@ pg_settings_get_flags(PG_FUNCTION_ARGS)
if (record == NULL)
PG_RETURN_NULL();
+ if (record->flags & GUC_DEFAULT_COMPILE)
+ flags[cnt++] = CStringGetTextDatum("DEFAULT_COMPILE");
+ if (record->flags & GUC_DEFAULT_INITDB)
+ flags[cnt++] = CStringGetTextDatum("DEFAULT_INITDB");
if (record->flags & GUC_EXPLAIN)
flags[cnt++] = CStringGetTextDatum("EXPLAIN");
if (record->flags & GUC_NO_RESET)
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index e5cc4e3205a..6bd206c164a 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -1205,7 +1205,7 @@ struct config_bool ConfigureNamesBool[] =
{"debug_assertions", PGC_INTERNAL, PRESET_OPTIONS,
gettext_noop("Shows whether the running server has assertion checks enabled."),
NULL,
- GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
+ GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE | GUC_DEFAULT_COMPILE
},
&assert_enabled,
DEFAULT_ASSERT_ENABLED,
@@ -1377,7 +1377,8 @@ struct config_bool ConfigureNamesBool[] =
{
{"update_process_title", PGC_SUSET, PROCESS_TITLE,
gettext_noop("Updates the process title to show the active SQL command."),
- gettext_noop("Enables updating of the process title every time a new SQL command is received by the server.")
+ gettext_noop("Enables updating of the process title every time a new SQL command is received by the server."),
+ GUC_DEFAULT_COMPILE
},
&update_process_title,
DEFAULT_UPDATE_PROCESS_TITLE,
@@ -2122,7 +2123,8 @@ struct config_int ConfigureNamesInt[] =
{
{"max_connections", PGC_POSTMASTER, CONN_AUTH_SETTINGS,
gettext_noop("Sets the maximum number of concurrent connections."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
},
&MaxConnections,
100, 1, MAX_BACKENDS,
@@ -2159,7 +2161,7 @@ struct config_int ConfigureNamesInt[] =
{"shared_buffers", PGC_POSTMASTER, RESOURCES_MEM,
gettext_noop("Sets the number of shared memory buffers used by the server."),
NULL,
- GUC_UNIT_BLOCKS
+ GUC_UNIT_BLOCKS | GUC_DEFAULT_INITDB
},
&NBuffers,
16384, 16, INT_MAX / 2,
@@ -2202,7 +2204,8 @@ struct config_int ConfigureNamesInt[] =
{
{"port", PGC_POSTMASTER, CONN_AUTH_SETTINGS,
gettext_noop("Sets the TCP port the server listens on."),
- NULL
+ NULL,
+ GUC_DEFAULT_COMPILE
},
&PostPortNumber,
DEF_PGPORT, 1, 65535,
@@ -2231,7 +2234,8 @@ struct config_int ConfigureNamesInt[] =
"to be a numeric mode specification in the form "
"accepted by the chmod and umask system calls. "
"(To use the customary octal format the number must "
- "start with a 0 (zero).)")
+ "start with a 0 (zero).)"),
+ GUC_DEFAULT_INITDB
},
&Log_file_mode,
0600, 0000, 0777,
@@ -2673,7 +2677,7 @@ struct config_int ConfigureNamesInt[] =
{"checkpoint_flush_after", PGC_SIGHUP, WAL_CHECKPOINTS,
gettext_noop("Number of pages after which previously performed writes are flushed to disk."),
NULL,
- GUC_UNIT_BLOCKS
+ GUC_UNIT_BLOCKS | GUC_DEFAULT_COMPILE
},
&checkpoint_flush_after,
DEFAULT_CHECKPOINT_FLUSH_AFTER, 0, WRITEBACK_MAX_PENDING_FLUSHES,
@@ -2891,7 +2895,7 @@ struct config_int ConfigureNamesInt[] =
{"bgwriter_flush_after", PGC_SIGHUP, RESOURCES_BGWRITER,
gettext_noop("Number of pages after which previously performed writes are flushed to disk."),
NULL,
- GUC_UNIT_BLOCKS
+ GUC_UNIT_BLOCKS | GUC_DEFAULT_COMPILE
},
&bgwriter_flush_after,
DEFAULT_BGWRITER_FLUSH_AFTER, 0, WRITEBACK_MAX_PENDING_FLUSHES,
@@ -2904,7 +2908,7 @@ struct config_int ConfigureNamesInt[] =
RESOURCES_ASYNCHRONOUS,
gettext_noop("Number of simultaneous requests that can be handled efficiently by the disk subsystem."),
NULL,
- GUC_EXPLAIN
+ GUC_EXPLAIN | GUC_DEFAULT_COMPILE
},
&effective_io_concurrency,
DEFAULT_EFFECTIVE_IO_CONCURRENCY,
@@ -2918,7 +2922,7 @@ struct config_int ConfigureNamesInt[] =
RESOURCES_ASYNCHRONOUS,
gettext_noop("A variant of effective_io_concurrency that is used for maintenance work."),
NULL,
- GUC_EXPLAIN
+ GUC_EXPLAIN | GUC_DEFAULT_COMPILE
},
&maintenance_io_concurrency,
DEFAULT_MAINTENANCE_IO_CONCURRENCY,
@@ -2931,7 +2935,7 @@ struct config_int ConfigureNamesInt[] =
{"backend_flush_after", PGC_USERSET, RESOURCES_ASYNCHRONOUS,
gettext_noop("Number of pages after which previously performed writes are flushed to disk."),
NULL,
- GUC_UNIT_BLOCKS
+ GUC_UNIT_BLOCKS | GUC_DEFAULT_COMPILE
},
&backend_flush_after,
DEFAULT_BACKEND_FLUSH_AFTER, 0, WRITEBACK_MAX_PENDING_FLUSHES,
@@ -3385,7 +3389,7 @@ struct config_int ConfigureNamesInt[] =
{"debug_discard_caches", PGC_SUSET, DEVELOPER_OPTIONS,
gettext_noop("Aggressively flush system caches for debugging purposes."),
NULL,
- GUC_NOT_IN_SAMPLE
+ GUC_NOT_IN_SAMPLE | GUC_DEFAULT_COMPILE
},
&debug_discard_caches,
#ifdef DISCARD_CACHES_ENABLED
@@ -3878,7 +3882,8 @@ struct config_string ConfigureNamesString[] =
{
{"log_timezone", PGC_SIGHUP, LOGGING_WHAT,
gettext_noop("Sets the time zone to use in log messages."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
},
&log_timezone_string,
"GMT",
@@ -3890,7 +3895,7 @@ struct config_string ConfigureNamesString[] =
gettext_noop("Sets the display format for date and time values."),
gettext_noop("Also controls interpretation of ambiguous "
"date inputs."),
- GUC_LIST_INPUT | GUC_REPORT
+ GUC_LIST_INPUT | GUC_REPORT | GUC_DEFAULT_INITDB
},
&datestyle_string,
"ISO, MDY",
@@ -3992,7 +3997,8 @@ struct config_string ConfigureNamesString[] =
{
{"lc_messages", PGC_SUSET, CLIENT_CONN_LOCALE,
gettext_noop("Sets the language in which messages are displayed."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
},
&locale_messages,
"",
@@ -4002,7 +4008,8 @@ struct config_string ConfigureNamesString[] =
{
{"lc_monetary", PGC_USERSET, CLIENT_CONN_LOCALE,
gettext_noop("Sets the locale for formatting monetary amounts."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
},
&locale_monetary,
"C",
@@ -4012,7 +4019,8 @@ struct config_string ConfigureNamesString[] =
{
{"lc_numeric", PGC_USERSET, CLIENT_CONN_LOCALE,
gettext_noop("Sets the locale for formatting numbers."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
},
&locale_numeric,
"C",
@@ -4022,7 +4030,8 @@ struct config_string ConfigureNamesString[] =
{
{"lc_time", PGC_USERSET, CLIENT_CONN_LOCALE,
gettext_noop("Sets the locale for formatting date and time values."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
},
&locale_time,
"C",
@@ -4181,7 +4190,8 @@ struct config_string ConfigureNamesString[] =
{"TimeZone", PGC_USERSET, CLIENT_CONN_LOCALE,
gettext_noop("Sets the time zone for displaying and interpreting time stamps."),
NULL,
- GUC_REPORT
+ GUC_REPORT | GUC_DEFAULT_INITDB
+
},
&timezone_string,
"GMT",
@@ -4212,7 +4222,7 @@ struct config_string ConfigureNamesString[] =
{"unix_socket_directories", PGC_POSTMASTER, CONN_AUTH_SETTINGS,
gettext_noop("Sets the directories where Unix-domain sockets will be created."),
NULL,
- GUC_LIST_INPUT | GUC_LIST_QUOTE | GUC_SUPERUSER_ONLY
+ GUC_LIST_INPUT | GUC_LIST_QUOTE | GUC_SUPERUSER_ONLY | GUC_DEFAULT_COMPILE
},
&Unix_socket_directories,
DEFAULT_PGSOCKET_DIR,
@@ -4293,7 +4303,7 @@ struct config_string ConfigureNamesString[] =
{"ssl_library", PGC_INTERNAL, PRESET_OPTIONS,
gettext_noop("Shows the name of the SSL library."),
NULL,
- GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
+ GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE | GUC_DEFAULT_COMPILE
},
&ssl_library,
#ifdef USE_SSL
@@ -4368,7 +4378,9 @@ struct config_string ConfigureNamesString[] =
{
{"default_text_search_config", PGC_USERSET, CLIENT_CONN_LOCALE,
gettext_noop("Sets default text search configuration."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
+
},
&TSCurrentConfig,
"pg_catalog.simple",
@@ -4379,7 +4391,7 @@ struct config_string ConfigureNamesString[] =
{"ssl_ciphers", PGC_SIGHUP, CONN_AUTH_SSL,
gettext_noop("Sets the list of allowed SSL ciphers."),
NULL,
- GUC_SUPERUSER_ONLY
+ GUC_SUPERUSER_ONLY | GUC_DEFAULT_COMPILE
},
&SSLCipherSuites,
#ifdef USE_OPENSSL
@@ -4394,7 +4406,7 @@ struct config_string ConfigureNamesString[] =
{"ssl_ecdh_curve", PGC_SIGHUP, CONN_AUTH_SSL,
gettext_noop("Sets the curve to use for ECDH."),
NULL,
- GUC_SUPERUSER_ONLY
+ GUC_SUPERUSER_ONLY | GUC_DEFAULT_COMPILE
},
&SSLECDHCurve,
#ifdef USE_SSL
@@ -4632,7 +4644,8 @@ struct config_enum ConfigureNamesEnum[] =
{
{"syslog_facility", PGC_SIGHUP, LOGGING_WHERE,
gettext_noop("Sets the syslog \"facility\" to be used when syslog enabled."),
- NULL
+ NULL,
+ GUC_DEFAULT_COMPILE
},
&syslog_facility,
DEFAULT_SYSLOG_FACILITY,
@@ -4741,7 +4754,9 @@ struct config_enum ConfigureNamesEnum[] =
{
{"dynamic_shared_memory_type", PGC_POSTMASTER, RESOURCES_MEM,
gettext_noop("Selects the dynamic shared memory implementation used."),
- NULL
+ NULL,
+ /* platform-specific default, which is also overriden during initdb */
+ GUC_DEFAULT_COMPILE | GUC_DEFAULT_INITDB
},
&dynamic_shared_memory_type,
DEFAULT_DYNAMIC_SHARED_MEMORY_TYPE, dynamic_shared_memory_options,
@@ -4751,7 +4766,8 @@ struct config_enum ConfigureNamesEnum[] =
{
{"shared_memory_type", PGC_POSTMASTER, RESOURCES_MEM,
gettext_noop("Selects the shared memory implementation used for the main shared memory region."),
- NULL
+ NULL,
+ GUC_DEFAULT_COMPILE
},
&shared_memory_type,
DEFAULT_SHARED_MEMORY_TYPE, shared_memory_options,
@@ -4761,7 +4777,8 @@ struct config_enum ConfigureNamesEnum[] =
{
{"wal_sync_method", PGC_SIGHUP, WAL_SETTINGS,
gettext_noop("Selects the method used for forcing WAL updates to disk."),
- NULL
+ NULL,
+ GUC_DEFAULT_COMPILE
},
&sync_method,
DEFAULT_SYNC_METHOD, sync_method_options,
@@ -4823,7 +4840,8 @@ struct config_enum ConfigureNamesEnum[] =
{
{"password_encryption", PGC_USERSET, CONN_AUTH_AUTH,
gettext_noop("Chooses the algorithm for encrypting passwords."),
- NULL
+ NULL,
+ GUC_DEFAULT_INITDB
},
&Password_encryption,
PASSWORD_TYPE_SCRAM_SHA_256, password_encryption_options,
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index b3aaff9665b..79053583a03 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -221,6 +221,8 @@ typedef enum
#define GUC_DISALLOW_IN_AUTO_FILE \
0x002000 /* can't set in PG_AUTOCONF_FILENAME */
#define GUC_RUNTIME_COMPUTED 0x004000 /* delay processing in 'postgres -C' */
+#define GUC_DEFAULT_COMPILE 0x008000 /* default is determined at compile time */
+#define GUC_DEFAULT_INITDB 0x010000 /* default is determined at during initdb */
#define GUC_UNIT_KB 0x01000000 /* value is in kilobytes */
#define GUC_UNIT_BLOCKS 0x02000000 /* value is in blocks */
--
2.25.1
--IpbVkmxF4tDyP/Kb
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0002-WIP-test-GUC-default-values.patch"
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: SLRU optimization - configurable buffer pool and partitioning the SLRU lock
@ 2023-12-13 12:19 Andrey M. Borodin <[email protected]>
0 siblings, 1 reply; 21+ messages in thread
From: Andrey M. Borodin @ 2023-12-13 12:19 UTC (permalink / raw)
To: Alvaro Herrera <[email protected]>; +Cc: Dilip Kumar <[email protected]>; tender wang <[email protected]>; PostgreSQL Hackers <[email protected]>
> On 12 Dec 2023, at 18:28, Alvaro Herrera <[email protected]> wrote:
>
> Andrey, do you have any stress tests or anything else that you used to
> gain confidence in this code?
We are using only first two steps of the patchset, these steps do not touch locking stuff.
We’ve got some confidence after Yura Sokolov’s benchmarks [0]. Thanks!
Best regards, Andrey Borodin.
[0] https://www.postgresql.org/message-id/flat/e46cdea96979545b2d8a13b451d8b1ce61dc7238.camel%40postgres...
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: SLRU optimization - configurable buffer pool and partitioning the SLRU lock
@ 2023-12-14 11:06 Dilip Kumar <[email protected]>
parent: Andrey M. Borodin <[email protected]>
0 siblings, 2 replies; 21+ messages in thread
From: Dilip Kumar @ 2023-12-14 11:06 UTC (permalink / raw)
To: Andrey M. Borodin <[email protected]>; +Cc: Alvaro Herrera <[email protected]>; tender wang <[email protected]>; PostgreSQL Hackers <[email protected]>
On Wed, Dec 13, 2023 at 5:49 PM Andrey M. Borodin <[email protected]> wrote:
> > On 12 Dec 2023, at 18:28, Alvaro Herrera <[email protected]> wrote:
> >
> > Andrey, do you have any stress tests or anything else that you used to
> > gain confidence in this code?
>
> We are using only first two steps of the patchset, these steps do not touch locking stuff.
>
> We’ve got some confidence after Yura Sokolov’s benchmarks [0]. Thanks!
>
I have run this test [1], instead of comparing against the master I
have compared the effect of (patch-1 = (0001+0002)slur buffer bank) vs
(patch-2 = (0001+0002+0003) slur buffer bank + bank-wise lock), and
here is the result of the benchmark-1 and benchmark-2. I have noticed
a very good improvement with the addition of patch 0003.
Machine information:
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 128
On-line CPU(s) list: 0-127
configurations:
max_wal_size=20GB
shared_buffers=20GB
checkpoint_timeout=40min
max_connections=700
maintenance_work_mem=1GB
subtrans_buffers=$variable
multixact_offsets_buffers=$variable
multixact_members_buffers=$variable
benchmark-1
version | subtrans | multixact | tps
| buffers | offs/memb | func+ballast
-----------+--------------+--------------+------
patch-1 | 64 | 64/128 | 87 + 58
patch-2 | 64 | 64/128 | 128 +83
patch-1 | 1024 | 512/1024 | 96 + 64
patch-2 | 1024 | 512/1024 | 163+108
benchmark-2
version | subtrans | multixact | tps
| buffers | offs/memb | func
-----------+--------------+--------------+------
patch-1 | 64 | 64/128 | 10
patch-2 | 64 | 64/128 | 12
patch-1 | 1024 | 512/1024 | 44
patch-2 | 1024 | 512/1024 | 72
[1] https://www.postgresql.org/message-id/flat/e46cdea96979545b2d8a13b451d8b1ce61dc7238.camel%40postgres...
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: SLRU optimization - configurable buffer pool and partitioning the SLRU lock
@ 2023-12-14 11:25 Andrey M. Borodin <[email protected]>
parent: Dilip Kumar <[email protected]>
1 sibling, 0 replies; 21+ messages in thread
From: Andrey M. Borodin @ 2023-12-14 11:25 UTC (permalink / raw)
To: Dilip Kumar <[email protected]>; +Cc: Alvaro Herrera <[email protected]>; tender wang <[email protected]>; PostgreSQL Hackers <[email protected]>
> On 14 Dec 2023, at 16:06, Dilip Kumar <[email protected]> wrote:
>
> I have noticed
> a very good improvement with the addition of patch 0003.
Indeed, a very impressive results! It’s almost x2 of performance on high contention scenario, on top of previous improvements.
Best regards, Andrey Borodin.
^ permalink raw reply [nested|flat] 21+ messages in thread
* Re: SLRU optimization - configurable buffer pool and partitioning the SLRU lock
@ 2023-12-14 14:56 Dilip Kumar <[email protected]>
parent: Dilip Kumar <[email protected]>
1 sibling, 0 replies; 21+ messages in thread
From: Dilip Kumar @ 2023-12-14 14:56 UTC (permalink / raw)
To: Andrey M. Borodin <[email protected]>; +Cc: Alvaro Herrera <[email protected]>; tender wang <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, Dec 14, 2023 at 4:36 PM Dilip Kumar <[email protected]> wrote:
>
> On Wed, Dec 13, 2023 at 5:49 PM Andrey M. Borodin <[email protected]> wrote:
>
> > > On 12 Dec 2023, at 18:28, Alvaro Herrera <[email protected]> wrote:
> > >
> > > Andrey, do you have any stress tests or anything else that you used to
> > > gain confidence in this code?
> >
I have done some more testing for the clog group update as the
attached test file executes two concurrent scripts executed with
pgbench, the first script is the slow script which will run 10-second
long transactions and the second script is a very fast transaction
with ~10000 transactions per second. Along with that, I have also
changed the bank size such that each bank will contain just 1 page
i.e. 32k transactions per bank. I have done this way so that we do
not need to keep long-running transactions running for very long in
order to get the transactions from different banks committed during
the same time. With this test, I have got that behavior and the below
logs shows that multiple transaction range which is in different
slru-bank (considering 32k transactions per bank) are doing group
update at the same time. e.g. in the below logs, we can see xid range
around 70600, 70548, and 70558, and xid range around 755, and 752 are
getting group updates by different leaders but near the same time.
It is running fine when running for a long duration, but I am not sure
how to validate the sanity of this kind of test.
2023-12-14 14:43:31.813 GMT [3306] LOG: group leader procno 606
updated status of procno 606 xid 70600
2023-12-14 14:43:31.816 GMT [3326] LOG: procno 586 for xid 70548
added for group update
2023-12-14 14:43:31.816 GMT [3326] LOG: procno 586 is group leader
and got the lock
2023-12-14 14:43:31.816 GMT [3326] LOG: group leader procno 586
updated status of procno 586 xid 70548
2023-12-14 14:43:31.818 GMT [3327] LOG: procno 585 for xid 70558
added for group update
2023-12-14 14:43:31.818 GMT [3327] LOG: procno 585 is group leader
and got the lock
2023-12-14 14:43:31.818 GMT [3327] LOG: group leader procno 585
updated status of procno 585 xid 70558
2023-12-14 14:43:31.829 GMT [3155] LOG: procno 687 for xid 752 added
for group update
2023-12-14 14:43:31.829 GMT [3207] LOG: procno 669 for xid 755 added
for group update
2023-12-14 14:43:31.829 GMT [3155] LOG: procno 687 is group leader
and got the lock
2023-12-14 14:43:31.829 GMT [3155] LOG: group leader procno 687
updated status of procno 669 xid 755
2023-12-14 14:43:31.829 GMT [3155] LOG: group leader procno 687
updated status of procno 687 xid 752
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
Attachments:
[text/x-sh] test_clog_group_commit.sh (1.2K, ../../CAFiTN-t0xmXU4QcUcoaaHiZtP=hGEVGt+7nwVrH_VMCc8sz9Wg@mail.gmail.com/2-test_clog_group_commit.sh)
download | inline:
# Goal of this script to generate scenario where some old long running slow
# transaction get committed with the new transactions such that they falls in
# different slru banks
rm -rf pgdata
./initdb -D pgdata
echo "max_wal_size=20GB" >> pgdata/postgresql.conf
echo "shared_buffers=20GB" >> pgdata/postgresql.conf
echo "checkpoint_timeout=40min" >> pgdata/postgresql.conf
echo "max_connections=700" >> pgdata/postgresql.conf
echo "maintenance_work_mem=1GB" >> pgdata/postgresql.conf
echo "subtrans_buffers=64" >> pgdata/postgresql.conf
echo "multixact_members_buffers=128" >> pgdata/postgresql.conf
#create slow_txn.sql script
cat > slow_txn.sql << EOF
BEGIN;
INSERT INTO test VALUES(1);
DELETE FROM test WHERE a=1;
select pg_sleep(10);
COMMIT;
EOF
#create fast_txn.sql script
cat > fast_txn.sql << EOF
BEGIN;
INSERT INTO test1 VALUES(1);
DELETE FROM test1 WHERE a=1;
COMMIT;
EOF
./pg_ctl -D pgdata -l logfile -c start
./psql -d postgres -c "create table test(a int)"
./psql -d postgres -c "create table test1(a int)"
./pgbench -i -s 1 postgres
./pgbench -f slow_txn.sql -c 28 -j 28 -P 1 -T 60 postgres &
./pgbench -f fast_txn.sql -c 100 -j 100 -P 1 -T 60 postgres
sleep(10);
./pg_ctl -D pgdata -l logfile -c stop
^ permalink raw reply [nested|flat] 21+ messages in thread
* Use Postgres as meson wrap subproject
@ 2025-12-23 15:21 Niyaz Hazigaleyev <[email protected]>
0 siblings, 0 replies; 21+ messages in thread
From: Niyaz Hazigaleyev @ 2025-12-23 15:21 UTC (permalink / raw)
To: [email protected]
It is not possible to use Postgres as meson wrap subproject at least out of box. But I think it can be easily fixed. I made a small patch for libpq and it works. There were two problems, first is catalog header generator using @SOURCE_DIR@ as root for paths, which will use main root project as source dir, so I just changed it to meson.project_source_root(). Second was that libpq dependency include_directories had no dependent headers(for example postgres_ext.h) so I just used include_directories that were also used in libpq_so, libpq_st. So the patch looks like this for commit 955f5506863dce0a3416e3fae7c3297dbbaa946d:
diff --git a/src/include/catalog/meson.build b/src/include/catalog/meson.build
index ec1cf46..2b12e8e 100644
--- a/src/include/catalog/meson.build
+++ b/src/include/catalog/meson.build
@@ -136,7 +136,7 @@ generated_catalog_headers = custom_target('generated_catalog_headers',
command: [
perl,
files('../../backend/catalog/genbki.pl'),
- '--include-path=@SOURCE_ROOT@/src/include',
+ '--include-path=' + (meson.project_source_root() / 'src/include'),
'--set-version=' + pg_version_major.to_string(),
'--output=@OUTDIR@', '@INPUT@'
],
diff --git a/src/interfaces/libpq/meson.build b/src/interfaces/libpq/meson.build
index b259c99..51e051f 100644
--- a/src/interfaces/libpq/meson.build
+++ b/src/interfaces/libpq/meson.build
@@ -82,7 +82,7 @@ libpq_so = shared_library('libpq',
libpq = declare_dependency(
link_with: [libpq_so],
- include_directories: [include_directories('.')]
+ include_directories: [libpq_inc, postgres_inc]
)
# Check for functions that libpq must not call. See libpq_check.pl for the
Example of wrap file that applies patch:
[wrap-git]
url = https://git.postgresql.org/git/postgresql.git
revision = 955f5506863dce0a3416e3fae7c3297dbbaa946d
depth = 1
diff_files = postgres/libpq.patch
[provide]
libpq = libpq
So to use Postgres as wrap subproject u need to change @SOURCE_DIR@ to meson.project_source_root() and check include_directories for all dependencies. Is there any plans to support it?
^ permalink raw reply [nested|flat] 21+ messages in thread
end of thread, other threads:[~2025-12-23 15:21 UTC | newest]
Thread overview: 21+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-08-30 13:56 Startup cost of sequential scan Alexander Korotkov <[email protected]>
2018-08-30 14:04 ` Tom Lane <[email protected]>
2018-08-30 14:31 ` Alexander Korotkov <[email protected]>
2018-08-30 14:58 ` Tom Lane <[email protected]>
2018-08-30 15:08 ` Konstantin Knizhnik <[email protected]>
2018-08-30 15:23 ` Alexander Korotkov <[email protected]>
2018-08-30 15:23 ` Andrew Gierth <[email protected]>
2018-08-30 15:33 ` Tom Lane <[email protected]>
2018-08-30 15:38 ` Alexander Korotkov <[email protected]>
2018-08-30 15:55 ` Tom Lane <[email protected]>
2018-08-31 10:50 ` Alexander Korotkov <[email protected]>
2018-08-30 19:43 ` Robert Haas <[email protected]>
2018-08-30 21:38 ` Tom Lane <[email protected]>
2022-07-19 13:31 [PATCH 1/2] pg_settings_get_flags(): add DEFAULT_COMPILE and DEFAULT_INITDB .. Justin Pryzby <[email protected]>
2022-07-19 13:31 [PATCH 1/2] pg_settings_get_flags(): add DEFAULT_COMPILE and DEFAULT_INITDB .. Justin Pryzby <[email protected]>
2022-07-19 13:31 [PATCH 1/2] pg_settings_get_flags(): add DEFAULT_COMPILE and DEFAULT_INITDB .. Justin Pryzby <[email protected]>
2023-12-13 12:19 Re: SLRU optimization - configurable buffer pool and partitioning the SLRU lock Andrey M. Borodin <[email protected]>
2023-12-14 11:06 ` Re: SLRU optimization - configurable buffer pool and partitioning the SLRU lock Dilip Kumar <[email protected]>
2023-12-14 11:25 ` Re: SLRU optimization - configurable buffer pool and partitioning the SLRU lock Andrey M. Borodin <[email protected]>
2023-12-14 14:56 ` Re: SLRU optimization - configurable buffer pool and partitioning the SLRU lock Dilip Kumar <[email protected]>
2025-12-23 15:21 Use Postgres as meson wrap subproject Niyaz Hazigaleyev <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox