varchar does not work too well with IS NOT NULL partial indexes. - Mailing list pgsql-general

Hello, I guess I've stuck upon one problem with (I guess) implicit
casting varchar to text...

Basically it looks like planner makes better use of
WHERE ... IS NOT NULL indexes if either you explicitly
put "text" as a column type or that you cast the column
to ::text when making index.

Here's a self-contained example (this is 8.2.4 server)

CREATE TABLE foo (i int);

INSERT INTO foo
  SELECT CASE WHEN i%10=0 THEN NULL ELSE i END
     FROM generate_series(1,100000) AS n(i);

CREATE INDEX foo_i_index ON foo (i) WHERE i IS NOT NULL;
ANALYZE foo;

EXPLAIN SELECT * FROM foo WHERE i=17;
                              QUERY PLAN
-----------------------------------------------------------------------
 Index Scan using foo_i_index on foo  (cost=0.00..8.28 rows=1 width=4)
   Index Cond: (i = 17)

ALTER TABLE foo ALTER COLUMN i TYPE text;
EXPLAIN SELECT * FROM foo WHERE i=17;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=12.14..554.42 rows=500 width=32)
   Recheck Cond: (i = '17'::text)
   ->  Bitmap Index Scan on foo_i_index  (cost=0.00..12.01 rows=498 width=0)
         Index Cond: (i = '17'::text)

 EXPLAIN SELECT * FROM foo WHERE i=17;
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on foo  (cost=0.00..1772.00 rows=500 width=34)
   Filter: ((i)::text = '17'::text)

CREATE INDEX foo_i2_index ON foo ((i::text));
EXPLAIN SELECT * FROM foo WHERE i='17'::text;
                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=12.14..554.82 rows=500 width=34)
   Recheck Cond: ((i)::text = '17'::text)
   ->  Bitmap Index Scan on foo_i2_index  (cost=0.00..12.01 rows=500 width=0)
         Index Cond: ((i)::text = '17'::text)

Regards,
    Dawid