Thread: modifying system table
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
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 >
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
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. > > > >
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
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.
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.
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
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.
"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
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?
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