An Interest In:
Web News this Week
- April 23, 2024
- April 22, 2024
- April 21, 2024
- April 20, 2024
- April 19, 2024
- April 18, 2024
- April 17, 2024
Some of Our Sources
- Engadget
- Team Treehouse
- Vandelay Design
- Fuel Your Creativity
- Inspiredology
- Noupe
- Line 25
- Wal You
- Spyre Studios
- TechPowerUp
Help Webnuz
Referal links:
PostgreSQL do different equality predicates make a difference?
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.
Original Link: https://dev.to/yugabyte/postgresql-do-different-equality-predicates-make-a-difference-1c7
Dev To
An online community for sharing and discovering great ideas, having debates, and making friendsMore About this Source Visit Dev To