0

Why is PostgreSQL doing a table scan on column != column?

Isn't this query guaranteed to return no results? Isn't anything != anything guaranteed to be always false for any record on a not null column?

test=# CREATE TABLE toasters (id integer NOT NULL);
CREATE TABLE
test=# EXPLAIN SELECT * FROM toasters where id != id;
                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on toasters  (cost=0.00..40.00 rows=2388 width=4)
   Filter: (id <> id)
(2 rows)

test=# EXPLAIN ANALYZE SELECT * FROM toasters where id != id;
QUERY PLAN
------------------------------------------------------------------------------------------------------
 Seq Scan on toasters  (cost=0.00..40.00 rows=2388 width=4) (actual time=0.002..0.002 rows=0 loops=1)
   Filter: (id <> id)
 Total runtime: 3.082 ms
(3 rows)

This is exactly the same for a table with millions of records.

A bit more context here: this query is generated by the SQLAclchemy ORM. http://docs.sqlalchemy.org/en/rel_0_9/faq.html#why-does-col-in-produce-col-col-why-not-1-0

  • It's doing the same regardless of the size of the table. – charlax Jun 27 '14 at 19:51
  • Also added analyze. – charlax Jun 27 '14 at 19:53
  • This is exactly the same for a table with about millions of records. Also, regardless of the index, isn't anything != anything guaranteed to be always false for any record on a not null column? – charlax Jun 27 '14 at 20:02
  • 2
    On second thought: the optimizer could recognize that the condition is always false. I don't know why it doesn't. Maybe you should post this to the Postgres mailing list. – a_horse_with_no_name Jun 27 '14 at 20:03
  • 1
    The optimiser isn't smart enough to spot this case and prove it's always false. Why would you write it in the first place? (One concern is that there are tons of things that could be optimized out, but each one adds a little more work and slows the optimizer pass down, so we don't like to add potentially expensive optimizer checks for "don't do that" coding patterns in queries. Unfortunately ORMs love to generate really stupid queries, so it's becoming less and less avoidable. Maybe we need an "ORM mode" in the optimizer...) – Craig Ringer Jun 28 '14 at 4:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.