Re: [SQL] alter table
On Feb 16, 2006, at 3:11 , Maciej Piekielniak wrote:
How can i modify few fields with alter?
I think you need to alter columns one at a time. If you need them to
go into effect at the same time, you can wrap the multiple ALTER
TABLE statements in a transaction. For example,
begin;
ALTER TABLE fv_wystawione ALTER id_fv SET DEFAULT nextval
('id_fv_seq'::text);
ALTER TABLE fv_wystawione ALTER imie SET DEFAULT '';
commit;
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] How to force PostgreSQL using an index
> "Owen Jacobson" <[EMAIL PROTECTED]> writes: > > On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote: > >> I see, but that doesn't explain whether it is possible to specify the > >> index to use. It seems that those options just force PostgreSQL using > >> another plan. > > > Drop the simple index and re-create it when you're done? > > BTW, the cute way to do that is > > BEGIN; > DROP INDEX unwanted; > EXPLAIN ANALYZE whatever...; > ROLLBACK; > > No need to actually rebuild the index when you are done. > > This does hold an exclusive lock on the table for the duration of your > experiment, so maybe not such a good idea in a live environment ... but > then again, dropping useful indexes in a live environment isn't a good > idea either, and this at least reduces the duration of the experiment by > a good deal. > Thanks, that's great! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] How to force PostgreSQL using an index
"Owen Jacobson" <[EMAIL PROTECTED]> writes: > On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote: >> I see, but that doesn't explain whether it is possible to specify the >> index to use. It seems that those options just force PostgreSQL using >> another plan. > Drop the simple index and re-create it when you're done? BTW, the cute way to do that is BEGIN; DROP INDEX unwanted; EXPLAIN ANALYZE whatever...; ROLLBACK; No need to actually rebuild the index when you are done. This does hold an exclusive lock on the table for the duration of your experiment, so maybe not such a good idea in a live environment ... but then again, dropping useful indexes in a live environment isn't a good idea either, and this at least reduces the duration of the experiment by a good deal. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How to force PostgreSQL using an index
> > > > Is there a way to force PostgreSQL using an index for a SELECT > > > > statement? > > > > > > Your best bet is to do > > > > > > set enable_indexscan=false; > > > > > > and then do the EXPLAIN ANALYSE for your select. > > > > I see, but that doesn't explain whether it is possible to specify the > > index to use. It seems that those options just force PostgreSQL using > > another plan. > > (snip) > > > I have an index on EVENT_DATE_CREATED that does it job. But I though > > that I can help my favourite PostgreSQL if I create a > > composite index on > > EVENT_DATE_CREATED and EVENT_NAME (in that order as EVENT_DATE_CREATED > > is more dense that EVENT_NAME). > > > > PostgreSQL prefer the simple index rather than the composite index (for > > I/O consideration, I suppose). I wanted to know how bad the composite > > index would be if it was used (the estimate cost). > > Drop the simple index and re-create it when you're done? > Yes, that is a solution! I will try that! :-) > As I understand it, the problem with letting clients specify which indexes > to use is that they tend, on the whole, to be wrong about what's most > efficient, so it's a feature almost specifically designed for shooting > yourself in the foot with. I agree that it'd be useful for experimenting > with indexing schemes, but then, so is DROP INDEX. > Yes, indeed, such a feature could be badly used. However it may happen sometimes that the planner is wrong; I already encountered such situations with both Oracle 9i and SQL Server 2000, even with statistics calculated. That is rare but that happens. Such options /*+ */ or WITH(INDEX(...)) help in such situations, even if that really sucks for the reason you know. Daniel ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to force PostgreSQL using an index
"Daniel Caune" <[EMAIL PROTECTED]> writes: > SELECT > FROM GSLOG_EVENT > WHERE EVENT_NAME = 'player-status-update' > AND EVENT_DATE_CREATED >= > AND EVENT_DATE_CREATED < > I have an index on EVENT_DATE_CREATED that does it job. But I though > that I can help my favourite PostgreSQL if I create a composite index on > EVENT_DATE_CREATED and EVENT_NAME (in that order as EVENT_DATE_CREATED > is more dense that EVENT_NAME). Wrong ... should be EVENT_NAME first. Think about the sort order of the data to see why --- your query represents a contiguous subset of the index if EVENT_NAME is first, but not if EVENT_DATE_CREATED is first. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] How to force PostgreSQL using an index
On Wed, Feb 15, 2006 at 05:26:57PM -0500, Daniel Caune wrote: > I have an index on EVENT_DATE_CREATED that does it job. But I though > that I can help my favourite PostgreSQL if I create a composite index on > EVENT_DATE_CREATED and EVENT_NAME (in that order as EVENT_DATE_CREATED > is more dense that EVENT_NAME). > > PostgreSQL prefer the simple index rather than the composite index (for > I/O consideration, I suppose). I wanted to know how bad the composite > index would be if it was used (the estimate cost). You could do that by turning up the data the planner spits out, to see why it picks this index. It doesn't do so automatically, I think: I _think_ it's cost based (Tom will probably chime in here and remind me how little I know). My bet is that the second column isn't adding enough selectivity to help. One thing that might affect this is to fiddle with the SET STATISTICS settings on the column(s) in question. You might find that as the samples get better, your index turns out to be usefully selective, and it gets chosen. But to answer your question, no, you can't tell it "use index foo". A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] How to force PostgreSQL using an index
Daniel Caune wrote: > > Andrew Sullivan wrote: > > > On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote: > > > > > > > > Is there a way to force PostgreSQL using an index for a SELECT > > > statement? > > > > Your best bet is to do > > > > set enable_indexscan=false; > > > > and then do the EXPLAIN ANALYSE for your select. > > I see, but that doesn't explain whether it is possible to specify the > index to use. It seems that those options just force PostgreSQL using > another plan. (snip) > I have an index on EVENT_DATE_CREATED that does it job. But I though > that I can help my favourite PostgreSQL if I create a > composite index on > EVENT_DATE_CREATED and EVENT_NAME (in that order as EVENT_DATE_CREATED > is more dense that EVENT_NAME). > > PostgreSQL prefer the simple index rather than the composite index (for > I/O consideration, I suppose). I wanted to know how bad the composite > index would be if it was used (the estimate cost). Drop the simple index and re-create it when you're done? As I understand it, the problem with letting clients specify which indexes to use is that they tend, on the whole, to be wrong about what's most efficient, so it's a feature almost specifically designed for shooting yourself in the foot with. I agree that it'd be useful for experimenting with indexing schemes, but then, so is DROP INDEX. -Owen ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How to force PostgreSQL using an index
> On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote: > > Hi, > > > > > > > > Is there a way to force PostgreSQL using an index for a SELECT > > statement? I just want to confirm that the index PostgreSQL decides to > > use is better than the index I supposed PostgreSQL would use (I already > > analyze the table). > > Your best bet is to do > > set enable_indexscan=false; > > and then do the EXPLAIN ANALYSE for your select. > > You might also find that fiddling with other settings affects the > planner's idea of what would be a good plan. The planner is > sensitive to what it thinks it knows about your environment. > I see, but that doesn't explain whether it is possible to specify the index to use. It seems that those options just force PostgreSQL using another plan. For example, I have a table that contains historical data from which I try to get a subset for a specified period of time: SELECT FROM GSLOG_EVENT WHERE EVENT_NAME = 'player-status-update' AND EVENT_DATE_CREATED >= AND EVENT_DATE_CREATED < I have an index on EVENT_DATE_CREATED that does it job. But I though that I can help my favourite PostgreSQL if I create a composite index on EVENT_DATE_CREATED and EVENT_NAME (in that order as EVENT_DATE_CREATED is more dense that EVENT_NAME). PostgreSQL prefer the simple index rather than the composite index (for I/O consideration, I suppose). I wanted to know how bad the composite index would be if it was used (the estimate cost). Daniel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How to force PostgreSQL using an index
SET SESSION enable_seqscan TO OFF could be interpreted as a strong hint to the server that you want it to use indexes. It's not completely mandatory (the server WILL still do a sequential scan if it has to) but postgresql will strongly prefer index scans. You may also have some luck twiddling the cpu_index_tuple_cost option. - Owen (Apologies for the Outlookism.) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Daniel Caune Sent: Wednesday, February 15, 2006 1:59 PM To: pgsql-sql@postgresql.org Subject: [SQL] How to force PostgreSQL using an index Hi, Is there a way to force PostgreSQL using an index for a SELECT statement? I just want to confirm that the index PostgreSQL decides to use is better than the index I supposed PostgreSQL would use (I already analyze the table). Regards, -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How to force PostgreSQL using an index
On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote: > Hi, > > > > Is there a way to force PostgreSQL using an index for a SELECT > statement? I just want to confirm that the index PostgreSQL decides to > use is better than the index I supposed PostgreSQL would use (I already > analyze the table). Your best bet is to do set enable_indexscan=false; and then do the EXPLAIN ANALYSE for your select. You might also find that fiddling with other settings affects the planner's idea of what would be a good plan. The planner is sensitive to what it thinks it knows about your environment. A -- Andrew Sullivan | [EMAIL PROTECTED] If they don't do anything, we don't need their acronym. --Josh Hamilton, on the US FEMA ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] How to force PostgreSQL using an index
Hi, Is there a way to force PostgreSQL using an index for a SELECT statement? I just want to confirm that the index PostgreSQL decides to use is better than the index I supposed PostgreSQL would use (I already analyze the table). Regards, -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418
Re: [SQL] alter table
Hello Stephan, Wednesday, February 15, 2006, 9:03:26 PM, you wrote: SS> Not in 7.4, but I believe 8.1 allows that (ALTER TABLE ALTER COLUMN TYPE SS> with semi-optional USING) Thx for all. -- Best regards, Maciejmailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] alter table
On Wed, 15 Feb 2006, Owen Jacobson wrote:
> Maciej Piekielniak wrote:
> >
> > Wednesday, February 15, 2006, 8:31:17 PM, you wrote:
> > OJ> Note that prior to 8.0 PostgreSQL does not support
> > multiple ALTER actions in a single query. To get an
> > equivalent effect, wrap separate ALTER TABLE queries in a transaction:
> >
> > OJ> BEGIN;
> > OJ> alter table xyz alter column id set default nextval('xyz_seq');
> > OJ> alter table xyz alter column foo set default '';
> > OJ> COMMIT;
> > OJ> Also, are you sure you want '' as a column default, and
> > not ALTER COLUMN foo DROP DEFAULT?
> > OJ> -Owen
> >
> > OK. THX. Second question:
> >
> > First, maybe set many fields with the same action - ex. set default?
> >
> > Ex. on mysql
> >
> > ALTER TABLE proc MODIFY name char(64) DEFAULT '' NOT NULL,
> > MODIFY specific_name char(64) DEFAULT '' NOT NULL,
> > MODIFY sql_data_access
> > enum('CONTAINS_SQL',
> > 'NO_SQL',
> > 'READS_SQL_DATA',
> > 'MODIFIES_SQL_DATA'
> > ) DEFAULT 'CONTAINS_SQL' NOT NULL
>
> Under PostgreSQL 7.4 you'd need to do those as three separate ALTER TABLE
> statements:
>
> BEGIN;
> ALTER TABLE proc ALTER name DEFAULT '' NOT NULL;
> ALTER TABLE proc ALTER specific_name DEFAULT '' NOT NULL;
> ... and so on ...
> COMMIT;
>
> Note that ALTER TABLE under postgresql cannot change a column's type
> (including precision or length).
Not in 7.4, but I believe 8.1 allows that (ALTER TABLE ALTER COLUMN TYPE
with semi-optional USING)
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] alter table
Maciej Piekielniak wrote: > > Hello Owen, > > Wednesday, February 15, 2006, 8:56:05 PM, you wrote: > >> ALTER TABLE proc MODIFY name varchar(64) DEFAULT '' NOT NULL; > > OJ> Not under 7.4. > > Hmm, maybe postgres can copy constraints and properties in > "create table .. as select"? What is it you're actually trying to accomplish? There's got to be a better way, but without understanding what you're doing (rather than how) it's hard to give you advice. CREATE TABLE AS and SELECT INTO only reproduce data, not metadata. AFAIK duplicating a table's constraints involves fishing around in the pg_ system tables. -Owen ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] alter table
Hello Owen, Wednesday, February 15, 2006, 8:56:05 PM, you wrote: >> ALTER TABLE proc MODIFY name varchar(64) DEFAULT '' NOT NULL; OJ> Not under 7.4. Hmm, maybe postgres can copy constraints and properties in "create table .. as select"? CREATE TABLE fv_wystawione ( abonament) AS SELECT a.nazwa from abonamenty a; This command only copy data type. -- Best regards, Maciejmailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] alter table
Maciej Piekielniak wrote:
>
> Wednesday, February 15, 2006, 8:31:17 PM, you wrote:
> OJ> Note that prior to 8.0 PostgreSQL does not support
> multiple ALTER actions in a single query. To get an
> equivalent effect, wrap separate ALTER TABLE queries in a transaction:
>
> OJ> BEGIN;
> OJ> alter table xyz alter column id set default nextval('xyz_seq');
> OJ> alter table xyz alter column foo set default '';
> OJ> COMMIT;
> OJ> Also, are you sure you want '' as a column default, and
> not ALTER COLUMN foo DROP DEFAULT?
> OJ> -Owen
>
> OK. THX. Second question:
>
> First, maybe set many fields with the same action - ex. set default?
>
> Ex. on mysql
>
> ALTER TABLE proc MODIFY name char(64) DEFAULT '' NOT NULL,
> MODIFY specific_name char(64) DEFAULT '' NOT NULL,
> MODIFY sql_data_access
> enum('CONTAINS_SQL',
> 'NO_SQL',
> 'READS_SQL_DATA',
> 'MODIFIES_SQL_DATA'
> ) DEFAULT 'CONTAINS_SQL' NOT NULL
Under PostgreSQL 7.4 you'd need to do those as three separate ALTER TABLE
statements:
BEGIN;
ALTER TABLE proc ALTER name DEFAULT '' NOT NULL;
ALTER TABLE proc ALTER specific_name DEFAULT '' NOT NULL;
... and so on ...
COMMIT;
Note that ALTER TABLE under postgresql cannot change a column's type (including
precision or length). You can fake it by renaming the existing column,
creating a new column of the appropriate type, UPDATEing data from the old
column to the new column, [setting the new column's constraints,] and finally
removing the old column, but it's a long-winded process.
> Second, can i modify more than 1 option with alter table on
> one field?:
>
> ex (mysql):
> ALTER TABLE proc MODIFY name varchar(64) DEFAULT '' NOT NULL;
Not under 7.4.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] alter table
Hello Owen,
Wednesday, February 15, 2006, 8:31:17 PM, you wrote:
OJ> Note that prior to 8.0 PostgreSQL does not support multiple ALTER actions
in a single query. To get an equivalent effect, wrap separate ALTER TABLE
queries in a transaction:
OJ> BEGIN;
OJ> alter table xyz alter column id set default nextval('xyz_seq');
OJ> alter table xyz alter column foo set default '';
OJ> COMMIT;
OJ> Also, are you sure you want '' as a column default, and not ALTER COLUMN
foo DROP DEFAULT?
OJ> -Owen
OK. THX. Second question:
First, maybe set many fields with the same action - ex. set default?
Ex. on mysql
ALTER TABLE proc MODIFY name char(64) DEFAULT '' NOT NULL,
MODIFY specific_name char(64) DEFAULT '' NOT NULL,
MODIFY sql_data_access
enum('CONTAINS_SQL',
'NO_SQL',
'READS_SQL_DATA',
'MODIFIES_SQL_DATA'
) DEFAULT 'CONTAINS_SQL' NOT NULL
Second, can i modify more than 1 option with alter table on one field?:
ex (mysql):
ALTER TABLE proc MODIFY name varchar(64) DEFAULT '' NOT NULL;
--
Best regards,
Maciejmailto:[EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] alter table
Andreas Kretschmer wrote:
>
> Maciej Piekielniak <[EMAIL PROTECTED]> schrieb:
>
> > Hello Andreas,
> >
> > Wednesday, February 15, 2006, 7:54:28 PM, you wrote:
> > AK> test=# alter table xyz alter column id set default
> nextval('xyz_seq'), alter column foo set default '';
> >
> > PGAdmin-SQL:
> >
> > alter table xyz alter column id set default
> nextval('xyz_seq'), alter column foo set default '';
> >
> > ERROR: syntax error at or near "," at character 63
>
> Hmm.
>
> test=# select version();
>version
> --
>
> PostgreSQL 8.1.2 on i486-pc-linux-gnu, compiled by GCC cc
> (GCC) 4.0.3 20060104 (prerelease) (Debian 4.0.2-6)
> (1 row)
Note that prior to 8.0 PostgreSQL does not support multiple ALTER actions in a
single query. To get an equivalent effect, wrap separate ALTER TABLE queries
in a transaction:
BEGIN;
alter table xyz alter column id set default nextval('xyz_seq');
alter table xyz alter column foo set default '';
COMMIT;
Also, are you sure you want '' as a column default, and not ALTER COLUMN foo
DROP DEFAULT?
-Owen
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] alter table
Hello Andreas, Wednesday, February 15, 2006, 8:27:00 PM, you wrote: AK> test=# select version(); AK>version AK> -- AK> PostgreSQL 8.1.2 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.3 20060104 (prerelease) (Debian 4.0.2-6) AK> (1 row) AK> i'm working with the native client - psql. AK> HTH, Andreas "PostgreSQL 7.4.7 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc (GCC) 3.3.5 (Debian 1:3.3.5-12)" I must work on 7.4... -- Best regards, Maciejmailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] alter table
Maciej Piekielniak <[EMAIL PROTECTED]> schrieb:
> Hello Andreas,
>
> Wednesday, February 15, 2006, 7:54:28 PM, you wrote:
> AK> test=# alter table xyz alter column id set default nextval('xyz_seq'),
> alter column foo set default '';
>
> PGAdmin-SQL:
>
> alter table xyz alter column id set default nextval('xyz_seq'), alter column
> foo set default '';
>
> ERROR: syntax error at or near "," at character 63
Hmm.
test=# select version();
version
--
PostgreSQL 8.1.2 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.3 20060104
(prerelease) (Debian 4.0.2-6)
(1 row)
i'm working with the native client - psql.
HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."(unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] alter table
Hello Andreas,
Wednesday, February 15, 2006, 7:54:28 PM, you wrote:
AK> test=# alter table xyz alter column id set default nextval('xyz_seq'),
alter column foo set default '';
PGAdmin-SQL:
alter table xyz alter column id set default nextval('xyz_seq'), alter column
foo set default '';
ERROR: syntax error at or near "," at character 63
--
Best regards,
Maciejmailto:[EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] alter table
Maciej Piekielniak <[EMAIL PROTECTED]> schrieb:
> Hello ,
>
> How can i modify few fields with alter?
>
> ALTER TABLE fv_wystawione
> ALTER id_fv SET DEFAULT nextval('id_fv_seq'::text),
> ALTER imie SET DEFAULT '';
test=# create table xyz (id int not null);
CREATE TABLE
test=# create sequence xyz_seq;
CREATE SEQUENCE
test=# alter table xyz alter column id set default nextval('xyz_seq');
ALTER TABLE
or:
test=# drop TABLE xyz;
DROP TABLE
test=# create table xyz (id int not null, foo varchar);
CREATE TABLE
test=# alter table xyz alter column id set default nextval('xyz_seq'), alter
column foo set default '';
ALTER TABLE
HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."(unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[SQL] alter table
Hello ,
How can i modify few fields with alter?
ALTER TABLE fv_wystawione
ALTER id_fv SET DEFAULT nextval('id_fv_seq'::text),
ALTER imie SET DEFAULT '';
Syntax error in last line.
--
Best regards,
Maciej mailto:[EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Modifying postgresql.org file
Ken Hill wrote: > On Wed, 2006-02-15 at 18:09 +0100, Peter Eisentraut wrote: > > > Ken Hill wrote: > > > Can someone point me in a > > > direction as to where I can learn how to modify the postgresql.org > > > file to increase work_mem? > > > > RTFM > > I apologize for my lack of knowledge, but what is "RTFM"? "Read The Fine Manual" If you hear that the F means something else, don't listen!! ;-) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Modifying postgresql.org file
On Wed, 2006-02-15 at 18:09 +0100, Peter Eisentraut wrote: Ken Hill wrote: > Can someone point me in a > direction as to where I can learn how to modify the postgresql.org > file to increase work_mem? RTFM I apologize for my lack of knowledge, but what is "RTFM"?
Re: [SQL] Modifying postgresql.org file
Andrew Sullivan wrote: > > On Wed, Feb 15, 2006 at 08:15:46AM -0800, Ken Hill wrote: > > It has been suggested to me to increase my work_mem to make queries > > preform faster. I believe I do this in the 'postgresql.org' file. I > > seem to have two of these files: > > > > /etc/postgresql/7.4/main/postgresql.org > > /usr/share/postgresql/7.4/postgresql.conf.sample > > Where did you get this version of Postgres? The main config file > should be $PGDATA/postgresql.conf. Anything else is probably the > result of your system's packaging having done some magic. But in any > case, unless I'm misremembering, the work_mem setting isn't in 7.4. In 8.0, the setting sort_mem was renamed to work_mem (sayeth the 8.0.0 release notes). The 7.4 documentation describes the same features for sort_mem that are now described under work_mem, so adjusting sort_mem should be appropriate. -Owen ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Modifying postgresql.org file
Ken Hill wrote: > Can someone point me in a > direction as to where I can learn how to modify the postgresql.org > file to increase work_mem? RTFM -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Modifying postgresql.org file
On Wed, Feb 15, 2006 at 08:35:31AM -0800, Ken Hill wrote: > > This is how Ubuntu installed postgresql via it's synaptic package > manager. Ok, then I suspect you need to consult the Ubuntu docs about what they did differently. If my understanding of Ubuntu is correct, that should be under /usr/share/doc/. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Modifying postgresql.org file
On Wed, 2006-02-15 at 11:20 -0500, Andrew Sullivan wrote: On Wed, Feb 15, 2006 at 08:15:46AM -0800, Ken Hill wrote: > It has been suggested to me to increase my work_mem to make queries > preform faster. I believe I do this in the 'postgresql.org' file. I > seem to have two of these files: > > /etc/postgresql/7.4/main/postgresql.org > /usr/share/postgresql/7.4/postgresql.conf.sample Where did you get this version of Postgres? The main config file should be $PGDATA/postgresql.conf. Anything else is probably the result of your system's packaging having done some magic. But in any case, unless I'm misremembering, the work_mem setting isn't in 7.4. You can check the docs on postgresql.org. There's a whole section on the configuration variables, and manuals are available for several releases back. A This is how Ubuntu installed postgresql via it's synaptic package manager.
Re: [SQL] Modifying postgresql.org file
Hi, On Wed, 2006-02-15 at 08:15 -0800, Ken Hill wrote: > It has been suggested to me to increase my work_mem to make queries > preform faster. I believe I do this in the 'postgresql.org' file. I > seem to have two of these files: > > /etc/postgresql/7.4/main/postgresql.org > /usr/share/postgresql/7.4/postgresql.conf.sample It must be postgresql.conf, not postgresql. Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PL/php, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Modifying postgresql.org file
On Wed, Feb 15, 2006 at 08:15:46AM -0800, Ken Hill wrote: > It has been suggested to me to increase my work_mem to make queries > preform faster. I believe I do this in the 'postgresql.org' file. I > seem to have two of these files: > > /etc/postgresql/7.4/main/postgresql.org > /usr/share/postgresql/7.4/postgresql.conf.sample Where did you get this version of Postgres? The main config file should be $PGDATA/postgresql.conf. Anything else is probably the result of your system's packaging having done some magic. But in any case, unless I'm misremembering, the work_mem setting isn't in 7.4. You can check the docs on postgresql.org. There's a whole section on the configuration variables, and manuals are available for several releases back. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Modifying postgresql.org file
It has been suggested to me to increase my work_mem to make queries preform faster. I believe I do this in the 'postgresql.org' file. I seem to have two of these files: /etc/postgresql/7.4/main/postgresql.org /usr/share/postgresql/7.4/postgresql.conf.sample I believe the second one is an example/sample file (similar to how xorg.conf comes with a sample file). Can someone point me in a direction as to where I can learn how to modify the postgresql.org file to increase work_mem? I have the O'Reilly book "Practical PostgreSQL" but it doesn't seem to cover this topic. I'm fairly confident that I can tweak the postgresql.org file; given that I was successfuly tweaking my xorg.conf file. Any help/guidance is very much appreciated. -Ken
Re: [SQL] Non Matching Records in Two Tables
On Tue, 2006-02-14 at 15:05 -0800, Bryce Nesbitt wrote: Ken Hill wrote: >> also (hate to be obvious) have you analyzed lately? >> I'd say that's fair game, not obvious. Vacuum/Analyze is ar so aparent to a person moving to Postgres from other DB's. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org In my O'Reily "Practical PostgreSQL" book it recommends vacuuming a production database on a daily basis. I liken this to MS Access' "compact/repair" procedure.
