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

"Dawid Kuroczko" <qnex42(at)gmail(dot)com> writes:

> If I may suggest it -- try to run the queries yourself. You will find the
> problem lies not in the statistics.

I was more concerned that there might be other discrepancies between the
commands in the email and the actual commands you're running.

Running it myself I do see the same behaviour in 8.3. I'm not sure whether
this is something we expect to work or not though. Binary-compatible types are
a bit of weirdness I still haven't quite absorbed.

postgres=# alter table foo alter column i type text;
ALTER TABLE

postgres=# analyze foo;
ANALYZE

postgres=# explain analyze select * from foo where i='17';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Scan using foo_i_index on foo (cost=0.00..8.28 rows=1 width=5) (actual time=0.132..0.138 rows=1 loops=1)
Index Cond: (i = '17'::text)
Total runtime: 0.235 ms
(3 rows)

postgres=# alter table foo alter column i type varchar(100);
ALTER TABLE

postgres=# analyze foo;
ANALYZE

postgres=# explain analyze select * from foo where i='17';
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..1681.00 rows=1 width=5) (actual time=0.147..281.349 rows=1 loops=1)
Filter: ((i)::text = '17'::text)
Total runtime: 281.448 ms
(3 rows)

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com