Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux