Re: How are null's stored? - Mailing list pgsql-performance

From Stephan Szabo
Subject Re: How are null's stored?
Date
Msg-id 20030513123355.B29586-100000@megazone23.bigpanda.com
Whole thread Raw
In response to Re: How are null's stored?  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: How are null's stored?
List pgsql-performance
On Mon, 12 May 2003, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > 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.
>
> Yeah, I've been wondering about that too.  A large part of the problem
> is that varchar has its own set of operators, which the planner has no
> right to assume behave exactly like the text ones ... but they do.  It
> might work to rip out the redundant varchar operators and allow indexes
> on varchar to become truly textual indexes (ie, they'd be text_ops not
> varchar_ops opclass).  There might be a few tweaks needed to get the
> planner to play nice with indexes that require implicit coercions, but
> I think it could be made to work.

This seems to possibly work on 7.4.  I took my system and removed the
varchar comparison operators and directly made a text_ops index on a
varchar(30).
That gave me indexscans for
 col = 'a'
 col = 'a'::varchar
 col = 'a'::text
 col = 'a' || 'b'

but I don't know if it has other bad effects yet.


> Another idea that has been rattling around is to stop treating bpchar as
> binary-equivalent to text, and in fact to make bpchar-to-text promotion
> go through rtrim() to eliminate padding spaces.

I guess this depends on how we read the comparisons/conversions from PAD
SPACE to NO PAD are supposed to work, but I think this would be good and
make things easier for alot of people since most people don't expect it,
especially when using functions like upper and lower that return text.


pgsql-performance by date:

Previous
From: Josh Berkus
Date:
Subject: Re: PERFORMANCE and SIZE
Next
From: Tom Lane
Date:
Subject: Re: How are null's stored?