Re: Speed difference between != and = operators?

Paul Caskey wrote:
>
> This query takes 206 seconds:
>
> SELECT t1.blah, t1.foo, t2.id
> FROM t1, t2, t3
> WHERE t1.SessionId = 427
> AND t1.CatalogId = 22
> AND t1.CatalogId = t3.CatalogId
> AND t2.id = t3.SomeId
> AND t2.Active != 0
>
> If I change the last line to this, it takes 1 second:
>
> AND t2.Active = 1
>
> The "Active" field is 0 or 1.  The query returns the same rows, either way
> (about 1700 rows).  There is an index on the Active field.
>
> These two queries both take 1 second:
>
> SELECT * FROM t2 WHERE Active = 1;
> SELECT * FROM t2 WHERE Active != 0;
>
> Any ideas?  Possible bug?


My coworker adds:

Interesting note... when you change the operator the way you
said you get a completely different query plan:

Original: (Active != 0)

Nested Loop  (cost=0.00..143.49 rows=1 width=20)
  ->  Nested Loop  (cost=0.00..141.46 rows=1 width=12)
        ->  Seq Scan on t2  (cost=0.00..48.11 rows=1 width=4)
        ->  Seq Scan on t3  (cost=0.00..71.50 rows=1748 width=8)
  ->  Index Scan using idx1 on t3 (cost=0.00..2.02 rows=1 width=8)


Altered: (Active = 1)

Nested Loop  (cost=5.06..272.65 rows=62 width=20)
  ->  Hash Join  (cost=5.06..146.49 rows=62 width=16)
        ->  Seq Scan on t3  (cost=0.00..71.49 rows=1748 width=8)
        ->  Hash  (cost=5.05..5.05 rows=4 width=8)
              ->  Index Scan using idx1 on t1 (cost=0.00..5.05 rows=4
width=8)
  ->  Index Scan using t2_pkey on t2  (cost=0.00..2.02 rows=1 width=4)


What's odd is the maximum cost is low in both cases, but is even lower in
the query than runs 200 times slower.

--
Paul Caskey        paul@nmxs.com        Software Engineer
New Mexico Software    5041 Indian School NE    Albuquerque, NM 87110
--