PostgreSQL do different equality predicates make a difference?

Frits Hoogland - Nov 19 '22 - - Dev Community

Recently I wondered whether the usage of different equality predicates ('=' and 'in') in a query would differently costed, and thus could lead to different plans.

Example table:

create table eq ( id text primary key, f1 text );
create index eq_f1_i on eq(f1);
Enter fullscreen mode Exit fullscreen mode

Fill table with arbitrary data:

insert into eq 
select id::text, id::text from generate_series(1,10000) id;
Enter fullscreen mode Exit fullscreen mode

Now let's see what '=' predicate does:

yugabyte=# explain select * from eq where id = '42';
                            QUERY PLAN
-------------------------------------------------------------------
 Index Scan using eq_pkey on eq  (cost=0.00..4.11 rows=1 width=64)
   Index Cond: (id = '42'::text)
Enter fullscreen mode Exit fullscreen mode

And let's see what the 'in' predicate does:

yugabyte=# explain select * from eq where id in ('42');
                            QUERY PLAN
-------------------------------------------------------------------
 Index Scan using eq_pkey on eq  (cost=0.00..4.11 rows=1 width=64)
   Index Cond: (id = '42'::text)
Enter fullscreen mode Exit fullscreen mode

The cost is identical, and actually the condition for the usage of the index is transformed to be identical in both cases (for both '=' and 'in', the condition becomes '='). There is no difference here.

I cannot see any reason how that could be, but maybe there's a difference when it's not on a primary key, and thus on YugabyteDB needs a 'secondary index'?
'=' predicate:

explain select * from eq where f1 = '42';
                             QUERY PLAN
--------------------------------------------------------------------
 Index Scan using eq_f1_i on eq  (cost=0.00..5.22 rows=10 width=64)
   Index Cond: (f1 = '42'::text)
Enter fullscreen mode Exit fullscreen mode

'in' predicate:

explain select * from eq where f1 in ('42');
                             QUERY PLAN
--------------------------------------------------------------------
 Index Scan using eq_f1_i on eq  (cost=0.00..5.22 rows=10 width=64)
   Index Cond: (f1 = '42'::text)
Enter fullscreen mode Exit fullscreen mode

There is no difference between using the '=' or the 'in' predicate for equality. Because SQL is a declarative language, the predicate is "translated" to what it's supposed to be doing, and costed. And besides being executed the same, it's also costed the same, so switching between '=' and 'in' will not flip plans.

The tests were conducted on a YugabyteDB version 2.13.2.0b135 database. Testing on PostgreSQL version 13 also shows completely identical cost for both equality predicates.

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Terabox Video Player