Numeric types and implicit conversion in PostgreSQL

PostgreSQL offers enriching variant for storing numeric kind of data. it varies from being integer types , floating type or as Numeric.

it’s always wise to understand on how it internally execute for equality operator when we try to compare different variants of numeric types among itself.

let’s create sample set as below.

create table testint as 
select 'a' col1 , generate_series(1,1000000)::integer col2;

create index idx_testint on testint(col2);

create table testnumeric as 
select  'a' col1 , generate_series(1,1000000)::numeric col2;

create index idx_testnumeric on testnumeric(col2);

For integer types, we will try to compare with all variant of numeric types and evaluate internals execution plan.
As part of evaluating effect we are imposing some setting to influence execution plan.

SET max_parallel_workers_per_gather = 0;
SET enable_seqscan = off;

explain analyze select * from testint where col2 = 999::smallint;
explain analyze select * from testint where col2 = 999::bigint;
explain analyze select * from testint where col2 = 999::double precision;
explain analyze select * from testint where col2 = 999::numeric;

sharing output that comprise of execution statistics

postgres=> explain analyze select * from testint where col2 = 999::smallint;
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_testint on testint  (cost=0.42..8.44 rows=1 width=6) (actual time=0.038..0.039 rows=1 loops=1)
   Index Cond: (col2 = '999'::smallint)
 Planning Time: 0.247 ms
 Execution Time: 0.058 ms
(4 rows)

postgres=> explain analyze select * from testint where col2 = 999::bigint;
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_testint on testint  (cost=0.42..8.44 rows=1 width=6) (actual time=0.035..0.036 rows=1 loops=1)
   Index Cond: (col2 = '999'::bigint)
 Planning Time: 0.085 ms
 Execution Time: 0.053 ms
(4 rows)

postgres=> explain analyze select * from testint where col2 = 999::double precision;
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Seq Scan on testint  (cost=10000000000.00..10000019425.00 rows=5000 width=6) (actual time=0.111..80.941 rows=1 loops=1)
   Filter: ((col2)::double precision = '999'::double precision)
   Rows Removed by Filter: 999999
 Planning Time: 0.106 ms
 Execution Time: 80.956 ms
(5 rows)

postgres=> explain analyze select * from testint where col2 = 999::numeric;

QUERY PLAN                                                        
-----------------------------------------------------------------------------------------
 Seq Scan on testint  (cost=10000000000.00..10000019425.00 rows=5000 width=6) (actual time=0.152..191.285 rows=1 loops=1)
   Filter: ((col2)::numeric = '999'::numeric)
   Rows Removed by Filter: 999999
 Planning Time: 0.060 ms
 Execution Time: 191.299 ms
(5 rows)

some of the key observation, based on above execution plan.

For all floating point filter’s, implicit cast is required for column to serve the output.With double precision and numeric casting was imposed on column and it didn’t leverages index as in case for smallint or bigint.

Index Cond: (col2 = ‘999’::smallint)
Index Cond: (col2 = ‘999’::bigint)
Filter: ((col2)::double precision = ‘999’::double precision)
Filter: ((col2)::numeric = ‘999’::numeric)

it clearly indicate as part of comparing floating variables with integer types will cause implicit cast and impose performance overhead.

let’s take it further and try to compare it without imposing any casting on input filters.

postgres=> explain analyze select * from testint where col2 = 999;

QUERY PLAN                                                      
-----------------------------------------------------------------------------------------
 Index Scan using idx_testint on testint  (cost=0.42..8.44 rows=1 width=6) (actual time=0.023..0.024 rows=1 loops=1)
   Index Cond: (col2 = 999)
 Planning Time: 0.046 ms
 Execution Time: 0.039 ms
(4 rows)

postgres=> explain analyze select * from testint where col2 = 999.99;

QUERY PLAN                                                         
-----------------------------------------------------------------------------------------
 Seq Scan on testint  (cost=10000000000.00..10000019425.00 rows=5000 width=6) (actual time=256.549..256.550 rows=0 loops=1)
   Filter: ((col2)::numeric = 999.99)
   Rows Removed by Filter: 1000000
 Planning Time: 0.041 ms
 Execution Time: 256.570 ms
(5 rows)

By default PostreSQL engine will type cast a constant filters to a numeric types or integer variants.

postgres=> select pg_typeof(99.99) , pg_typeof(99),pg_typeof(99999999999999999);
 
pg_typeof | pg_typeof | pg_typeof 
-----------+-----------+-----------
 numeric   | integer   | bigint

so for all numeric constant that is part of floating type family will still cause implicit conversion.

As part of development with PostgreSQL, we should take care of comparing numeric types within same variant other wise it will cause performance degradation.