Re: varchar does not work too well with IS NOT NULL partial indexes.

On 7/24/07, Gregory Stark <stark@enterprisedb.com> wrote:
> "Dawid Kuroczko" <qnex42@gmail.com> writes:
>
> > 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)
>
> I think you've lost some single-quotes around 17 in this query. With the
> single-quotes it works like this which seems like the correct result. You
> don't need the casts in the index definition if you write the query with
> single-quotes.

Well, maybe I used wrong example...

CREATE TABLE foo (t varchar(100));
INSERT INTO foo
  SELECT CASE WHEN i % 10 = 0 THEN NULL ELSE 'X' || i END
     FROM generate_series(1,1000000) AS n(i);

What we have here is a table with every 10th row NULL.

CREATE INDEX foo_t_index ON foo (t) WHERE t IS NOT NULL;

...and an index which will contain only NOT NULL values.

Now, if we:

# EXPLAIN ANALYZE SELECT t FROM foo WHERE t='X17';
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..18025.78 rows=1 width=8) (actual
time=0.079..565.661 rows=1 loops=1)
   Filter: ((t)::text = 'X17'::text)
 Total runtime: 565.689 ms


# EXPLAIN ANALYZE SELECT t FROM foo WHERE t='X17';
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on foo  (cost=0.00..178.00 rows=50 width=68)
   Filter: ((t)::text = 'X17'::text)
(2 rows)

But if we:

# ALTER TABLE foo ALTER COLUMN t TYPE text;

# EXPLAIN ANALYZE SELECT t FROM foo WHERE t='X17';
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Index Scan using foo_t_index on foo  (cost=0.00..8.39 rows=1
width=10) (actual time=0.051..0.052 rows=1 loops=1)
   Index Cond: (t = 'X17'::text)
 Total runtime: 0.077 ms

...so it does nothing to do with single quotes.  Actually it works
fine, so long as you use text instead of varchar2:

# EXPLAIN ANALYZE SELECT t FROM foo WHERE t=17;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Index Scan using foo_t_index on foo  (cost=0.00..8.39 rows=1
width=10) (actual time=0.014..0.014 rows=0 loops=1)
   Index Cond: (t = '17'::text)
 Total runtime: 0.034 ms


I hope I have stated the problem clearly now. :-)

   Regards,
      Dawid