Re: B-tree index on a VARCHAR(4000) column

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: John Turner <fenwayriffs(at)gmail(dot)com>
Cc: Merlin Moncure <mmoncure(at)gmail(dot)com>, Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: B-tree index on a VARCHAR(4000) column
Date: 2017-09-10 18:24:47
Message-ID: 21945.1505067887@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

John Turner <fenwayriffs(at)gmail(dot)com> writes:
> On Sun, Sep 10, 2017 at 10:42 AM Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> Nope. Memory usage is proportional to the size of the string, not the
>> maximum length for varchar. Maximum length is a constraint.

> Ok, thanks for verifying. I was curious since other platforms seem to
> handle this aspect of memory allocation differently (more crudely, perhaps)
> based on estimation of how fully populated the column _might_ be given a
> size constraint:
> https://sqlperformance.com/2017/06/sql-plan/performance-myths-oversizing-strings

AFAIR, the only way in which a different declared max column length would
affect Postgres' behavior like that is that, depending on what other
columns are in the table, it might be able to prove that it doesn't need
to create a "toast table" for the table, because no row in the table could
ever be wide enough to require toasting. That would save a few
microseconds during table creation and drop ... but an unused toast table
that's just sitting there is surely not much overhead.

For every other purpose, PG just pays attention to the actual column
values' lengths.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message techmail+pgsql 2017-09-10 18:25:45 Re: pg_ident mapping Kerberos Usernames
Previous Message John Turner 2017-09-10 18:00:59 Re: B-tree index on a VARCHAR(4000) column