This content originally appeared on DEV Community 👩‍💻👨‍💻 and was authored by Frits Hoogland
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);
Fill table with arbitrary data:
insert into eq
select id::text, id::text from generate_series(1,10000) id;
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)
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)
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)
'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)
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.
This content originally appeared on DEV Community 👩‍💻👨‍💻 and was authored by Frits Hoogland
Frits Hoogland | Sciencx (2022-11-19T12:47:26+00:00) PostgreSQL do different equality predicates make a difference?. Retrieved from https://www.scien.cx/2022/11/19/postgresql-do-different-equality-predicates-make-a-difference/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.