src/test/regress/expected/planner_est.out - master - githubmirror/postgres

src/test/regress/expected/planner_est.out
224 строки10 KB
Tom Lane
Avoid collation lookup failure when considering a "char" column.
28 июн 2026, 19:31
28 июн 2026, 19:31b574fec
--
-- Tests for testing query planner selectivity and width estimates
--
-- Most selectivity and width estimations rely too heavily on statistics
-- gathered by ANALYZE, or could vary depending on hardware.  However, there
-- are a few cases where we can have more certainty about the expected number
-- of rows, or width of rows.  This is a good home for such tests.
--
-- Function to assist with verifying EXPLAIN which includes costs.  A series
-- of bool flags allows control over which portions are masked out
CREATE FUNCTION explain_mask_costs(query text, do_analyze bool,
    hide_costs bool, hide_row_est bool, hide_width bool) RETURNS setof text
LANGUAGE plpgsql AS
$$
DECLARE
    ln text;
    analyze_str text;
BEGIN
    IF do_analyze = true THEN
        analyze_str := 'on';
    ELSE
        analyze_str := 'off';
    END IF;

    -- avoid jit related output by disabling it
    SET LOCAL jit = 0;

    FOR ln IN
        EXECUTE format('explain (analyze %s, costs on, summary off, timing off, buffers off) %s',
            analyze_str, query)
    LOOP
        IF hide_costs = true THEN
            ln := regexp_replace(ln, 'cost=\d+\.\d\d\.\.\d+\.\d\d', 'cost=N..N');
        END IF;

        IF hide_row_est = true THEN
            -- don't use 'g' so that we leave the actual rows intact
            ln := regexp_replace(ln, 'rows=\d+', 'rows=N');
        END IF;

        IF hide_width = true THEN
            ln := regexp_replace(ln, 'width=\d+', 'width=N');
        END IF;

        RETURN NEXT ln;
    END LOOP;
END;
$$;
--
-- Test the SupportRequestRows support function for generate_series_timestamp()
--
-- Ensure the row estimate matches the actual rows
SELECT explain_mask_costs($$
SELECT * FROM generate_series(TIMESTAMPTZ '2024-02-01', TIMESTAMPTZ '2024-03-01', INTERVAL '1 day') g(s);$$,
true, true, false, true);
                                     explain_mask_costs                                      
---------------------------------------------------------------------------------------------
 Function Scan on generate_series g  (cost=N..N rows=30 width=N) (actual rows=30.00 loops=1)
(1 row)

-- As above but with generate_series_timestamp
SELECT explain_mask_costs($$
SELECT * FROM generate_series(TIMESTAMP '2024-02-01', TIMESTAMP '2024-03-01', INTERVAL '1 day') g(s);$$,
true, true, false, true);
                                     explain_mask_costs                                      
---------------------------------------------------------------------------------------------
 Function Scan on generate_series g  (cost=N..N rows=30 width=N) (actual rows=30.00 loops=1)
(1 row)

-- As above but with generate_series_timestamptz_at_zone()
SELECT explain_mask_costs($$
SELECT * FROM generate_series(TIMESTAMPTZ '2024-02-01', TIMESTAMPTZ '2024-03-01', INTERVAL '1 day', 'UTC') g(s);$$,
true, true, false, true);
                                     explain_mask_costs                                      
---------------------------------------------------------------------------------------------
 Function Scan on generate_series g  (cost=N..N rows=30 width=N) (actual rows=30.00 loops=1)
(1 row)

-- Ensure the estimated and actual row counts match when the range isn't
-- evenly divisible by the step
SELECT explain_mask_costs($$
SELECT * FROM generate_series(TIMESTAMPTZ '2024-02-01', TIMESTAMPTZ '2024-03-01', INTERVAL '7 day') g(s);$$,
true, true, false, true);
                                    explain_mask_costs                                     
-------------------------------------------------------------------------------------------
 Function Scan on generate_series g  (cost=N..N rows=5 width=N) (actual rows=5.00 loops=1)
(1 row)

-- Ensure the estimates match when step is decreasing
SELECT explain_mask_costs($$
SELECT * FROM generate_series(TIMESTAMPTZ '2024-03-01', TIMESTAMPTZ '2024-02-01', INTERVAL '-1 day') g(s);$$,
true, true, false, true);
                                     explain_mask_costs                                      
---------------------------------------------------------------------------------------------
 Function Scan on generate_series g  (cost=N..N rows=30 width=N) (actual rows=30.00 loops=1)
(1 row)

-- Ensure an empty range estimates 1 row
SELECT explain_mask_costs($$
SELECT * FROM generate_series(TIMESTAMPTZ '2024-03-01', TIMESTAMPTZ '2024-02-01', INTERVAL '1 day') g(s);$$,
true, true, false, true);
                                    explain_mask_costs                                     
-------------------------------------------------------------------------------------------
 Function Scan on generate_series g  (cost=N..N rows=1 width=N) (actual rows=0.00 loops=1)
(1 row)

-- Ensure we get the default row estimate for infinity values
SELECT explain_mask_costs($$
SELECT * FROM generate_series(TIMESTAMPTZ '-infinity', TIMESTAMPTZ 'infinity', INTERVAL '1 day') g(s);$$,
false, true, false, true);
                        explain_mask_costs                         
-------------------------------------------------------------------
 Function Scan on generate_series g  (cost=N..N rows=1000 width=N)
(1 row)

-- Ensure the row estimate behaves correctly when step size is zero.
-- We expect generate_series_timestamp() to throw the error rather than in
-- the support function.
SELECT * FROM generate_series(TIMESTAMPTZ '2024-02-01', TIMESTAMPTZ '2024-03-01', INTERVAL '0 day') g(s);
ERROR:  step size cannot equal zero
--
-- Test the SupportRequestRows support function for generate_series_numeric()
--
-- Ensure the row estimate matches the actual rows
SELECT explain_mask_costs($$
SELECT * FROM generate_series(1.0, 25.0) g(s);$$,
true, true, false, true);
                                     explain_mask_costs                                      
---------------------------------------------------------------------------------------------
 Function Scan on generate_series g  (cost=N..N rows=25 width=N) (actual rows=25.00 loops=1)
(1 row)

-- As above but with non-default step
SELECT explain_mask_costs($$
SELECT * FROM generate_series(1.0, 25.0, 2.0) g(s);$$,
true, true, false, true);
                                     explain_mask_costs                                      
---------------------------------------------------------------------------------------------
 Function Scan on generate_series g  (cost=N..N rows=13 width=N) (actual rows=13.00 loops=1)
(1 row)

-- Ensure the estimates match when step is decreasing
SELECT explain_mask_costs($$
SELECT * FROM generate_series(25.0, 1.0, -1.0) g(s);$$,
true, true, false, true);
                                     explain_mask_costs                                      
---------------------------------------------------------------------------------------------
 Function Scan on generate_series g  (cost=N..N rows=25 width=N) (actual rows=25.00 loops=1)
(1 row)

-- Ensure an empty range estimates 1 row
SELECT explain_mask_costs($$
SELECT * FROM generate_series(25.0, 1.0, 1.0) g(s);$$,
true, true, false, true);
                                    explain_mask_costs                                     
-------------------------------------------------------------------------------------------
 Function Scan on generate_series g  (cost=N..N rows=1 width=N) (actual rows=0.00 loops=1)
(1 row)

-- Ensure we get the default row estimate for error cases (infinity/NaN values
-- and zero step size)
SELECT explain_mask_costs($$
SELECT * FROM generate_series('-infinity'::NUMERIC, 'infinity'::NUMERIC, 1.0) g(s);$$,
false, true, false, true);
                        explain_mask_costs                         
-------------------------------------------------------------------
 Function Scan on generate_series g  (cost=N..N rows=1000 width=N)
(1 row)

SELECT explain_mask_costs($$
SELECT * FROM generate_series(1.0, 25.0, 'NaN'::NUMERIC) g(s);$$,
false, true, false, true);
                        explain_mask_costs                         
-------------------------------------------------------------------
 Function Scan on generate_series g  (cost=N..N rows=1000 width=N)
(1 row)

SELECT explain_mask_costs($$
SELECT * FROM generate_series(25.0, 2.0, 0.0) g(s);$$,
false, true, false, true);
                        explain_mask_costs                         
-------------------------------------------------------------------
 Function Scan on generate_series g  (cost=N..N rows=1000 width=N)
(1 row)

--
-- Test ScalarArrayOpExpr row estimates for <> ALL for arrays with NULLs.  We
-- expect the planner to estimate 1 row will match in both of the following
-- tests.
--
-- Try a const array containing a NULL
SELECT explain_mask_costs($$
SELECT * FROM tenk1 WHERE unique1 <> ALL (ARRAY[1, 2, 99, NULL]);$$,
false, true, false, true);
                   explain_mask_costs                    
---------------------------------------------------------
 Seq Scan on tenk1  (cost=N..N rows=1 width=N)
   Filter: (unique1 <> ALL ('{1,2,99,NULL}'::integer[]))
(2 rows)

-- Try a non-const array containing a NULL
SELECT explain_mask_costs($$
SELECT * FROM tenk1 WHERE unique1 <> ALL (ARRAY[1, 2, 98, (SELECT 99), NULL]);$$,
false, true, false, true);
                                 explain_mask_costs                                  
-------------------------------------------------------------------------------------
 Seq Scan on tenk1  (cost=N..N rows=1 width=N)
   Filter: (unique1 <> ALL (ARRAY[1, 2, 98, (InitPlan expr_1).col1, NULL::integer]))
   InitPlan expr_1
     ->  Result  (cost=N..N rows=1 width=N)
(4 rows)

-- Verify that scalarineqsel() works on "char" columns
CREATE TEMP TABLE char_table_1 AS
  SELECT i::"char" AS c FROM generate_series(64,96) i;
ANALYZE char_table_1;
EXPLAIN (COSTS OFF) SELECT * FROM char_table_1 WHERE c < 'Q';
         QUERY PLAN          
-----------------------------
 Seq Scan on char_table_1
   Filter: (c < 'Q'::"char")
(2 rows)

DROP FUNCTION explain_mask_costs(text, bool, bool, bool, bool);