Difference between IS FALSE and = 'f' in Postgres and index use

Question

In Postgres 9.6, on a users table with around 12 million rows, where the active boolean column has a btree index on it.

EXPLAIN ANALYZE SELECT * FROM users WHERE active = 'f' LIMIT 1;

Limit  (cost=0.00..0.14 rows=1 width=982) (actual time=0.039..0.040 rows=1 loops=1)
  ->  Seq Scan on users  (cost=0.00..3190979.68 rows=23264168 width=982) (actual time=0.036..0.036 rows=1 loops=1)
        Filter: (NOT active)
        Rows Removed by Filter: 115
Planning time: 0.161 ms
Execution time: 0.067 ms

But, using IS FALSE seems to use the index.

EXPLAIN ANALYZE SELECT * FROM users WHERE active IS FALSE LIMIT 1;

Limit  (cost=0.44..0.59 rows=1 width=982) (actual time=0.054..0.056 rows=1 loops=1)
  ->  Index Scan using index_users_on_active on users  (cost=0.44..2059.14 rows=13183 width=982) (actual time=0.051..0.051 rows=1 loops=1)
        Index Cond: (active = false)
        Filter: (active IS FALSE)
Planning time: 0.170 ms
Execution time: 0.094 ms

The vast majority of the records, the active value is true, and I understand an index isn't always faster.

It seems that Rails prefers the active = 'f' syntax, as that's what it outputs when you build a query.

Why are these different? How are they different? Should the other ever be used?

User.where(active: false).limit(1).to_sql returns SELECT "users".* FROM "users" WHERE "users"."active" = 'f' LIMIT 1 — tibbon
– tibbon, Commented Sep 25, 2017 at 17:15

Robert Nubel · Accepted Answer · 2017-09-25 17:36:22Z

23

The difference is in how NULL values are handled. Per the docs:

IS FALSE will always return a boolean value, even if the argument is null.
= 'f' will return null if the argument is null.

So to illustrate,

rnubel=# SELECT NULL = 'f' as equals_false, NULL IS FALSE as is_false;

 equals_false | is_false
--------------+----------
              | f

Generally, most things in SQL will operate on a ternary-logic basis, where null values produce null results; so it's not surprising that Rails defaults to using the operator consistent with that basis. However, it's different from what you'd expect in Ruby (where !nil == true), so it's likely been the cause of much unexpected behavior.

answered Sep 25, 2017 at 17:36

Robert Nubel

7,5621 gold badge20 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vao Tsun · Accepted Answer · 2017-09-26 07:46:39Z

1

I would like to oppose my answer to the one from Robert Nubel. Despite the fact that comparison null = false will return null, it will not affect the resulting set:

t=> with b(v) as (values(true),(false),(null))
mon-> select v,v::text from b;
 v |   v
---+-------
 t | true
 f | false
   |
(3 rows)

t=> with b(v) as (values(true),(false),(null))
select v,v::text from b where v is false;
 v |   v
---+-------
 f | false
(1 row)

t=> with b(v) as (values(true),(false),(null))
select v,v::text from b where not v;;
 v |   v
---+-------
 f | false
(1 row)

t=> with b(v) as (values(true),(false),(null))
select v,v::text from b where v = 'f';
 v |   v
---+-------
 f | false
(1 row)

The difference in execution plans was due to index condition hit (Index Cond: (active = false))

answered Sep 26, 2017 at 7:46

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

1 Comment

Robert Nubel Over a year ago

A good point, though the two are not always equivalent. For example, compare SELECT EVERY(v IS FALSE) vs SELECT EVERY(v = 'f') when the values are just false and null -- you'll get back false for the first, but true for the latter. The difference in behavior, though subtle, explains why Postgres does not take advantage of the index.

Collectives™ on Stack Overflow

Difference between IS FALSE and = 'f' in Postgres and index use

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related