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

On 7/24/07, Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
> "Dawid Kuroczko" <qnex42(at)gmail(dot)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