From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Josh Berkus <josh(at)agliodbs(dot)com> |
Cc: | Ryan <pgsql-performance(at)seahat(dot)com>, <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: How are null's stored? |
Date: | 2003-05-12 23:19:25 |
Message-ID: | 20030512160355.L17085-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-performance |
On Mon, 12 May 2003, Josh Berkus wrote:
> > So if the internal format is identical, why does the INFERNAL database
> > ignore indexes when you have a text compared to a varchar?
>
> I don't seem to have this problem; I use TEXT or VARCHAR willy-nilly,
> including in LIKE 'string%' and UPPER(field) queries, and the indexes work
> fine.
I can get the case he's complaining about with some cases I believe.
With an indexed varchar field, I can get 7.3.1 to give me:
sszabo=# set enable_seqscan=off;
SET
sszabo=# explain select * from aq2 where a=('f' || 'g');
QUERY PLAN
---------------------------------------------------------------------
Seq Scan on aq2 (cost=100000000.00..100000022.50 rows=1 width=168)
Filter: ((a)::text = 'fg'::text)
but
sszabo=# explain select * from aq2 where a=('f' || 'g')::varchar;
QUERY PLAN
----------------------------------------------------------------------
Index Scan using aq2_pkey on aq2 (cost=0.00..4.82 rows=1 width=168)
Index Cond: (a = 'fg'::character varying)
or
sszabo=# explain select * from aq2 where a=('f' || 'g'::varchar);
QUERY PLAN
----------------------------------------------------------------------
Index Scan using aq2_pkey on aq2 (cost=0.00..4.82 rows=1 width=168)
Index Cond: (a = 'fg'::character varying)
All in all, I'm not sure what the semantic differences between a varchar
with no length specified and a text are in PostgreSQL actually and if the
whole thing could be simplified in some way that doesn't break backwards
compatibility.
From | Date | Subject | |
---|---|---|---|
Next Message | Christopher Browne | 2003-05-12 23:29:46 | Re: Scheduled jobs |
Previous Message | alex avriette | 2003-05-12 23:11:34 | Re: patch src/bin/psql/help.c |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-05-12 23:50:24 | Re: How are null's stored? |
Previous Message | Josh Berkus | 2003-05-12 22:46:39 | Re: How are null's stored? |