Re: How to use index in WHERE int = float - Mailing list pgsql-general

> Did you read what I wrote?  Cause you just repeated it as an argument
> against my point.

Lets re-visit the second issue in my reply.

I tried in 8.3

explain SELECT dokumnr
     FROM DOK
    where dokumnr IN (1227714)
 AND
 ( '0'  or
  dokumnr IN (SELECT dokumnr FROM firma1.bilkaib )
          )

"Index Scan using dok_pkey on dok  (cost=16.55..24.82 rows=1 width=4)"
"  Index Cond: (dokumnr = 1227714)"
"  Filter: (hashed subplan)"
"  SubPlan"
"    ->  Seq Scan on bilkaib  (cost=0.00..15.44 rows=444 width=4)"


and

  explain SELECT dokumnr
     FROM DOK
    where dokumnr IN (1227714)
 AND
 ( -- '0'  or
  dokumnr IN (SELECT dokumnr FROM firma1.bilkaib )
          )

"Nested Loop IN Join  (cost=0.00..16.55 rows=1 width=4)"
"  ->  Index Scan using dok_pkey on dok  (cost=0.00..8.27 rows=1 width=4)"
"        Index Cond: (dokumnr = 1227714)"
"  ->  Index Scan using bilkaib_dokumnr_idx on bilkaib  (cost=0.00..8.27
rows=1 width=4)"
"        Index Cond: (bilkaib.dokumnr = 1227714)"


As you see simply removing constant expression

 '0' or

produces different query plan which is much faster for large amoutnts of
data.
Same results are for large data set and for earlier postgresql versions.

Do you think that is OK and reasonable ?

Andrus.