On 7/24/07, Gregory Stark <stark@xxxxxxxxxxxxxxxx> wrote:
"Dawid Kuroczko" <qnex42@xxxxxxxxx> 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
