1

cd_col of table1 has regular single column btree index, but when I am querying cd_col using split_part function, I assumed it shouldn't use the btree index, but it does use. Shouldn't it need a function based index for it work? This is version 15.3.

postgres=> explain select * from schema1.table1 where cd_col = 'abc';
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Index Scan using ix_table1 on table1  (cost=0.29..8.30 rows=1 width=2414)
   Index Cond: ((cd_col)::text = 'abc'::text)
(2 rows)

postgres=> explain select * from schema1.table1 where cd_col = split_part('abc|xyz','|',1);
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Index Scan using ix_table1 on table1  (cost=0.29..8.30 rows=1 width=2414)
   Index Cond: ((cd_col)::text = 'abc'::text)
(2 rows)

postgres=> \set id '\'abc|xyz\''
postgres=> explain select * from schema1.table1 where cd_col = split_part(:id,'|',1);
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Index Scan using ix_table1 on table1  (cost=0.29..8.30 rows=1 width=2414)
   Index Cond: ((cd_col)::text = 'abc'::text)
(2 rows)

postgres=> \echo :id
'abc|xyz'
1
  • If split_part were applied to the column, that would be a problem. But since all of its arguments are literals and since it is immutable, it can be evaluated up front.
    – jjanes
    Commented Jun 20 at 22:12

1 Answer 1

1

That's constant folding.

Your function call was pre-evaluated and as you can see in the resulting query plan, split_part() is no longer present and will not be called during execution at all. Planner/Optimizer correctly noticed that it's enough to run it once, prior to that, and then re-use the result as a constant.

The only function left is texteq() behind = operator, which B-Trees have no problem with.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.