public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v3 3/3] Fix sizing of tape read buffers. 23+ messages / 3 participants [nested] [flat]
* [PATCH v3 3/3] Fix sizing of tape read buffers. @ 2021-01-22 20:59 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 23+ messages in thread From: Heikki Linnakangas @ 2021-01-22 20:59 UTC (permalink / raw) --- src/backend/utils/sort/tuplesort.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index 14104643e78..aff2f201f52 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -2658,7 +2658,7 @@ merge_read_buffer_size(int64 avail_mem, int nInputTapes, int nInputRuns, * we derive the input buffer size from the amount of memory available, * and M and N. */ - return (avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes; + return Max((avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes, 0); } /* -- 2.29.2 --------------631E0A48DCAEA386819DFCEC-- ^ permalink raw reply [nested|flat] 23+ messages in thread
* [PATCH v3 3/3] Fix sizing of tape read buffers. @ 2021-01-22 20:59 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 23+ messages in thread From: Heikki Linnakangas @ 2021-01-22 20:59 UTC (permalink / raw) --- src/backend/utils/sort/tuplesort.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index 14104643e78..aff2f201f52 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -2658,7 +2658,7 @@ merge_read_buffer_size(int64 avail_mem, int nInputTapes, int nInputRuns, * we derive the input buffer size from the amount of memory available, * and M and N. */ - return (avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes; + return Max((avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes, 0); } /* -- 2.29.2 --------------631E0A48DCAEA386819DFCEC-- ^ permalink raw reply [nested|flat] 23+ messages in thread
* [PATCH v3 3/3] Fix sizing of tape read buffers. @ 2021-01-22 20:59 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 23+ messages in thread From: Heikki Linnakangas @ 2021-01-22 20:59 UTC (permalink / raw) --- src/backend/utils/sort/tuplesort.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index 14104643e78..aff2f201f52 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -2658,7 +2658,7 @@ merge_read_buffer_size(int64 avail_mem, int nInputTapes, int nInputRuns, * we derive the input buffer size from the amount of memory available, * and M and N. */ - return (avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes; + return Max((avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes, 0); } /* -- 2.29.2 --------------631E0A48DCAEA386819DFCEC-- ^ permalink raw reply [nested|flat] 23+ messages in thread
* [PATCH v3 3/3] Fix sizing of tape read buffers. @ 2021-01-22 20:59 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 23+ messages in thread From: Heikki Linnakangas @ 2021-01-22 20:59 UTC (permalink / raw) --- src/backend/utils/sort/tuplesort.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index 14104643e78..aff2f201f52 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -2658,7 +2658,7 @@ merge_read_buffer_size(int64 avail_mem, int nInputTapes, int nInputRuns, * we derive the input buffer size from the amount of memory available, * and M and N. */ - return (avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes; + return Max((avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes, 0); } /* -- 2.29.2 --------------631E0A48DCAEA386819DFCEC-- ^ permalink raw reply [nested|flat] 23+ messages in thread
* [PATCH v3 3/3] Fix sizing of tape read buffers. @ 2021-01-22 20:59 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 23+ messages in thread From: Heikki Linnakangas @ 2021-01-22 20:59 UTC (permalink / raw) --- src/backend/utils/sort/tuplesort.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index 14104643e78..aff2f201f52 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -2658,7 +2658,7 @@ merge_read_buffer_size(int64 avail_mem, int nInputTapes, int nInputRuns, * we derive the input buffer size from the amount of memory available, * and M and N. */ - return (avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes; + return Max((avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes, 0); } /* -- 2.29.2 --------------631E0A48DCAEA386819DFCEC-- ^ permalink raw reply [nested|flat] 23+ messages in thread
* [PATCH v3 3/3] Fix sizing of tape read buffers. @ 2021-01-22 20:59 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 23+ messages in thread From: Heikki Linnakangas @ 2021-01-22 20:59 UTC (permalink / raw) --- src/backend/utils/sort/tuplesort.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index 14104643e78..aff2f201f52 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -2658,7 +2658,7 @@ merge_read_buffer_size(int64 avail_mem, int nInputTapes, int nInputRuns, * we derive the input buffer size from the amount of memory available, * and M and N. */ - return (avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes; + return Max((avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes, 0); } /* -- 2.29.2 --------------631E0A48DCAEA386819DFCEC-- ^ permalink raw reply [nested|flat] 23+ messages in thread
* [PATCH v3 3/3] Fix sizing of tape read buffers. @ 2021-01-22 20:59 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 23+ messages in thread From: Heikki Linnakangas @ 2021-01-22 20:59 UTC (permalink / raw) --- src/backend/utils/sort/tuplesort.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index 14104643e78..aff2f201f52 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -2658,7 +2658,7 @@ merge_read_buffer_size(int64 avail_mem, int nInputTapes, int nInputRuns, * we derive the input buffer size from the amount of memory available, * and M and N. */ - return (avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes; + return Max((avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes, 0); } /* -- 2.29.2 --------------631E0A48DCAEA386819DFCEC-- ^ permalink raw reply [nested|flat] 23+ messages in thread
* [PATCH v3 3/3] Fix sizing of tape read buffers. @ 2021-01-22 20:59 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 23+ messages in thread From: Heikki Linnakangas @ 2021-01-22 20:59 UTC (permalink / raw) --- src/backend/utils/sort/tuplesort.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index 14104643e78..aff2f201f52 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -2658,7 +2658,7 @@ merge_read_buffer_size(int64 avail_mem, int nInputTapes, int nInputRuns, * we derive the input buffer size from the amount of memory available, * and M and N. */ - return (avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes; + return Max((avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes, 0); } /* -- 2.29.2 --------------631E0A48DCAEA386819DFCEC-- ^ permalink raw reply [nested|flat] 23+ messages in thread
* [PATCH v3 3/3] Fix sizing of tape read buffers. @ 2021-01-22 20:59 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 23+ messages in thread From: Heikki Linnakangas @ 2021-01-22 20:59 UTC (permalink / raw) --- src/backend/utils/sort/tuplesort.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index 14104643e78..aff2f201f52 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -2658,7 +2658,7 @@ merge_read_buffer_size(int64 avail_mem, int nInputTapes, int nInputRuns, * we derive the input buffer size from the amount of memory available, * and M and N. */ - return (avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes; + return Max((avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes, 0); } /* -- 2.29.2 --------------631E0A48DCAEA386819DFCEC-- ^ permalink raw reply [nested|flat] 23+ messages in thread
* [PATCH v3 3/3] Fix sizing of tape read buffers. @ 2021-01-22 20:59 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 23+ messages in thread From: Heikki Linnakangas @ 2021-01-22 20:59 UTC (permalink / raw) --- src/backend/utils/sort/tuplesort.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index 14104643e78..aff2f201f52 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -2658,7 +2658,7 @@ merge_read_buffer_size(int64 avail_mem, int nInputTapes, int nInputRuns, * we derive the input buffer size from the amount of memory available, * and M and N. */ - return (avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes; + return Max((avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes, 0); } /* -- 2.29.2 --------------631E0A48DCAEA386819DFCEC-- ^ permalink raw reply [nested|flat] 23+ messages in thread
* [PATCH v3 3/3] Fix sizing of tape read buffers. @ 2021-01-22 20:59 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 23+ messages in thread From: Heikki Linnakangas @ 2021-01-22 20:59 UTC (permalink / raw) --- src/backend/utils/sort/tuplesort.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index 14104643e78..aff2f201f52 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -2658,7 +2658,7 @@ merge_read_buffer_size(int64 avail_mem, int nInputTapes, int nInputRuns, * we derive the input buffer size from the amount of memory available, * and M and N. */ - return (avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes; + return Max((avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes, 0); } /* -- 2.29.2 --------------631E0A48DCAEA386819DFCEC-- ^ permalink raw reply [nested|flat] 23+ messages in thread
* [PATCH v3 3/3] Fix sizing of tape read buffers. @ 2021-01-22 20:59 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 23+ messages in thread From: Heikki Linnakangas @ 2021-01-22 20:59 UTC (permalink / raw) --- src/backend/utils/sort/tuplesort.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index 14104643e78..aff2f201f52 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -2658,7 +2658,7 @@ merge_read_buffer_size(int64 avail_mem, int nInputTapes, int nInputRuns, * we derive the input buffer size from the amount of memory available, * and M and N. */ - return (avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes; + return Max((avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes, 0); } /* -- 2.29.2 --------------631E0A48DCAEA386819DFCEC-- ^ permalink raw reply [nested|flat] 23+ messages in thread
* [PATCH v3 3/3] Fix sizing of tape read buffers. @ 2021-01-22 20:59 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 23+ messages in thread From: Heikki Linnakangas @ 2021-01-22 20:59 UTC (permalink / raw) --- src/backend/utils/sort/tuplesort.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index 14104643e78..aff2f201f52 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -2658,7 +2658,7 @@ merge_read_buffer_size(int64 avail_mem, int nInputTapes, int nInputRuns, * we derive the input buffer size from the amount of memory available, * and M and N. */ - return (avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes; + return Max((avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes, 0); } /* -- 2.29.2 --------------631E0A48DCAEA386819DFCEC-- ^ permalink raw reply [nested|flat] 23+ messages in thread
* [PATCH v3 3/3] Fix sizing of tape read buffers. @ 2021-01-22 20:59 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 23+ messages in thread From: Heikki Linnakangas @ 2021-01-22 20:59 UTC (permalink / raw) --- src/backend/utils/sort/tuplesort.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index 14104643e78..aff2f201f52 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -2658,7 +2658,7 @@ merge_read_buffer_size(int64 avail_mem, int nInputTapes, int nInputRuns, * we derive the input buffer size from the amount of memory available, * and M and N. */ - return (avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes; + return Max((avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes, 0); } /* -- 2.29.2 --------------631E0A48DCAEA386819DFCEC-- ^ permalink raw reply [nested|flat] 23+ messages in thread
* [PATCH v3 3/3] Fix sizing of tape read buffers. @ 2021-01-22 20:59 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 23+ messages in thread From: Heikki Linnakangas @ 2021-01-22 20:59 UTC (permalink / raw) --- src/backend/utils/sort/tuplesort.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index 14104643e78..aff2f201f52 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -2658,7 +2658,7 @@ merge_read_buffer_size(int64 avail_mem, int nInputTapes, int nInputRuns, * we derive the input buffer size from the amount of memory available, * and M and N. */ - return (avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes; + return Max((avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes, 0); } /* -- 2.29.2 --------------631E0A48DCAEA386819DFCEC-- ^ permalink raw reply [nested|flat] 23+ messages in thread
* [PATCH v3 3/3] Fix sizing of tape read buffers. @ 2021-01-22 20:59 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 23+ messages in thread From: Heikki Linnakangas @ 2021-01-22 20:59 UTC (permalink / raw) --- src/backend/utils/sort/tuplesort.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index 14104643e78..aff2f201f52 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -2658,7 +2658,7 @@ merge_read_buffer_size(int64 avail_mem, int nInputTapes, int nInputRuns, * we derive the input buffer size from the amount of memory available, * and M and N. */ - return (avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes; + return Max((avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes, 0); } /* -- 2.29.2 --------------631E0A48DCAEA386819DFCEC-- ^ permalink raw reply [nested|flat] 23+ messages in thread
* [PATCH v3 3/3] Fix sizing of tape read buffers. @ 2021-01-22 20:59 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 23+ messages in thread From: Heikki Linnakangas @ 2021-01-22 20:59 UTC (permalink / raw) --- src/backend/utils/sort/tuplesort.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index 14104643e78..aff2f201f52 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -2658,7 +2658,7 @@ merge_read_buffer_size(int64 avail_mem, int nInputTapes, int nInputRuns, * we derive the input buffer size from the amount of memory available, * and M and N. */ - return (avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes; + return Max((avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes, 0); } /* -- 2.29.2 --------------631E0A48DCAEA386819DFCEC-- ^ permalink raw reply [nested|flat] 23+ messages in thread
* [PATCH v3 3/3] Fix sizing of tape read buffers. @ 2021-01-22 20:59 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 23+ messages in thread From: Heikki Linnakangas @ 2021-01-22 20:59 UTC (permalink / raw) --- src/backend/utils/sort/tuplesort.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index 14104643e78..aff2f201f52 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -2658,7 +2658,7 @@ merge_read_buffer_size(int64 avail_mem, int nInputTapes, int nInputRuns, * we derive the input buffer size from the amount of memory available, * and M and N. */ - return (avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes; + return Max((avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes, 0); } /* -- 2.29.2 --------------631E0A48DCAEA386819DFCEC-- ^ permalink raw reply [nested|flat] 23+ messages in thread
* [PATCH v3 3/3] Fix sizing of tape read buffers. @ 2021-01-22 20:59 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 23+ messages in thread From: Heikki Linnakangas @ 2021-01-22 20:59 UTC (permalink / raw) --- src/backend/utils/sort/tuplesort.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index 14104643e78..aff2f201f52 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -2658,7 +2658,7 @@ merge_read_buffer_size(int64 avail_mem, int nInputTapes, int nInputRuns, * we derive the input buffer size from the amount of memory available, * and M and N. */ - return (avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes; + return Max((avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes, 0); } /* -- 2.29.2 --------------631E0A48DCAEA386819DFCEC-- ^ permalink raw reply [nested|flat] 23+ messages in thread
* [PATCH v3 3/3] Fix sizing of tape read buffers. @ 2021-01-22 20:59 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 23+ messages in thread From: Heikki Linnakangas @ 2021-01-22 20:59 UTC (permalink / raw) --- src/backend/utils/sort/tuplesort.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index 14104643e78..aff2f201f52 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -2658,7 +2658,7 @@ merge_read_buffer_size(int64 avail_mem, int nInputTapes, int nInputRuns, * we derive the input buffer size from the amount of memory available, * and M and N. */ - return (avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes; + return Max((avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes, 0); } /* -- 2.29.2 --------------631E0A48DCAEA386819DFCEC-- ^ permalink raw reply [nested|flat] 23+ messages in thread
* [PATCH v3 3/3] Fix sizing of tape read buffers. @ 2021-01-22 20:59 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 23+ messages in thread From: Heikki Linnakangas @ 2021-01-22 20:59 UTC (permalink / raw) --- src/backend/utils/sort/tuplesort.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index 14104643e78..aff2f201f52 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -2658,7 +2658,7 @@ merge_read_buffer_size(int64 avail_mem, int nInputTapes, int nInputRuns, * we derive the input buffer size from the amount of memory available, * and M and N. */ - return (avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes; + return Max((avail_mem - TAPE_BUFFER_OVERHEAD * nOutputTapes) / nInputTapes, 0); } /* -- 2.29.2 --------------631E0A48DCAEA386819DFCEC-- ^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: pg_stats and range statistics @ 2023-01-21 18:53 Egor Rogov <[email protected]> 2023-01-22 18:19 ` Re: pg_stats and range statistics Tomas Vondra <[email protected]> 0 siblings, 1 reply; 23+ messages in thread From: Egor Rogov @ 2023-01-21 18:53 UTC (permalink / raw) To: Tomas Vondra <[email protected]>; Soumyadeep Chakraborty <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> Hi Tomas, On 21.01.2023 00:50, Tomas Vondra wrote: > Hi Egor, > > While reviewing a patch improving join estimates for ranges [1] I > realized we don't show stats for ranges in pg_stats, and I recalled we > had this patch. > > I rebased the v2, and I decided to took a stab at showing separate > histograms for lower/upper histogram bounds. I believe it makes it way > more readable, which is what pg_stats is about IMHO. Thanks for looking into this. I have to admit it looks much better this way, so +1. > This simply adds two functions, accepting/producing anyarray - one for > lower bounds, one for upper bounds. I don't think it can be done with a > plain subquery (or at least I don't know how). Anyarray is an alien to SQL, so functions are well justified here. What makes me a bit uneasy is two almost identical functions. Should we consider other options like a function with an additional parameter or a function returning an array of bounds arrays (which is somewhat wasteful, but probably it doesn't matter much here)? > Finally, it renames the empty_range_frac to start with range_, per the > earlier discussion. I wonder if the new column names for lower/upper > bounds (range_lower_bounds_histograms/range_upper_bounds_histograms) are > too long ... It seems so. The ending -s should be left out since it's a single histogram now. And I think that range_lower_histogram/range_upper_histogram are descriptive enough. I'm adding one more patch to shorten the column names, refresh the docs, and make 'make check' happy (unfortunately, we have to edit src/regress/expected/rules.out every time pg_stats definition changes). > > regards > > [1] https://commitfest.postgresql.org/41/3821/ > From b339c0eab5616cec61e9d9e85398034861608d30 Mon Sep 17 00:00:00 2001 From: Tomas Vondra <[email protected]> Date: Fri, 20 Jan 2023 20:50:41 +0100 Subject: [PATCH 1/3] Display length and bounds histograms in pg_stats Values corresponding to STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM and STATISTIC_KIND_BOUNDS_HISTOGRAM were not exposed to pg_stats when these slot kinds were introduced in 918eee0c49. This commit adds the missing fields to pg_stats. TODO: catalog version bump --- doc/src/sgml/catalogs.sgml | 32 ++++++++++++++++++++++++++++ src/backend/catalog/system_views.sql | 23 +++++++++++++++++++- src/include/catalog/pg_statistic.h | 3 +++ src/test/regress/expected/rules.out | 26 +++++++++++++++++++++- 4 files changed, 82 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index c1e4048054e..c8bd84c56eb 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -9634,6 +9634,38 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l User mapping specific options, as <quote>keyword=value</quote> strings </para></entry> </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>range_length_histogram</structfield> <type>anyarray</type> + </para> + <para> + A histogram of the lengths of non-empty and non-null range values of a + range type column. (Null for non-range types.) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>empty_range_frac</structfield> <type>float4</type> + </para> + <para> + Fraction of column entries whose values are empty ranges. + (Null for non-range types.) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>range_bounds_histograms</structfield> <type>anyarray</type> + </para> + <para> + Histograms of lower and upper bounds of non-empty, non-null ranges, + combined into a single array of range values. The lower and upper bounds + of each value correspond to the histograms of lower and upper bounds + respectively. (Null for non-range types.) + </para></entry> + </row> </tbody> </tgroup> </table> diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 8608e3fa5b1..ccd6c7ffdb7 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -243,7 +243,28 @@ CREATE VIEW pg_stats WITH (security_barrier) AS WHEN stakind3 = 5 THEN stanumbers3 WHEN stakind4 = 5 THEN stanumbers4 WHEN stakind5 = 5 THEN stanumbers5 - END AS elem_count_histogram + END AS elem_count_histogram, + CASE + WHEN stakind1 = 6 THEN stavalues1 + WHEN stakind2 = 6 THEN stavalues2 + WHEN stakind3 = 6 THEN stavalues3 + WHEN stakind4 = 6 THEN stavalues4 + WHEN stakind5 = 6 THEN stavalues5 + END AS range_length_histogram, + CASE + WHEN stakind1 = 6 THEN stanumbers1[1] + WHEN stakind2 = 6 THEN stanumbers2[1] + WHEN stakind3 = 6 THEN stanumbers3[1] + WHEN stakind4 = 6 THEN stanumbers4[1] + WHEN stakind5 = 6 THEN stanumbers5[1] + END AS empty_range_frac, + CASE + WHEN stakind1 = 7 THEN stavalues1 + WHEN stakind2 = 7 THEN stavalues2 + WHEN stakind3 = 7 THEN stavalues3 + WHEN stakind4 = 7 THEN stavalues4 + WHEN stakind5 = 7 THEN stavalues5 + END AS range_bounds_histograms FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid) JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum) LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) diff --git a/src/include/catalog/pg_statistic.h b/src/include/catalog/pg_statistic.h index 8770c5b4c60..10401dece0d 100644 --- a/src/include/catalog/pg_statistic.h +++ b/src/include/catalog/pg_statistic.h @@ -152,6 +152,9 @@ DECLARE_FOREIGN_KEY((starelid, staattnum), pg_attribute, (attrelid, attnum)); * data "kind" will appear in any particular slot. Instead, search the * stakind fields to see if the desired data is available. (The standard * function get_attstatsslot() may be used for this.) + * + * Note: The pg_stats view needs to be modified whenever a new slot kind is + * added to core. */ /* diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index e7a2f5856aa..ced5933271c 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2436,7 +2436,31 @@ pg_stats| SELECT n.nspname AS schemaname, WHEN (s.stakind4 = 5) THEN s.stanumbers4 WHEN (s.stakind5 = 5) THEN s.stanumbers5 ELSE NULL::real[] - END AS elem_count_histogram + END AS elem_count_histogram, + CASE + WHEN (s.stakind1 = 6) THEN s.stavalues1 + WHEN (s.stakind2 = 6) THEN s.stavalues2 + WHEN (s.stakind3 = 6) THEN s.stavalues3 + WHEN (s.stakind4 = 6) THEN s.stavalues4 + WHEN (s.stakind5 = 6) THEN s.stavalues5 + ELSE NULL::anyarray + END AS range_length_histogram, + CASE + WHEN (s.stakind1 = 6) THEN s.stanumbers1[1] + WHEN (s.stakind2 = 6) THEN s.stanumbers2[1] + WHEN (s.stakind3 = 6) THEN s.stanumbers3[1] + WHEN (s.stakind4 = 6) THEN s.stanumbers4[1] + WHEN (s.stakind5 = 6) THEN s.stanumbers5[1] + ELSE NULL::real + END AS empty_range_frac, + CASE + WHEN (s.stakind1 = 7) THEN s.stavalues1 + WHEN (s.stakind2 = 7) THEN s.stavalues2 + WHEN (s.stakind3 = 7) THEN s.stavalues3 + WHEN (s.stakind4 = 7) THEN s.stavalues4 + WHEN (s.stakind5 = 7) THEN s.stavalues5 + ELSE NULL::anyarray + END AS range_bounds_histograms FROM (((pg_statistic s JOIN pg_class c ON ((c.oid = s.starelid))) JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) -- 2.39.0 From 9bd2bbef9dc092ab5fdace74e26f3afeaa62830d Mon Sep 17 00:00:00 2001 From: Tomas Vondra <[email protected]> Date: Fri, 20 Jan 2023 22:29:53 +0100 Subject: [PATCH 2/3] add functions to extract lower/upper ramge bounds --- src/backend/catalog/system_views.sql | 19 ++- src/backend/utils/adt/rangetypes_typanalyze.c | 118 ++++++++++++++++++ src/include/catalog/pg_proc.dat | 10 ++ 3 files changed, 141 insertions(+), 6 deletions(-) diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index ccd6c7ffdb7..e3314550e32 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -259,12 +259,19 @@ CREATE VIEW pg_stats WITH (security_barrier) AS WHEN stakind5 = 6 THEN stanumbers5[1] END AS empty_range_frac, CASE - WHEN stakind1 = 7 THEN stavalues1 - WHEN stakind2 = 7 THEN stavalues2 - WHEN stakind3 = 7 THEN stavalues3 - WHEN stakind4 = 7 THEN stavalues4 - WHEN stakind5 = 7 THEN stavalues5 - END AS range_bounds_histograms + WHEN stakind1 = 7 THEN ranges_lower_bounds(stavalues1) + WHEN stakind2 = 7 THEN ranges_lower_bounds(stavalues2) + WHEN stakind3 = 7 THEN ranges_lower_bounds(stavalues3) + WHEN stakind4 = 7 THEN ranges_lower_bounds(stavalues4) + WHEN stakind5 = 7 THEN ranges_lower_bounds(stavalues5) + END AS range_lower_bounds_histograms, + CASE + WHEN stakind1 = 7 THEN ranges_upper_bounds(stavalues1) + WHEN stakind2 = 7 THEN ranges_upper_bounds(stavalues2) + WHEN stakind3 = 7 THEN ranges_upper_bounds(stavalues3) + WHEN stakind4 = 7 THEN ranges_upper_bounds(stavalues4) + WHEN stakind5 = 7 THEN ranges_upper_bounds(stavalues5) + END AS range_upper_bounds_histograms FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid) JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum) LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) diff --git a/src/backend/utils/adt/rangetypes_typanalyze.c b/src/backend/utils/adt/rangetypes_typanalyze.c index 86810a1a6e6..a0097282fc0 100644 --- a/src/backend/utils/adt/rangetypes_typanalyze.c +++ b/src/backend/utils/adt/rangetypes_typanalyze.c @@ -26,6 +26,8 @@ #include "catalog/pg_operator.h" #include "commands/vacuum.h" +#include "utils/array.h" +#include "utils/arrayaccess.h" #include "utils/float.h" #include "utils/fmgrprotos.h" #include "utils/lsyscache.h" @@ -427,3 +429,119 @@ compute_range_stats(VacAttrStats *stats, AnalyzeAttrFetchFunc fetchfunc, * hashtable should also go away, as it used a child memory context. */ } + +Datum +ranges_lower_bounds(PG_FUNCTION_ARGS) +{ + AnyArrayType *array = PG_GETARG_ANY_ARRAY_P(0); + int ndims = AARR_NDIM(array); + int *dims = AARR_DIMS(array); + Oid element_type = AARR_ELEMTYPE(array); + int i; + array_iter iter; + int nelems; + Datum *elems; + TypeCacheEntry *typentry; + TypeCacheEntry *typentry_element; + + /* Get information about range type; note column might be a domain */ + typentry = range_get_typcache(fcinfo, getBaseType(element_type)); + + if (typentry->typtype != TYPTYPE_RANGE) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("expected array of ranges"))); + + ndims = AARR_NDIM(array); + dims = AARR_DIMS(array); + nelems = ArrayGetNItems(ndims, dims); + + elems = (Datum *) palloc(nelems * sizeof(Datum)); + + array_iter_setup(&iter, array); + + for (i = 0; i < nelems; i++) + { + Datum itemvalue; + bool isnull; + RangeBound lower; + RangeBound upper; + bool empty; + + /* Get source element, checking for NULL */ + itemvalue = array_iter_next(&iter, &isnull, i, + typentry->typlen, typentry->typbyval, + typentry->typalign); + + Assert(!isnull); + + range_deserialize(typentry, (RangeType *) itemvalue, &lower, &upper, &empty); + elems[i] = lower.val; + } + + typentry_element = typentry->rngelemtype; + + PG_RETURN_ARRAYTYPE_P(construct_array(elems, nelems, + typentry_element->type_id, + typentry_element->typlen, + typentry_element->typbyval, + typentry_element->typalign)); +} + +Datum +ranges_upper_bounds(PG_FUNCTION_ARGS) +{ + AnyArrayType *array = PG_GETARG_ANY_ARRAY_P(0); + int ndims = AARR_NDIM(array); + int *dims = AARR_DIMS(array); + Oid element_type = AARR_ELEMTYPE(array); + int i; + array_iter iter; + int nelems; + Datum *elems; + TypeCacheEntry *typentry; + TypeCacheEntry *typentry_element; + + /* Get information about range type; note column might be a domain */ + typentry = range_get_typcache(fcinfo, getBaseType(element_type)); + + if (typentry->typtype != TYPTYPE_RANGE) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("expected array of ranges"))); + + ndims = AARR_NDIM(array); + dims = AARR_DIMS(array); + nelems = ArrayGetNItems(ndims, dims); + + elems = (Datum *) palloc(nelems * sizeof(Datum)); + + array_iter_setup(&iter, array); + + for (i = 0; i < nelems; i++) + { + Datum itemvalue; + bool isnull; + RangeBound lower; + RangeBound upper; + bool empty; + + /* Get source element, checking for NULL */ + itemvalue = array_iter_next(&iter, &isnull, i, + typentry->typlen, typentry->typbyval, + typentry->typalign); + + Assert(!isnull); + + range_deserialize(typentry, (RangeType *) itemvalue, &lower, &upper, &empty); + elems[i] = upper.val; + } + + typentry_element = typentry->rngelemtype; + + PG_RETURN_ARRAYTYPE_P(construct_array(elems, nelems, + typentry_element->type_id, + typentry_element->typlen, + typentry_element->typbyval, + typentry_element->typalign)); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 86eb8e8c58a..161557e4cb2 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -11891,4 +11891,14 @@ prorettype => 'bytea', proargtypes => 'pg_brin_minmax_multi_summary', prosrc => 'brin_minmax_multi_summary_send' }, +{ oid => '9693', descr => 'lower bounds of ranges', + proname => 'ranges_lower_bounds', provolatile => 's', + prorettype => 'anyarray', proargtypes => 'anyarray', + prosrc => 'ranges_lower_bounds' }, + +{ oid => '9694', descr => 'upper bounds of ranges', + proname => 'ranges_upper_bounds', provolatile => 's', + prorettype => 'anyarray', proargtypes => 'anyarray', + prosrc => 'ranges_upper_bounds' }, + ] -- 2.39.0 From 600fed1faae369b8291ec3a7fc64f6ad8099c124 Mon Sep 17 00:00:00 2001 From: Tomas Vondra <[email protected]> Date: Fri, 20 Jan 2023 22:40:28 +0100 Subject: [PATCH 3/3] rename empty_range_frac --- src/backend/catalog/system_views.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index e3314550e32..ac99d41f808 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -257,7 +257,7 @@ CREATE VIEW pg_stats WITH (security_barrier) AS WHEN stakind3 = 6 THEN stanumbers3[1] WHEN stakind4 = 6 THEN stanumbers4[1] WHEN stakind5 = 6 THEN stanumbers5[1] - END AS empty_range_frac, + END AS range_empty_frac, CASE WHEN stakind1 = 7 THEN ranges_lower_bounds(stavalues1) WHEN stakind2 = 7 THEN ranges_lower_bounds(stavalues2) -- 2.39.0 diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index c8bd84c56e..e0851c52b4 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -9647,7 +9647,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <row> <entry role="catalog_table_entry"><para role="column_definition"> - <structfield>empty_range_frac</structfield> <type>float4</type> + <structfield>range_empty_frac</structfield> <type>float4</type> </para> <para> Fraction of column entries whose values are empty ranges. @@ -9657,13 +9657,21 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <row> <entry role="catalog_table_entry"><para role="column_definition"> - <structfield>range_bounds_histograms</structfield> <type>anyarray</type> + <structfield>range_lower_histogram</structfield> <type>anyarray</type> </para> <para> - Histograms of lower and upper bounds of non-empty, non-null ranges, - combined into a single array of range values. The lower and upper bounds - of each value correspond to the histograms of lower and upper bounds - respectively. (Null for non-range types.) + A histogram of lower bounds of non-empty and non-null range values. + (Null for non-range types.) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>range_upper_histogram</structfield> <type>anyarray</type> + </para> + <para> + A histogram of upper bounds of non-empty and non-null range values. + (Null for non-range types.) </para></entry> </row> </tbody> diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index ac99d41f80..b3b9e64ae2 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -264,14 +264,14 @@ CREATE VIEW pg_stats WITH (security_barrier) AS WHEN stakind3 = 7 THEN ranges_lower_bounds(stavalues3) WHEN stakind4 = 7 THEN ranges_lower_bounds(stavalues4) WHEN stakind5 = 7 THEN ranges_lower_bounds(stavalues5) - END AS range_lower_bounds_histograms, + END AS range_lower_histogram, CASE WHEN stakind1 = 7 THEN ranges_upper_bounds(stavalues1) WHEN stakind2 = 7 THEN ranges_upper_bounds(stavalues2) WHEN stakind3 = 7 THEN ranges_upper_bounds(stavalues3) WHEN stakind4 = 7 THEN ranges_upper_bounds(stavalues4) WHEN stakind5 = 7 THEN ranges_upper_bounds(stavalues5) - END AS range_upper_bounds_histograms + END AS range_upper_histogram FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid) JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum) LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index ced5933271..26087dfdf5 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2452,15 +2452,23 @@ pg_stats| SELECT n.nspname AS schemaname, WHEN (s.stakind4 = 6) THEN s.stanumbers4[1] WHEN (s.stakind5 = 6) THEN s.stanumbers5[1] ELSE NULL::real - END AS empty_range_frac, + END AS range_empty_frac, CASE - WHEN (s.stakind1 = 7) THEN s.stavalues1 - WHEN (s.stakind2 = 7) THEN s.stavalues2 - WHEN (s.stakind3 = 7) THEN s.stavalues3 - WHEN (s.stakind4 = 7) THEN s.stavalues4 - WHEN (s.stakind5 = 7) THEN s.stavalues5 + WHEN (s.stakind1 = 7) THEN ranges_lower_bounds(s.stavalues1) + WHEN (s.stakind2 = 7) THEN ranges_lower_bounds(s.stavalues2) + WHEN (s.stakind3 = 7) THEN ranges_lower_bounds(s.stavalues3) + WHEN (s.stakind4 = 7) THEN ranges_lower_bounds(s.stavalues4) + WHEN (s.stakind5 = 7) THEN ranges_lower_bounds(s.stavalues5) ELSE NULL::anyarray - END AS range_bounds_histograms + END AS range_lower_histogram, + CASE + WHEN (s.stakind1 = 7) THEN ranges_upper_bounds(s.stavalues1) + WHEN (s.stakind2 = 7) THEN ranges_upper_bounds(s.stavalues2) + WHEN (s.stakind3 = 7) THEN ranges_upper_bounds(s.stavalues3) + WHEN (s.stakind4 = 7) THEN ranges_upper_bounds(s.stavalues4) + WHEN (s.stakind5 = 7) THEN ranges_upper_bounds(s.stavalues5) + ELSE NULL::anyarray + END AS range_upper_histogram FROM (((pg_statistic s JOIN pg_class c ON ((c.oid = s.starelid))) JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) Attachments: [text/plain] 0001-Display-length-and-bounds-histograms-in-pg_-20230120.patch (6.2K, ../../[email protected]/2-0001-Display-length-and-bounds-histograms-in-pg_-20230120.patch) download | inline diff: From b339c0eab5616cec61e9d9e85398034861608d30 Mon Sep 17 00:00:00 2001 From: Tomas Vondra <[email protected]> Date: Fri, 20 Jan 2023 20:50:41 +0100 Subject: [PATCH 1/3] Display length and bounds histograms in pg_stats Values corresponding to STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM and STATISTIC_KIND_BOUNDS_HISTOGRAM were not exposed to pg_stats when these slot kinds were introduced in 918eee0c49. This commit adds the missing fields to pg_stats. TODO: catalog version bump --- doc/src/sgml/catalogs.sgml | 32 ++++++++++++++++++++++++++++ src/backend/catalog/system_views.sql | 23 +++++++++++++++++++- src/include/catalog/pg_statistic.h | 3 +++ src/test/regress/expected/rules.out | 26 +++++++++++++++++++++- 4 files changed, 82 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index c1e4048054e..c8bd84c56eb 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -9634,6 +9634,38 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l User mapping specific options, as <quote>keyword=value</quote> strings </para></entry> </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>range_length_histogram</structfield> <type>anyarray</type> + </para> + <para> + A histogram of the lengths of non-empty and non-null range values of a + range type column. (Null for non-range types.) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>empty_range_frac</structfield> <type>float4</type> + </para> + <para> + Fraction of column entries whose values are empty ranges. + (Null for non-range types.) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>range_bounds_histograms</structfield> <type>anyarray</type> + </para> + <para> + Histograms of lower and upper bounds of non-empty, non-null ranges, + combined into a single array of range values. The lower and upper bounds + of each value correspond to the histograms of lower and upper bounds + respectively. (Null for non-range types.) + </para></entry> + </row> </tbody> </tgroup> </table> diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 8608e3fa5b1..ccd6c7ffdb7 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -243,7 +243,28 @@ CREATE VIEW pg_stats WITH (security_barrier) AS WHEN stakind3 = 5 THEN stanumbers3 WHEN stakind4 = 5 THEN stanumbers4 WHEN stakind5 = 5 THEN stanumbers5 - END AS elem_count_histogram + END AS elem_count_histogram, + CASE + WHEN stakind1 = 6 THEN stavalues1 + WHEN stakind2 = 6 THEN stavalues2 + WHEN stakind3 = 6 THEN stavalues3 + WHEN stakind4 = 6 THEN stavalues4 + WHEN stakind5 = 6 THEN stavalues5 + END AS range_length_histogram, + CASE + WHEN stakind1 = 6 THEN stanumbers1[1] + WHEN stakind2 = 6 THEN stanumbers2[1] + WHEN stakind3 = 6 THEN stanumbers3[1] + WHEN stakind4 = 6 THEN stanumbers4[1] + WHEN stakind5 = 6 THEN stanumbers5[1] + END AS empty_range_frac, + CASE + WHEN stakind1 = 7 THEN stavalues1 + WHEN stakind2 = 7 THEN stavalues2 + WHEN stakind3 = 7 THEN stavalues3 + WHEN stakind4 = 7 THEN stavalues4 + WHEN stakind5 = 7 THEN stavalues5 + END AS range_bounds_histograms FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid) JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum) LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) diff --git a/src/include/catalog/pg_statistic.h b/src/include/catalog/pg_statistic.h index 8770c5b4c60..10401dece0d 100644 --- a/src/include/catalog/pg_statistic.h +++ b/src/include/catalog/pg_statistic.h @@ -152,6 +152,9 @@ DECLARE_FOREIGN_KEY((starelid, staattnum), pg_attribute, (attrelid, attnum)); * data "kind" will appear in any particular slot. Instead, search the * stakind fields to see if the desired data is available. (The standard * function get_attstatsslot() may be used for this.) + * + * Note: The pg_stats view needs to be modified whenever a new slot kind is + * added to core. */ /* diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index e7a2f5856aa..ced5933271c 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2436,7 +2436,31 @@ pg_stats| SELECT n.nspname AS schemaname, WHEN (s.stakind4 = 5) THEN s.stanumbers4 WHEN (s.stakind5 = 5) THEN s.stanumbers5 ELSE NULL::real[] - END AS elem_count_histogram + END AS elem_count_histogram, + CASE + WHEN (s.stakind1 = 6) THEN s.stavalues1 + WHEN (s.stakind2 = 6) THEN s.stavalues2 + WHEN (s.stakind3 = 6) THEN s.stavalues3 + WHEN (s.stakind4 = 6) THEN s.stavalues4 + WHEN (s.stakind5 = 6) THEN s.stavalues5 + ELSE NULL::anyarray + END AS range_length_histogram, + CASE + WHEN (s.stakind1 = 6) THEN s.stanumbers1[1] + WHEN (s.stakind2 = 6) THEN s.stanumbers2[1] + WHEN (s.stakind3 = 6) THEN s.stanumbers3[1] + WHEN (s.stakind4 = 6) THEN s.stanumbers4[1] + WHEN (s.stakind5 = 6) THEN s.stanumbers5[1] + ELSE NULL::real + END AS empty_range_frac, + CASE + WHEN (s.stakind1 = 7) THEN s.stavalues1 + WHEN (s.stakind2 = 7) THEN s.stavalues2 + WHEN (s.stakind3 = 7) THEN s.stavalues3 + WHEN (s.stakind4 = 7) THEN s.stavalues4 + WHEN (s.stakind5 = 7) THEN s.stavalues5 + ELSE NULL::anyarray + END AS range_bounds_histograms FROM (((pg_statistic s JOIN pg_class c ON ((c.oid = s.starelid))) JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) -- 2.39.0 [text/plain] 0002-add-functions-to-extract-lower-upper-ramge--20230120.patch (6.4K, ../../[email protected]/3-0002-add-functions-to-extract-lower-upper-ramge--20230120.patch) download | inline diff: From 9bd2bbef9dc092ab5fdace74e26f3afeaa62830d Mon Sep 17 00:00:00 2001 From: Tomas Vondra <[email protected]> Date: Fri, 20 Jan 2023 22:29:53 +0100 Subject: [PATCH 2/3] add functions to extract lower/upper ramge bounds --- src/backend/catalog/system_views.sql | 19 ++- src/backend/utils/adt/rangetypes_typanalyze.c | 118 ++++++++++++++++++ src/include/catalog/pg_proc.dat | 10 ++ 3 files changed, 141 insertions(+), 6 deletions(-) diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index ccd6c7ffdb7..e3314550e32 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -259,12 +259,19 @@ CREATE VIEW pg_stats WITH (security_barrier) AS WHEN stakind5 = 6 THEN stanumbers5[1] END AS empty_range_frac, CASE - WHEN stakind1 = 7 THEN stavalues1 - WHEN stakind2 = 7 THEN stavalues2 - WHEN stakind3 = 7 THEN stavalues3 - WHEN stakind4 = 7 THEN stavalues4 - WHEN stakind5 = 7 THEN stavalues5 - END AS range_bounds_histograms + WHEN stakind1 = 7 THEN ranges_lower_bounds(stavalues1) + WHEN stakind2 = 7 THEN ranges_lower_bounds(stavalues2) + WHEN stakind3 = 7 THEN ranges_lower_bounds(stavalues3) + WHEN stakind4 = 7 THEN ranges_lower_bounds(stavalues4) + WHEN stakind5 = 7 THEN ranges_lower_bounds(stavalues5) + END AS range_lower_bounds_histograms, + CASE + WHEN stakind1 = 7 THEN ranges_upper_bounds(stavalues1) + WHEN stakind2 = 7 THEN ranges_upper_bounds(stavalues2) + WHEN stakind3 = 7 THEN ranges_upper_bounds(stavalues3) + WHEN stakind4 = 7 THEN ranges_upper_bounds(stavalues4) + WHEN stakind5 = 7 THEN ranges_upper_bounds(stavalues5) + END AS range_upper_bounds_histograms FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid) JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum) LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) diff --git a/src/backend/utils/adt/rangetypes_typanalyze.c b/src/backend/utils/adt/rangetypes_typanalyze.c index 86810a1a6e6..a0097282fc0 100644 --- a/src/backend/utils/adt/rangetypes_typanalyze.c +++ b/src/backend/utils/adt/rangetypes_typanalyze.c @@ -26,6 +26,8 @@ #include "catalog/pg_operator.h" #include "commands/vacuum.h" +#include "utils/array.h" +#include "utils/arrayaccess.h" #include "utils/float.h" #include "utils/fmgrprotos.h" #include "utils/lsyscache.h" @@ -427,3 +429,119 @@ compute_range_stats(VacAttrStats *stats, AnalyzeAttrFetchFunc fetchfunc, * hashtable should also go away, as it used a child memory context. */ } + +Datum +ranges_lower_bounds(PG_FUNCTION_ARGS) +{ + AnyArrayType *array = PG_GETARG_ANY_ARRAY_P(0); + int ndims = AARR_NDIM(array); + int *dims = AARR_DIMS(array); + Oid element_type = AARR_ELEMTYPE(array); + int i; + array_iter iter; + int nelems; + Datum *elems; + TypeCacheEntry *typentry; + TypeCacheEntry *typentry_element; + + /* Get information about range type; note column might be a domain */ + typentry = range_get_typcache(fcinfo, getBaseType(element_type)); + + if (typentry->typtype != TYPTYPE_RANGE) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("expected array of ranges"))); + + ndims = AARR_NDIM(array); + dims = AARR_DIMS(array); + nelems = ArrayGetNItems(ndims, dims); + + elems = (Datum *) palloc(nelems * sizeof(Datum)); + + array_iter_setup(&iter, array); + + for (i = 0; i < nelems; i++) + { + Datum itemvalue; + bool isnull; + RangeBound lower; + RangeBound upper; + bool empty; + + /* Get source element, checking for NULL */ + itemvalue = array_iter_next(&iter, &isnull, i, + typentry->typlen, typentry->typbyval, + typentry->typalign); + + Assert(!isnull); + + range_deserialize(typentry, (RangeType *) itemvalue, &lower, &upper, &empty); + elems[i] = lower.val; + } + + typentry_element = typentry->rngelemtype; + + PG_RETURN_ARRAYTYPE_P(construct_array(elems, nelems, + typentry_element->type_id, + typentry_element->typlen, + typentry_element->typbyval, + typentry_element->typalign)); +} + +Datum +ranges_upper_bounds(PG_FUNCTION_ARGS) +{ + AnyArrayType *array = PG_GETARG_ANY_ARRAY_P(0); + int ndims = AARR_NDIM(array); + int *dims = AARR_DIMS(array); + Oid element_type = AARR_ELEMTYPE(array); + int i; + array_iter iter; + int nelems; + Datum *elems; + TypeCacheEntry *typentry; + TypeCacheEntry *typentry_element; + + /* Get information about range type; note column might be a domain */ + typentry = range_get_typcache(fcinfo, getBaseType(element_type)); + + if (typentry->typtype != TYPTYPE_RANGE) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("expected array of ranges"))); + + ndims = AARR_NDIM(array); + dims = AARR_DIMS(array); + nelems = ArrayGetNItems(ndims, dims); + + elems = (Datum *) palloc(nelems * sizeof(Datum)); + + array_iter_setup(&iter, array); + + for (i = 0; i < nelems; i++) + { + Datum itemvalue; + bool isnull; + RangeBound lower; + RangeBound upper; + bool empty; + + /* Get source element, checking for NULL */ + itemvalue = array_iter_next(&iter, &isnull, i, + typentry->typlen, typentry->typbyval, + typentry->typalign); + + Assert(!isnull); + + range_deserialize(typentry, (RangeType *) itemvalue, &lower, &upper, &empty); + elems[i] = upper.val; + } + + typentry_element = typentry->rngelemtype; + + PG_RETURN_ARRAYTYPE_P(construct_array(elems, nelems, + typentry_element->type_id, + typentry_element->typlen, + typentry_element->typbyval, + typentry_element->typalign)); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 86eb8e8c58a..161557e4cb2 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -11891,4 +11891,14 @@ prorettype => 'bytea', proargtypes => 'pg_brin_minmax_multi_summary', prosrc => 'brin_minmax_multi_summary_send' }, +{ oid => '9693', descr => 'lower bounds of ranges', + proname => 'ranges_lower_bounds', provolatile => 's', + prorettype => 'anyarray', proargtypes => 'anyarray', + prosrc => 'ranges_lower_bounds' }, + +{ oid => '9694', descr => 'upper bounds of ranges', + proname => 'ranges_upper_bounds', provolatile => 's', + prorettype => 'anyarray', proargtypes => 'anyarray', + prosrc => 'ranges_upper_bounds' }, + ] -- 2.39.0 [text/plain] 0003-rename-empty_range_frac-20230120.patch (965B, ../../[email protected]/4-0003-rename-empty_range_frac-20230120.patch) download | inline diff: From 600fed1faae369b8291ec3a7fc64f6ad8099c124 Mon Sep 17 00:00:00 2001 From: Tomas Vondra <[email protected]> Date: Fri, 20 Jan 2023 22:40:28 +0100 Subject: [PATCH 3/3] rename empty_range_frac --- src/backend/catalog/system_views.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index e3314550e32..ac99d41f808 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -257,7 +257,7 @@ CREATE VIEW pg_stats WITH (security_barrier) AS WHEN stakind3 = 6 THEN stanumbers3[1] WHEN stakind4 = 6 THEN stanumbers4[1] WHEN stakind5 = 6 THEN stanumbers5[1] - END AS empty_range_frac, + END AS range_empty_frac, CASE WHEN stakind1 = 7 THEN ranges_lower_bounds(stavalues1) WHEN stakind2 = 7 THEN ranges_lower_bounds(stavalues2) -- 2.39.0 [text/plain] 0004-shorten_names-refresh_docs-tests-20230121.patch (4.7K, ../../[email protected]/5-0004-shorten_names-refresh_docs-tests-20230121.patch) download | inline diff: diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index c8bd84c56e..e0851c52b4 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -9647,7 +9647,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <row> <entry role="catalog_table_entry"><para role="column_definition"> - <structfield>empty_range_frac</structfield> <type>float4</type> + <structfield>range_empty_frac</structfield> <type>float4</type> </para> <para> Fraction of column entries whose values are empty ranges. @@ -9657,13 +9657,21 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <row> <entry role="catalog_table_entry"><para role="column_definition"> - <structfield>range_bounds_histograms</structfield> <type>anyarray</type> + <structfield>range_lower_histogram</structfield> <type>anyarray</type> </para> <para> - Histograms of lower and upper bounds of non-empty, non-null ranges, - combined into a single array of range values. The lower and upper bounds - of each value correspond to the histograms of lower and upper bounds - respectively. (Null for non-range types.) + A histogram of lower bounds of non-empty and non-null range values. + (Null for non-range types.) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>range_upper_histogram</structfield> <type>anyarray</type> + </para> + <para> + A histogram of upper bounds of non-empty and non-null range values. + (Null for non-range types.) </para></entry> </row> </tbody> diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index ac99d41f80..b3b9e64ae2 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -264,14 +264,14 @@ CREATE VIEW pg_stats WITH (security_barrier) AS WHEN stakind3 = 7 THEN ranges_lower_bounds(stavalues3) WHEN stakind4 = 7 THEN ranges_lower_bounds(stavalues4) WHEN stakind5 = 7 THEN ranges_lower_bounds(stavalues5) - END AS range_lower_bounds_histograms, + END AS range_lower_histogram, CASE WHEN stakind1 = 7 THEN ranges_upper_bounds(stavalues1) WHEN stakind2 = 7 THEN ranges_upper_bounds(stavalues2) WHEN stakind3 = 7 THEN ranges_upper_bounds(stavalues3) WHEN stakind4 = 7 THEN ranges_upper_bounds(stavalues4) WHEN stakind5 = 7 THEN ranges_upper_bounds(stavalues5) - END AS range_upper_bounds_histograms + END AS range_upper_histogram FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid) JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum) LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index ced5933271..26087dfdf5 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2452,15 +2452,23 @@ pg_stats| SELECT n.nspname AS schemaname, WHEN (s.stakind4 = 6) THEN s.stanumbers4[1] WHEN (s.stakind5 = 6) THEN s.stanumbers5[1] ELSE NULL::real - END AS empty_range_frac, + END AS range_empty_frac, CASE - WHEN (s.stakind1 = 7) THEN s.stavalues1 - WHEN (s.stakind2 = 7) THEN s.stavalues2 - WHEN (s.stakind3 = 7) THEN s.stavalues3 - WHEN (s.stakind4 = 7) THEN s.stavalues4 - WHEN (s.stakind5 = 7) THEN s.stavalues5 + WHEN (s.stakind1 = 7) THEN ranges_lower_bounds(s.stavalues1) + WHEN (s.stakind2 = 7) THEN ranges_lower_bounds(s.stavalues2) + WHEN (s.stakind3 = 7) THEN ranges_lower_bounds(s.stavalues3) + WHEN (s.stakind4 = 7) THEN ranges_lower_bounds(s.stavalues4) + WHEN (s.stakind5 = 7) THEN ranges_lower_bounds(s.stavalues5) ELSE NULL::anyarray - END AS range_bounds_histograms + END AS range_lower_histogram, + CASE + WHEN (s.stakind1 = 7) THEN ranges_upper_bounds(s.stavalues1) + WHEN (s.stakind2 = 7) THEN ranges_upper_bounds(s.stavalues2) + WHEN (s.stakind3 = 7) THEN ranges_upper_bounds(s.stavalues3) + WHEN (s.stakind4 = 7) THEN ranges_upper_bounds(s.stavalues4) + WHEN (s.stakind5 = 7) THEN ranges_upper_bounds(s.stavalues5) + ELSE NULL::anyarray + END AS range_upper_histogram FROM (((pg_statistic s JOIN pg_class c ON ((c.oid = s.starelid))) JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) ^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: pg_stats and range statistics 2023-01-21 18:53 Re: pg_stats and range statistics Egor Rogov <[email protected]> @ 2023-01-22 18:19 ` Tomas Vondra <[email protected]> 0 siblings, 0 replies; 23+ messages in thread From: Tomas Vondra @ 2023-01-22 18:19 UTC (permalink / raw) To: Egor Rogov <[email protected]>; Soumyadeep Chakraborty <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> On 1/21/23 19:53, Egor Rogov wrote: > Hi Tomas, > > On 21.01.2023 00:50, Tomas Vondra wrote: >> Hi Egor, >> >> While reviewing a patch improving join estimates for ranges [1] I >> realized we don't show stats for ranges in pg_stats, and I recalled we >> had this patch. >> >> I rebased the v2, and I decided to took a stab at showing separate >> histograms for lower/upper histogram bounds. I believe it makes it way >> more readable, which is what pg_stats is about IMHO. > > > Thanks for looking into this. > > I have to admit it looks much better this way, so +1. > OK, good to hear. > >> This simply adds two functions, accepting/producing anyarray - one for >> lower bounds, one for upper bounds. I don't think it can be done with a >> plain subquery (or at least I don't know how). > > > Anyarray is an alien to SQL, so functions are well justified here. What > makes me a bit uneasy is two almost identical functions. Should we > consider other options like a function with an additional parameter or a > function returning an array of bounds arrays (which is somewhat > wasteful, but probably it doesn't matter much here)? > I thought about that, but I think the alternatives (e.g. a single function with a parameter determining which boundary to return). But I don't think it's better. Moreover, I think this is pretty similar to lower/upper, which already work on range values. So if we have separate functions for that, we should do the same thing here. I renamed the functions to ranges_lower/ranges_upper, but maybe why not to even call the functions lower/upper too? The main trouble with the function I can think of is that we only have anyarray type, not anyrangearray. So the functions will get called for arbitrary array, and the check that it's array of ranges happens inside. I'm not sure if that's a good or bad idea, or what would it take to add a new polymorphic type ... For now I at least kept "ranges_" to make it less likely. > >> Finally, it renames the empty_range_frac to start with range_, per the >> earlier discussion. I wonder if the new column names for lower/upper >> bounds (range_lower_bounds_histograms/range_upper_bounds_histograms) are >> too long ... > > > It seems so. The ending -s should be left out since it's a single > histogram now. And I think that > range_lower_histogram/range_upper_histogram are descriptive enough. > > I'm adding one more patch to shorten the column names, refresh the docs, > and make 'make check' happy (unfortunately, we have to edit > src/regress/expected/rules.out every time pg_stats definition changes). > Thanks. I noticed the docs were added to pg_user_mapping by mistake, not to pg_stats. So I fixed that, and I also added the new functions. Finally, I reordered the fields a bit - moved range_empty_frac to keep the histogram fields together. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company Attachments: [text/x-patch] 0001-Display-length-and-bounds-histograms-in-pg_-20230122.patch (11.9K, ../../[email protected]/2-0001-Display-length-and-bounds-histograms-in-pg_-20230122.patch) download | inline diff: From 8049d2b7e5d1636d5fb2b7d421d6b29a39389fb3 Mon Sep 17 00:00:00 2001 From: Tomas Vondra <[email protected]> Date: Fri, 20 Jan 2023 20:50:41 +0100 Subject: [PATCH 1/2] Display length and bounds histograms in pg_stats Values corresponding to STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM and STATISTIC_KIND_BOUNDS_HISTOGRAM were not exposed to pg_stats when these slot kinds were introduced in 918eee0c49. This commit adds the missing fields to pg_stats. TODO: catalog version bump --- doc/src/sgml/catalogs.sgml | 40 ++++++ src/backend/catalog/system_views.sql | 30 ++++- src/backend/utils/adt/rangetypes_typanalyze.c | 118 ++++++++++++++++++ src/include/catalog/pg_proc.dat | 10 ++ src/include/catalog/pg_statistic.h | 3 + src/test/regress/expected/rules.out | 34 ++++- 6 files changed, 233 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index c1e4048054..e0851c52b4 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -9634,6 +9634,46 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l User mapping specific options, as <quote>keyword=value</quote> strings </para></entry> </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>range_length_histogram</structfield> <type>anyarray</type> + </para> + <para> + A histogram of the lengths of non-empty and non-null range values of a + range type column. (Null for non-range types.) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>range_empty_frac</structfield> <type>float4</type> + </para> + <para> + Fraction of column entries whose values are empty ranges. + (Null for non-range types.) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>range_lower_histogram</structfield> <type>anyarray</type> + </para> + <para> + A histogram of lower bounds of non-empty and non-null range values. + (Null for non-range types.) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>range_upper_histogram</structfield> <type>anyarray</type> + </para> + <para> + A histogram of upper bounds of non-empty and non-null range values. + (Null for non-range types.) + </para></entry> + </row> </tbody> </tgroup> </table> diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 8608e3fa5b..b3b9e64ae2 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -243,7 +243,35 @@ CREATE VIEW pg_stats WITH (security_barrier) AS WHEN stakind3 = 5 THEN stanumbers3 WHEN stakind4 = 5 THEN stanumbers4 WHEN stakind5 = 5 THEN stanumbers5 - END AS elem_count_histogram + END AS elem_count_histogram, + CASE + WHEN stakind1 = 6 THEN stavalues1 + WHEN stakind2 = 6 THEN stavalues2 + WHEN stakind3 = 6 THEN stavalues3 + WHEN stakind4 = 6 THEN stavalues4 + WHEN stakind5 = 6 THEN stavalues5 + END AS range_length_histogram, + CASE + WHEN stakind1 = 6 THEN stanumbers1[1] + WHEN stakind2 = 6 THEN stanumbers2[1] + WHEN stakind3 = 6 THEN stanumbers3[1] + WHEN stakind4 = 6 THEN stanumbers4[1] + WHEN stakind5 = 6 THEN stanumbers5[1] + END AS range_empty_frac, + CASE + WHEN stakind1 = 7 THEN ranges_lower_bounds(stavalues1) + WHEN stakind2 = 7 THEN ranges_lower_bounds(stavalues2) + WHEN stakind3 = 7 THEN ranges_lower_bounds(stavalues3) + WHEN stakind4 = 7 THEN ranges_lower_bounds(stavalues4) + WHEN stakind5 = 7 THEN ranges_lower_bounds(stavalues5) + END AS range_lower_histogram, + CASE + WHEN stakind1 = 7 THEN ranges_upper_bounds(stavalues1) + WHEN stakind2 = 7 THEN ranges_upper_bounds(stavalues2) + WHEN stakind3 = 7 THEN ranges_upper_bounds(stavalues3) + WHEN stakind4 = 7 THEN ranges_upper_bounds(stavalues4) + WHEN stakind5 = 7 THEN ranges_upper_bounds(stavalues5) + END AS range_upper_histogram FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid) JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum) LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) diff --git a/src/backend/utils/adt/rangetypes_typanalyze.c b/src/backend/utils/adt/rangetypes_typanalyze.c index 86810a1a6e..a0097282fc 100644 --- a/src/backend/utils/adt/rangetypes_typanalyze.c +++ b/src/backend/utils/adt/rangetypes_typanalyze.c @@ -26,6 +26,8 @@ #include "catalog/pg_operator.h" #include "commands/vacuum.h" +#include "utils/array.h" +#include "utils/arrayaccess.h" #include "utils/float.h" #include "utils/fmgrprotos.h" #include "utils/lsyscache.h" @@ -427,3 +429,119 @@ compute_range_stats(VacAttrStats *stats, AnalyzeAttrFetchFunc fetchfunc, * hashtable should also go away, as it used a child memory context. */ } + +Datum +ranges_lower_bounds(PG_FUNCTION_ARGS) +{ + AnyArrayType *array = PG_GETARG_ANY_ARRAY_P(0); + int ndims = AARR_NDIM(array); + int *dims = AARR_DIMS(array); + Oid element_type = AARR_ELEMTYPE(array); + int i; + array_iter iter; + int nelems; + Datum *elems; + TypeCacheEntry *typentry; + TypeCacheEntry *typentry_element; + + /* Get information about range type; note column might be a domain */ + typentry = range_get_typcache(fcinfo, getBaseType(element_type)); + + if (typentry->typtype != TYPTYPE_RANGE) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("expected array of ranges"))); + + ndims = AARR_NDIM(array); + dims = AARR_DIMS(array); + nelems = ArrayGetNItems(ndims, dims); + + elems = (Datum *) palloc(nelems * sizeof(Datum)); + + array_iter_setup(&iter, array); + + for (i = 0; i < nelems; i++) + { + Datum itemvalue; + bool isnull; + RangeBound lower; + RangeBound upper; + bool empty; + + /* Get source element, checking for NULL */ + itemvalue = array_iter_next(&iter, &isnull, i, + typentry->typlen, typentry->typbyval, + typentry->typalign); + + Assert(!isnull); + + range_deserialize(typentry, (RangeType *) itemvalue, &lower, &upper, &empty); + elems[i] = lower.val; + } + + typentry_element = typentry->rngelemtype; + + PG_RETURN_ARRAYTYPE_P(construct_array(elems, nelems, + typentry_element->type_id, + typentry_element->typlen, + typentry_element->typbyval, + typentry_element->typalign)); +} + +Datum +ranges_upper_bounds(PG_FUNCTION_ARGS) +{ + AnyArrayType *array = PG_GETARG_ANY_ARRAY_P(0); + int ndims = AARR_NDIM(array); + int *dims = AARR_DIMS(array); + Oid element_type = AARR_ELEMTYPE(array); + int i; + array_iter iter; + int nelems; + Datum *elems; + TypeCacheEntry *typentry; + TypeCacheEntry *typentry_element; + + /* Get information about range type; note column might be a domain */ + typentry = range_get_typcache(fcinfo, getBaseType(element_type)); + + if (typentry->typtype != TYPTYPE_RANGE) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("expected array of ranges"))); + + ndims = AARR_NDIM(array); + dims = AARR_DIMS(array); + nelems = ArrayGetNItems(ndims, dims); + + elems = (Datum *) palloc(nelems * sizeof(Datum)); + + array_iter_setup(&iter, array); + + for (i = 0; i < nelems; i++) + { + Datum itemvalue; + bool isnull; + RangeBound lower; + RangeBound upper; + bool empty; + + /* Get source element, checking for NULL */ + itemvalue = array_iter_next(&iter, &isnull, i, + typentry->typlen, typentry->typbyval, + typentry->typalign); + + Assert(!isnull); + + range_deserialize(typentry, (RangeType *) itemvalue, &lower, &upper, &empty); + elems[i] = upper.val; + } + + typentry_element = typentry->rngelemtype; + + PG_RETURN_ARRAYTYPE_P(construct_array(elems, nelems, + typentry_element->type_id, + typentry_element->typlen, + typentry_element->typbyval, + typentry_element->typalign)); +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 86eb8e8c58..161557e4cb 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -11891,4 +11891,14 @@ prorettype => 'bytea', proargtypes => 'pg_brin_minmax_multi_summary', prosrc => 'brin_minmax_multi_summary_send' }, +{ oid => '9693', descr => 'lower bounds of ranges', + proname => 'ranges_lower_bounds', provolatile => 's', + prorettype => 'anyarray', proargtypes => 'anyarray', + prosrc => 'ranges_lower_bounds' }, + +{ oid => '9694', descr => 'upper bounds of ranges', + proname => 'ranges_upper_bounds', provolatile => 's', + prorettype => 'anyarray', proargtypes => 'anyarray', + prosrc => 'ranges_upper_bounds' }, + ] diff --git a/src/include/catalog/pg_statistic.h b/src/include/catalog/pg_statistic.h index 8770c5b4c6..10401dece0 100644 --- a/src/include/catalog/pg_statistic.h +++ b/src/include/catalog/pg_statistic.h @@ -152,6 +152,9 @@ DECLARE_FOREIGN_KEY((starelid, staattnum), pg_attribute, (attrelid, attnum)); * data "kind" will appear in any particular slot. Instead, search the * stakind fields to see if the desired data is available. (The standard * function get_attstatsslot() may be used for this.) + * + * Note: The pg_stats view needs to be modified whenever a new slot kind is + * added to core. */ /* diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index e7a2f5856a..26087dfdf5 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2436,7 +2436,39 @@ pg_stats| SELECT n.nspname AS schemaname, WHEN (s.stakind4 = 5) THEN s.stanumbers4 WHEN (s.stakind5 = 5) THEN s.stanumbers5 ELSE NULL::real[] - END AS elem_count_histogram + END AS elem_count_histogram, + CASE + WHEN (s.stakind1 = 6) THEN s.stavalues1 + WHEN (s.stakind2 = 6) THEN s.stavalues2 + WHEN (s.stakind3 = 6) THEN s.stavalues3 + WHEN (s.stakind4 = 6) THEN s.stavalues4 + WHEN (s.stakind5 = 6) THEN s.stavalues5 + ELSE NULL::anyarray + END AS range_length_histogram, + CASE + WHEN (s.stakind1 = 6) THEN s.stanumbers1[1] + WHEN (s.stakind2 = 6) THEN s.stanumbers2[1] + WHEN (s.stakind3 = 6) THEN s.stanumbers3[1] + WHEN (s.stakind4 = 6) THEN s.stanumbers4[1] + WHEN (s.stakind5 = 6) THEN s.stanumbers5[1] + ELSE NULL::real + END AS range_empty_frac, + CASE + WHEN (s.stakind1 = 7) THEN ranges_lower_bounds(s.stavalues1) + WHEN (s.stakind2 = 7) THEN ranges_lower_bounds(s.stavalues2) + WHEN (s.stakind3 = 7) THEN ranges_lower_bounds(s.stavalues3) + WHEN (s.stakind4 = 7) THEN ranges_lower_bounds(s.stavalues4) + WHEN (s.stakind5 = 7) THEN ranges_lower_bounds(s.stavalues5) + ELSE NULL::anyarray + END AS range_lower_histogram, + CASE + WHEN (s.stakind1 = 7) THEN ranges_upper_bounds(s.stavalues1) + WHEN (s.stakind2 = 7) THEN ranges_upper_bounds(s.stavalues2) + WHEN (s.stakind3 = 7) THEN ranges_upper_bounds(s.stavalues3) + WHEN (s.stakind4 = 7) THEN ranges_upper_bounds(s.stavalues4) + WHEN (s.stakind5 = 7) THEN ranges_upper_bounds(s.stavalues5) + ELSE NULL::anyarray + END AS range_upper_histogram FROM (((pg_statistic s JOIN pg_class c ON ((c.oid = s.starelid))) JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) -- 2.39.0 [text/x-patch] 0002-tweaks-20230122.patch (13.7K, ../../[email protected]/3-0002-tweaks-20230122.patch) download | inline diff: From 9e95cf8025f37b819a3396fa24e0ccac92f18ce0 Mon Sep 17 00:00:00 2001 From: Tomas Vondra <[email protected]> Date: Sun, 22 Jan 2023 17:28:30 +0100 Subject: [PATCH 2/2] tweaks --- doc/src/sgml/catalogs.sgml | 40 ------------------- doc/src/sgml/func.sgml | 36 +++++++++++++++++ doc/src/sgml/system-views.sgml | 40 +++++++++++++++++++ src/backend/catalog/system_views.sql | 34 ++++++++-------- src/backend/utils/adt/rangetypes_typanalyze.c | 6 +++ src/include/catalog/pg_proc.dat | 4 +- src/include/catalog/pg_statistic.h | 3 -- src/test/regress/expected/rules.out | 36 ++++++++--------- 8 files changed, 119 insertions(+), 80 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index e0851c52b4..c1e4048054 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -9634,46 +9634,6 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l User mapping specific options, as <quote>keyword=value</quote> strings </para></entry> </row> - - <row> - <entry role="catalog_table_entry"><para role="column_definition"> - <structfield>range_length_histogram</structfield> <type>anyarray</type> - </para> - <para> - A histogram of the lengths of non-empty and non-null range values of a - range type column. (Null for non-range types.) - </para></entry> - </row> - - <row> - <entry role="catalog_table_entry"><para role="column_definition"> - <structfield>range_empty_frac</structfield> <type>float4</type> - </para> - <para> - Fraction of column entries whose values are empty ranges. - (Null for non-range types.) - </para></entry> - </row> - - <row> - <entry role="catalog_table_entry"><para role="column_definition"> - <structfield>range_lower_histogram</structfield> <type>anyarray</type> - </para> - <para> - A histogram of lower bounds of non-empty and non-null range values. - (Null for non-range types.) - </para></entry> - </row> - - <row> - <entry role="catalog_table_entry"><para role="column_definition"> - <structfield>range_upper_histogram</structfield> <type>anyarray</type> - </para> - <para> - A histogram of upper bounds of non-empty and non-null range values. - (Null for non-range types.) - </para></entry> - </row> </tbody> </tgroup> </table> diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index b8dac9ef46..f8eb488b2c 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -19357,6 +19357,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>ranges_lower</primary> + </indexterm> + <function>ranges_lower</function> ( <type>anyarray</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Extracts lower bounds of ranges in the array (<literal>NULL</literal> if + the range is empty or the lower bound is infinite). + </para> + <para> + <literal>lower(numrange(1.1,2.2))</literal> + <returnvalue>1.1</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> @@ -19375,6 +19393,24 @@ SELECT NULLIF(value, '(none)') ... </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>ranges_upper</primary> + </indexterm> + <function>ranges_upper</function> ( <type>anyarray</type> ) + <returnvalue>anyarray</returnvalue> + </para> + <para> + Extracts upper bounds of ranges (<literal>NULL</literal> if the + range is empty or the upper bound is infinite). + </para> + <para> + <literal>upper(numrange(1.1,2.2))</literal> + <returnvalue>2.2</returnvalue> + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml index 7c8fc3f654..a48d775467 100644 --- a/doc/src/sgml/system-views.sgml +++ b/doc/src/sgml/system-views.sgml @@ -3783,6 +3783,46 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx non-null elements. (Null for scalar types.) </para></entry> </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>range_length_histogram</structfield> <type>anyarray</type> + </para> + <para> + A histogram of the lengths of non-empty and non-null range values of a + range type column. (Null for non-range types.) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>range_empty_frac</structfield> <type>float4</type> + </para> + <para> + Fraction of column entries whose values are empty ranges. + (Null for non-range types.) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>range_lower_histogram</structfield> <type>anyarray</type> + </para> + <para> + A histogram of lower bounds of non-empty and non-null range values. + (Null for non-range types.) + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>range_upper_histogram</structfield> <type>anyarray</type> + </para> + <para> + A histogram of upper bounds of non-empty and non-null range values. + (Null for non-range types.) + </para></entry> + </row> </tbody> </tgroup> </table> diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index b3b9e64ae2..00e91e5d93 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -244,13 +244,6 @@ CREATE VIEW pg_stats WITH (security_barrier) AS WHEN stakind4 = 5 THEN stanumbers4 WHEN stakind5 = 5 THEN stanumbers5 END AS elem_count_histogram, - CASE - WHEN stakind1 = 6 THEN stavalues1 - WHEN stakind2 = 6 THEN stavalues2 - WHEN stakind3 = 6 THEN stavalues3 - WHEN stakind4 = 6 THEN stavalues4 - WHEN stakind5 = 6 THEN stavalues5 - END AS range_length_histogram, CASE WHEN stakind1 = 6 THEN stanumbers1[1] WHEN stakind2 = 6 THEN stanumbers2[1] @@ -259,18 +252,25 @@ CREATE VIEW pg_stats WITH (security_barrier) AS WHEN stakind5 = 6 THEN stanumbers5[1] END AS range_empty_frac, CASE - WHEN stakind1 = 7 THEN ranges_lower_bounds(stavalues1) - WHEN stakind2 = 7 THEN ranges_lower_bounds(stavalues2) - WHEN stakind3 = 7 THEN ranges_lower_bounds(stavalues3) - WHEN stakind4 = 7 THEN ranges_lower_bounds(stavalues4) - WHEN stakind5 = 7 THEN ranges_lower_bounds(stavalues5) + WHEN stakind1 = 6 THEN stavalues1 + WHEN stakind2 = 6 THEN stavalues2 + WHEN stakind3 = 6 THEN stavalues3 + WHEN stakind4 = 6 THEN stavalues4 + WHEN stakind5 = 6 THEN stavalues5 + END AS range_length_histogram, + CASE + WHEN stakind1 = 7 THEN ranges_lower(stavalues1) + WHEN stakind2 = 7 THEN ranges_lower(stavalues2) + WHEN stakind3 = 7 THEN ranges_lower(stavalues3) + WHEN stakind4 = 7 THEN ranges_lower(stavalues4) + WHEN stakind5 = 7 THEN ranges_lower(stavalues5) END AS range_lower_histogram, CASE - WHEN stakind1 = 7 THEN ranges_upper_bounds(stavalues1) - WHEN stakind2 = 7 THEN ranges_upper_bounds(stavalues2) - WHEN stakind3 = 7 THEN ranges_upper_bounds(stavalues3) - WHEN stakind4 = 7 THEN ranges_upper_bounds(stavalues4) - WHEN stakind5 = 7 THEN ranges_upper_bounds(stavalues5) + WHEN stakind1 = 7 THEN ranges_upper(stavalues1) + WHEN stakind2 = 7 THEN ranges_upper(stavalues2) + WHEN stakind3 = 7 THEN ranges_upper(stavalues3) + WHEN stakind4 = 7 THEN ranges_upper(stavalues4) + WHEN stakind5 = 7 THEN ranges_upper(stavalues5) END AS range_upper_histogram FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid) JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum) diff --git a/src/backend/utils/adt/rangetypes_typanalyze.c b/src/backend/utils/adt/rangetypes_typanalyze.c index a0097282fc..982f23ae22 100644 --- a/src/backend/utils/adt/rangetypes_typanalyze.c +++ b/src/backend/utils/adt/rangetypes_typanalyze.c @@ -430,6 +430,9 @@ compute_range_stats(VacAttrStats *stats, AnalyzeAttrFetchFunc fetchfunc, */ } +/* + * ranges_lower_bounds() -- return array of lower bounds for ranges + */ Datum ranges_lower_bounds(PG_FUNCTION_ARGS) { @@ -488,6 +491,9 @@ ranges_lower_bounds(PG_FUNCTION_ARGS) typentry_element->typalign)); } +/* + * ranges_upper_bounds() -- return array of upper bounds for ranges + */ Datum ranges_upper_bounds(PG_FUNCTION_ARGS) { diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 161557e4cb..c53e9fc669 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -11892,12 +11892,12 @@ prosrc => 'brin_minmax_multi_summary_send' }, { oid => '9693', descr => 'lower bounds of ranges', - proname => 'ranges_lower_bounds', provolatile => 's', + proname => 'ranges_lower', provolatile => 's', prorettype => 'anyarray', proargtypes => 'anyarray', prosrc => 'ranges_lower_bounds' }, { oid => '9694', descr => 'upper bounds of ranges', - proname => 'ranges_upper_bounds', provolatile => 's', + proname => 'ranges_upper', provolatile => 's', prorettype => 'anyarray', proargtypes => 'anyarray', prosrc => 'ranges_upper_bounds' }, diff --git a/src/include/catalog/pg_statistic.h b/src/include/catalog/pg_statistic.h index 10401dece0..8770c5b4c6 100644 --- a/src/include/catalog/pg_statistic.h +++ b/src/include/catalog/pg_statistic.h @@ -152,9 +152,6 @@ DECLARE_FOREIGN_KEY((starelid, staattnum), pg_attribute, (attrelid, attnum)); * data "kind" will appear in any particular slot. Instead, search the * stakind fields to see if the desired data is available. (The standard * function get_attstatsslot() may be used for this.) - * - * Note: The pg_stats view needs to be modified whenever a new slot kind is - * added to core. */ /* diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 26087dfdf5..ea4d721e98 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2437,14 +2437,6 @@ pg_stats| SELECT n.nspname AS schemaname, WHEN (s.stakind5 = 5) THEN s.stanumbers5 ELSE NULL::real[] END AS elem_count_histogram, - CASE - WHEN (s.stakind1 = 6) THEN s.stavalues1 - WHEN (s.stakind2 = 6) THEN s.stavalues2 - WHEN (s.stakind3 = 6) THEN s.stavalues3 - WHEN (s.stakind4 = 6) THEN s.stavalues4 - WHEN (s.stakind5 = 6) THEN s.stavalues5 - ELSE NULL::anyarray - END AS range_length_histogram, CASE WHEN (s.stakind1 = 6) THEN s.stanumbers1[1] WHEN (s.stakind2 = 6) THEN s.stanumbers2[1] @@ -2454,19 +2446,27 @@ pg_stats| SELECT n.nspname AS schemaname, ELSE NULL::real END AS range_empty_frac, CASE - WHEN (s.stakind1 = 7) THEN ranges_lower_bounds(s.stavalues1) - WHEN (s.stakind2 = 7) THEN ranges_lower_bounds(s.stavalues2) - WHEN (s.stakind3 = 7) THEN ranges_lower_bounds(s.stavalues3) - WHEN (s.stakind4 = 7) THEN ranges_lower_bounds(s.stavalues4) - WHEN (s.stakind5 = 7) THEN ranges_lower_bounds(s.stavalues5) + WHEN (s.stakind1 = 6) THEN s.stavalues1 + WHEN (s.stakind2 = 6) THEN s.stavalues2 + WHEN (s.stakind3 = 6) THEN s.stavalues3 + WHEN (s.stakind4 = 6) THEN s.stavalues4 + WHEN (s.stakind5 = 6) THEN s.stavalues5 + ELSE NULL::anyarray + END AS range_length_histogram, + CASE + WHEN (s.stakind1 = 7) THEN ranges_lower(s.stavalues1) + WHEN (s.stakind2 = 7) THEN ranges_lower(s.stavalues2) + WHEN (s.stakind3 = 7) THEN ranges_lower(s.stavalues3) + WHEN (s.stakind4 = 7) THEN ranges_lower(s.stavalues4) + WHEN (s.stakind5 = 7) THEN ranges_lower(s.stavalues5) ELSE NULL::anyarray END AS range_lower_histogram, CASE - WHEN (s.stakind1 = 7) THEN ranges_upper_bounds(s.stavalues1) - WHEN (s.stakind2 = 7) THEN ranges_upper_bounds(s.stavalues2) - WHEN (s.stakind3 = 7) THEN ranges_upper_bounds(s.stavalues3) - WHEN (s.stakind4 = 7) THEN ranges_upper_bounds(s.stavalues4) - WHEN (s.stakind5 = 7) THEN ranges_upper_bounds(s.stavalues5) + WHEN (s.stakind1 = 7) THEN ranges_upper(s.stavalues1) + WHEN (s.stakind2 = 7) THEN ranges_upper(s.stavalues2) + WHEN (s.stakind3 = 7) THEN ranges_upper(s.stavalues3) + WHEN (s.stakind4 = 7) THEN ranges_upper(s.stavalues4) + WHEN (s.stakind5 = 7) THEN ranges_upper(s.stavalues5) ELSE NULL::anyarray END AS range_upper_histogram FROM (((pg_statistic s -- 2.39.0 ^ permalink raw reply [nested|flat] 23+ messages in thread
end of thread, other threads:[~2023-01-22 18:19 UTC | newest] Thread overview: 23+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-01-22 20:59 [PATCH v3 3/3] Fix sizing of tape read buffers. Heikki Linnakangas <[email protected]> 2021-01-22 20:59 [PATCH v3 3/3] Fix sizing of tape read buffers. Heikki Linnakangas <[email protected]> 2021-01-22 20:59 [PATCH v3 3/3] Fix sizing of tape read buffers. Heikki Linnakangas <[email protected]> 2021-01-22 20:59 [PATCH v3 3/3] Fix sizing of tape read buffers. Heikki Linnakangas <[email protected]> 2021-01-22 20:59 [PATCH v3 3/3] Fix sizing of tape read buffers. Heikki Linnakangas <[email protected]> 2021-01-22 20:59 [PATCH v3 3/3] Fix sizing of tape read buffers. Heikki Linnakangas <[email protected]> 2021-01-22 20:59 [PATCH v3 3/3] Fix sizing of tape read buffers. Heikki Linnakangas <[email protected]> 2021-01-22 20:59 [PATCH v3 3/3] Fix sizing of tape read buffers. Heikki Linnakangas <[email protected]> 2021-01-22 20:59 [PATCH v3 3/3] Fix sizing of tape read buffers. Heikki Linnakangas <[email protected]> 2021-01-22 20:59 [PATCH v3 3/3] Fix sizing of tape read buffers. Heikki Linnakangas <[email protected]> 2021-01-22 20:59 [PATCH v3 3/3] Fix sizing of tape read buffers. Heikki Linnakangas <[email protected]> 2021-01-22 20:59 [PATCH v3 3/3] Fix sizing of tape read buffers. Heikki Linnakangas <[email protected]> 2021-01-22 20:59 [PATCH v3 3/3] Fix sizing of tape read buffers. Heikki Linnakangas <[email protected]> 2021-01-22 20:59 [PATCH v3 3/3] Fix sizing of tape read buffers. Heikki Linnakangas <[email protected]> 2021-01-22 20:59 [PATCH v3 3/3] Fix sizing of tape read buffers. Heikki Linnakangas <[email protected]> 2021-01-22 20:59 [PATCH v3 3/3] Fix sizing of tape read buffers. Heikki Linnakangas <[email protected]> 2021-01-22 20:59 [PATCH v3 3/3] Fix sizing of tape read buffers. Heikki Linnakangas <[email protected]> 2021-01-22 20:59 [PATCH v3 3/3] Fix sizing of tape read buffers. Heikki Linnakangas <[email protected]> 2021-01-22 20:59 [PATCH v3 3/3] Fix sizing of tape read buffers. Heikki Linnakangas <[email protected]> 2021-01-22 20:59 [PATCH v3 3/3] Fix sizing of tape read buffers. Heikki Linnakangas <[email protected]> 2021-01-22 20:59 [PATCH v3 3/3] Fix sizing of tape read buffers. Heikki Linnakangas <[email protected]> 2023-01-21 18:53 Re: pg_stats and range statistics Egor Rogov <[email protected]> 2023-01-22 18:19 ` Re: pg_stats and range statistics Tomas Vondra <[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