Thread: modifying system table

modifying system table

From
Divya Jain
Date:
I'm running Postgres 7.2.4. A VARCHAR column in my schema needs to be longer than the currently defined max length.
Whatis the best way to achieve this without adding a new column with the correct VARCHAR length ? 

One way I figured is to modify the pg_attribute table and set atttypmod to the correct value. Just want to know if
thereis any risk involved in doing this. 
Thanks
Divya


Re: modifying system table

From
Robert Treat
Date:
It's generally considered safe, but search the archives for the proper
formula so you get the results your hoping for. (IIRC its length + 4,
but I'd search just to be sure).

Robert Treat

On Wed, 2003-05-28 at 17:15, Divya Jain wrote:
> I'm running Postgres 7.2.4. A VARCHAR column in my schema needs to be longer than the currently defined max length.
Whatis the best way to achieve this without adding a new column with the correct VARCHAR length ? 
>
> One way I figured is to modify the pg_attribute table and set atttypmod to the correct value. Just want to know if
thereis any risk involved in doing this. 
> Thanks
> Divya
>




defined max length

From
lexx@gorodok.net
Date:
Hello ALL.

Where can I find (and set if need) all defined max length restrictions of PostgreSQL
server.
for example,
    max lenght of VARCHAR,
or any variable-length array,
or max query length and so on ?

I'm using PostgreSQL 7.2, RH linux 7.2


Thanks in advance.


--
Best regards,
 lexx                            mailto:lexx@gorodok.net



Re: defined max length

From
Andrew Biagioni
Date:
I recommend you look at chapter 5, "Data Types", of the PostgreSQL
User's Guide.

lexx@gorodok.net wrote:

>Hello ALL.
>
>Where can I find (and set if need) all defined max length restrictions of PostgreSQL
>server.
>for example,
>    max lenght of VARCHAR,
>or any variable-length array,
>or max query length and so on ?
>
>I'm using PostgreSQL 7.2, RH linux 7.2
>
>
>Thanks in advance.
>
>
>
>



Indexing a boolean

From
Kris Kiger
Date:
I would appreciate it if I could get some thoughts on indexing a field
with only two values?  For example, I have a table with a few million
rows in it.  All items in this table are broken up into two categories
using 'T' or 'F'.  It seems logical to me that an index on this field
would create two logical 'buckets', so that one could say, "I want all
'T' values", or "I want all 'F' values" and merely have to look in the
appropriate bucket, rather than have to perform a sequential scan
through three million items every time a request is made based on 'T' or
'F'.  If I were to create an index on a char(1) field that contains only
values of 'T' or 'F', would the query analyzer override the use of this
index?  How does Postgres address this problem and what are all of your
thoughts on this issue?  I appreciate the help!

Kris


Re: Indexing a boolean

From
Bruno Wolff III
Date:
On Thu, Aug 21, 2003 at 15:47:39 -0500,
  Kris Kiger <kris@musicrebellion.com> wrote:
> I would appreciate it if I could get some thoughts on indexing a field
> with only two values?  For example, I have a table with a few million
> rows in it.  All items in this table are broken up into two categories
> using 'T' or 'F'.  It seems logical to me that an index on this field
> would create two logical 'buckets', so that one could say, "I want all
> 'T' values", or "I want all 'F' values" and merely have to look in the
> appropriate bucket, rather than have to perform a sequential scan
> through three million items every time a request is made based on 'T' or
> 'F'.  If I were to create an index on a char(1) field that contains only
> values of 'T' or 'F', would the query analyzer override the use of this
> index?  How does Postgres address this problem and what are all of your
> thoughts on this issue?  I appreciate the help!

You probably don't want to use an index unless one of the two values
is much more common than the other. In that case you want to use a partial
index on the rare condition.

Re: Indexing a boolean

From
"scott.marlowe"
Date:
On Thu, 21 Aug 2003, Kris Kiger wrote:

> I would appreciate it if I could get some thoughts on indexing a field
> with only two values?  For example, I have a table with a few million
> rows in it.  All items in this table are broken up into two categories
> using 'T' or 'F'.  It seems logical to me that an index on this field
> would create two logical 'buckets', so that one could say, "I want all
> 'T' values", or "I want all 'F' values" and merely have to look in the
> appropriate bucket, rather than have to perform a sequential scan
> through three million items every time a request is made based on 'T' or
> 'F'.  If I were to create an index on a char(1) field that contains only
> values of 'T' or 'F', would the query analyzer override the use of this
> index?  How does Postgres address this problem and what are all of your
> thoughts on this issue?  I appreciate the help!

Often the best approach here is to make a partial index:

create index table_dx on table (bool_field) where bool_field IS TRUE;

This works well if you have a large portion of the boolean fields set to
FALSE, and want to find the few that are TRUE.  Reverse the TRUE and false
for other situations.


Partial indexes (was: Re: Indexing a boolean)

From
Sam Barnett-Cormack
Date:
On Thu, 21 Aug 2003, scott.marlowe wrote:

> On Thu, 21 Aug 2003, Kris Kiger wrote:
>
> > I would appreciate it if I could get some thoughts on indexing a field
> > with only two values?  For example, I have a table with a few million
> > rows in it.  All items in this table are broken up into two categories
> > using 'T' or 'F'.  It seems logical to me that an index on this field
> > would create two logical 'buckets', so that one could say, "I want all
> > 'T' values", or "I want all 'F' values" and merely have to look in the
> > appropriate bucket, rather than have to perform a sequential scan
> > through three million items every time a request is made based on 'T' or
> > 'F'.  If I were to create an index on a char(1) field that contains only
> > values of 'T' or 'F', would the query analyzer override the use of this
> > index?  How does Postgres address this problem and what are all of your
> > thoughts on this issue?  I appreciate the help!
>
> Often the best approach here is to make a partial index:
>
> create index table_dx on table (bool_field) where bool_field IS TRUE;
>
> This works well if you have a large portion of the boolean fields set to
> FALSE, and want to find the few that are TRUE.  Reverse the TRUE and false
> for other situations.

A more general question:

Can the planner tell which index to use if there is more than one that
fits the bill? Like if there is a full index and one or more partial
indexes on a field, can it determine which to use for a given query?

--

Sam Barnett-Cormack
Software Developer                           |  Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk)  |  Lancaster University

Re: Partial indexes (was: Re: Indexing a boolean)

From
"scott.marlowe"
Date:
On Fri, 22 Aug 2003, Sam Barnett-Cormack wrote:

> On Thu, 21 Aug 2003, scott.marlowe wrote:
>
> > On Thu, 21 Aug 2003, Kris Kiger wrote:
> >
> > > I would appreciate it if I could get some thoughts on indexing a field
> > > with only two values?  For example, I have a table with a few million
> > > rows in it.  All items in this table are broken up into two categories
> > > using 'T' or 'F'.  It seems logical to me that an index on this field
> > > would create two logical 'buckets', so that one could say, "I want all
> > > 'T' values", or "I want all 'F' values" and merely have to look in the
> > > appropriate bucket, rather than have to perform a sequential scan
> > > through three million items every time a request is made based on 'T' or
> > > 'F'.  If I were to create an index on a char(1) field that contains only
> > > values of 'T' or 'F', would the query analyzer override the use of this
> > > index?  How does Postgres address this problem and what are all of your
> > > thoughts on this issue?  I appreciate the help!
> >
> > Often the best approach here is to make a partial index:
> >
> > create index table_dx on table (bool_field) where bool_field IS TRUE;
> >
> > This works well if you have a large portion of the boolean fields set to
> > FALSE, and want to find the few that are TRUE.  Reverse the TRUE and false
> > for other situations.
>
> A more general question:
>
> Can the planner tell which index to use if there is more than one that
> fits the bill? Like if there is a full index and one or more partial
> indexes on a field, can it determine which to use for a given query?

Basically, for partial / functional indexes, the create index where clause
needs to exactly (or nearly so) match the select query's where clause:

create index table_dx on table (bool_field) where bool_field IS TRUE;
select * from table where bool_field = 't';  <- might not match (currently
won't match)
select * from table where bool_field IS TRUE;  <- will match

And yes, the query planner will usually know to use the most selective
index, i.e. the smaller, cheaper to use index.


Re: Partial indexes (was: Re: Indexing a boolean)

From
Tom Lane
Date:
"scott.marlowe" <scott.marlowe@ihs.com> writes:
> Basically, for partial / functional indexes, the create index where clause
> needs to exactly (or nearly so) match the select query's where clause:

Another consideration is that an index declaration like this:

    create index fooi on foo (flag) where flag;

is really rather redundant, since the entries will only be made for rows
where flag is true, and so storage of the column value in the index is
useless.  You may be able to get more mileage out of the index by making
the index on another column that you often test in conjunction with the
flag.  For example:

regression=# create table foo (flag bool, ts timestamp);
CREATE TABLE
regression=# create index fooi on foo(ts) where flag;
CREATE INDEX
regression=# explain select * from foo where ts > '2003-10-11' and flag;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..22.50 rows=167 width=9)
   Filter: ((ts > '2003-10-11 00:00:00'::timestamp without time zone) AND flag)
(2 rows)

regression=# set enable_seqscan TO 0;
SET
regression=# explain select * from foo where ts > '2003-10-11' and flag;
                               QUERY PLAN
-------------------------------------------------------------------------
 Index Scan using fooi on foo  (cost=0.00..43.25 rows=167 width=9)
   Index Cond: (ts > '2003-10-11 00:00:00'::timestamp without time zone)
   Filter: flag
(3 rows)

(In a more realistic situation, the planner would probably have chosen
the indexscan without any prompting.)

            regards, tom lane

Re: Indexing a boolean

From
Renney Thomas
Date:
scott.marlowe wrote:

>On Thu, 21 Aug 2003, Kris Kiger wrote:
>
>
>>I would appreciate it if I could get some thoughts on indexing a field
>>with only two values?  For example, I have a table with a few million
>>

>>Often the best approach here is to make a partial index:
>>
>>create index table_dx on table (bool_field) where bool_field IS TRUE;
>>
>>This works well if you have a large portion of the boolean fields set to
>>FALSE, and want to find the few that are TRUE.  Reverse the TRUE and false
>>for other situations.
>>
>>
>>

When will PGSql have bitmapped indexes for these cases - like Oracle?
Will it be in a release sometime soon?


Re: Indexing a boolean

From
Bruce Momjian
Date:
Renney Thomas wrote:
> scott.marlowe wrote:
>
> >On Thu, 21 Aug 2003, Kris Kiger wrote:
> >
> >
> >>I would appreciate it if I could get some thoughts on indexing a field
> >>with only two values?  For example, I have a table with a few million
> >>
>
> >>Often the best approach here is to make a partial index:
> >>
> >>create index table_dx on table (bool_field) where bool_field IS TRUE;
> >>
> >>This works well if you have a large portion of the boolean fields set to
> >>FALSE, and want to find the few that are TRUE.  Reverse the TRUE and false
> >>for other situations.
> >>
> >>
> >>
>
> When will PGSql have bitmapped indexes for these cases - like Oracle?
> Will it be in a release sometime soon?

Uh, we really don't know --- when someone codes it.  We do have
/contrib/intarray, which might help.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073