By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select version();
explain (analyze, verbose)
select CASE
WHEN (a::text) ~ E'^\\d+$'
THEN (a::text)::integer
ELSE -1
END as to_number
from generate_series(0,1e7) as a;
CREATE OR replace FUNCTION to_number(argument text,default_result integer default -1)
RETURNS integer LANGUAGE SQL
AS '
SELECT CASE
WHEN argument~E''^\\d+$''
THEN argument::integer
ELSE default_result
END';
explain (analyze, verbose)
select to_number(a::text,default_result := -1) from generate_series(0,1e7) as a;
| version |
|---|
| PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
SELECT 1
| QUERY PLAN |
|---|
| Function Scan on pg_catalog.generate_series a (cost=0.00..27.50 rows=1000 width=4) (actual time=2320.720..10090.206 rows=10000001 loops=1) |
| Output: CASE WHEN ((a)::text ~ '^\d+$'::text) THEN ((a)::text)::integer ELSE '-1'::integer END |
| Function Call: generate_series('0'::numeric, '10000000'::numeric) |
| Planning Time: 0.111 ms |
| Execution Time: 10685.770 ms |
EXPLAIN
CREATE FUNCTION
| QUERY PLAN |
|---|
| Function Scan on pg_catalog.generate_series a (cost=0.00..27.50 rows=1000 width=4) (actual time=1852.918..9058.069 rows=10000001 loops=1) |
| Output: CASE WHEN ((a)::text ~ '^\d+$'::text) THEN ((a)::text)::integer ELSE '-1'::integer END |
| Function Call: generate_series('0'::numeric, '10000000'::numeric) |
| Planning Time: 0.114 ms |
| Execution Time: 9562.008 ms |
EXPLAIN