[SQL] Droping indexes
Hi all, Please, if I drop all indexes from a table, can I recreate them after performing a vacuum full at this table? I mean, I do not know details about the indexes, so what I am asking is if I issue a REINDEX on this table, will it create the proper indexes again? Please advise. Thanks in advance. Mario Behring Never miss an email again! Yahoo! Toolbar alerts you the instant new Mail arrives. http://tools.search.yahoo.com/toolbar/features/mail/
Re: [SQL] Droping indexes
Hi Tomas, Thank you. Please help me here.I am not a database guy...how do I use this CLUSTER command and what does it do? Please keep in mind that I do not have disk space left. Also, please check the table I am talking about below: opennms=# \d events Tabela "public.events" Colunm |Type | Modifyers -+-+--- eventid | integer | not null eventuei| character varying(256) | not null nodeid | integer | eventtime | timestamp without time zone | not null eventhost | character varying(256) | eventsource | character varying(128) | not null ipaddr | character varying(16) | eventdpname | character varying(12) | not null eventsnmphost | character varying(256) | serviceid | integer | eventsnmp | character varying(256) | eventparms | text| eventcreatetime | timestamp without time zone | not null eventdescr | character varying(4000) | eventloggroup | character varying(32) | eventlogmsg | character varying(256) | eventseverity | integer | not null eventpathoutage | character varying(1024) | eventcorrelation| character varying(1024) | eventsuppressedcount| integer | eventoperinstruct | character varying(1024) | eventautoaction | character varying(256) | eventoperaction | character varying(256) | eventoperactionmenutext | character varying(64) | eventnotification | character varying(128) | eventtticket| character varying(128) | eventtticketstate | integer | eventforward| character varying(256) | eventmouseovertext | character varying(64) | eventlog| character(1)| not null eventdisplay| character(1)| not null eventackuser| character varying(256) | ndices:ktime| timestamp without time zone | "pk_eventid"primary key, btree (eventid) "events_acktime_idx" btree (eventacktime) "events_ackuser_idx" btree (eventackuser) "events_display_idx" btree (eventdisplay) "events_ipaddr_idx" btree (ipaddr) "events_log_idx" btree (eventlog) "events_nodeid_idx" btree (nodeid) "events_serviceid_idx" btree (serviceid) "events_severity_idx" btree (eventseverity) "events_time_idx" btree (eventtime) "events_uei_idx" btree (eventuei) Restrictions of foreing key: "fk_nodeid6" FOREIGN KEY (nodeid) REFERENCES node(nodeid) ON DELETE CASCADE Thanks. Mario Behring - Original Message From: Tomas Vondra <[EMAIL PROTECTED]> To: Mario Behring <[EMAIL PROTECTED]> Sent: Tuesday, January 16, 2007 12:29:59 PM Subject: Re: [SQL] Droping indexes > Hi all, > > Please, if I drop all indexes from a table, can I recreate them after > performing a vacuum full at this table? I mean, I do not know details > about the indexes, so what I am asking is if I issue a REINDEX on this > table, will it create the proper indexes again? > > Please advise. No, if you drop them they're lost and you'll have to recreate them using CREATE INDEX. Being in your situation, I'd use CLUSTER instead of VACUUM + REINDEX, as it basically does the same thing, plus it has several advantages related to performance. Just think carefully which index would you use to cluster the table - the index associated with the primary key is generally a good choice. Tomas PS: Don't forget to analyze the table after that! Need Mail bonding? Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users. http://answers.yahoo.com/dir/?link=list&sid=396546091
Re: [SQL] Droping indexes
Mario Behring wrote: > Hi Tomas, > > Thank you. > > Please help me here.I am not a database guy...how do I use this > CLUSTER command and what does it do? Please keep in mind that I do not have > disk space left. If the disk is full you cannot use CLUSTER anyway. Suggestion: make a note of the indexes that exist. Drop them. Do a VACUUM FULL of the table. Create the indexes again. After that's done: 1. read the CLUSTER documentation, as it may help you in the future 2. consider restructuring the table so that redundant information is kept only on one place (for example, the eventackuser could probably be put on a separate table and on this one store just an integer ID). This will make the table and the index on that column a lot smaller. 3. add more disks to your installation 4. research a more effective VACUUM policy -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Droping indexes
Hi Alvaro, Thank you for your advise..I was thinking about doing exactly that, I wasn't sure on how to do it though, meaning, considering the info below, how should I use the CREATE INDEX command to create these indexes?? opennms=# \d events Tabela "public.events" Colunm |Type | Modifyers -+-+--- eventid | integer | not null eventuei| character varying(256) | not null nodeid | integer | eventtime | timestamp without time zone | not null eventhost | character varying(256) | eventsource | character varying(128) | not null ipaddr | character varying(16) | eventdpname | character varying(12) | not null eventsnmphost | character varying(256) | serviceid | integer | eventsnmp | character varying(256) | eventparms | text| eventcreatetime | timestamp without time zone | not null eventdescr | character varying(4000) | eventloggroup | character varying(32) | eventlogmsg | character varying(256) | eventseverity | integer | not null eventpathoutage | character varying(1024) | eventcorrelation| character varying(1024) | eventsuppressedcount| integer | eventoperinstruct | character varying(1024) | eventautoaction | character varying(256) | eventoperaction | character varying(256) | eventoperactionmenutext | character varying(64) | eventnotification | character varying(128) | eventtticket| character varying(128) | eventtticketstate | integer | eventforward| character varying(256) | eventmouseovertext | character varying(64) | eventlog| character(1)| not null eventdisplay| character(1)| not null eventackuser| character varying(256) | ndices:ktime| timestamp without time zone | "pk_eventid"primary key, btree (eventid) "events_acktime_idx" btree (eventacktime) "events_ackuser_idx" btree (eventackuser) "events_display_idx" btree (eventdisplay) "events_ipaddr_idx" btree (ipaddr) "events_log_idx" btree (eventlog) "events_nodeid_idx" btree (nodeid) "events_serviceid_idx" btree (serviceid) "events_severity_idx" btree (eventseverity) "events_time_idx" btree (eventtime) "events_uei_idx" btree (eventuei) Restrictions of foreing key: "fk_nodeid6" FOREIGN KEY (nodeid) REFERENCES node(nodeid) ON DELETE CASCADE Thank you. Best regards, Mario Behring - Original Message From: Alvaro Herrera <[EMAIL PROTECTED]> To: Mario Behring <[EMAIL PROTECTED]> Cc: Tomas Vondra <[EMAIL PROTECTED]>; pgsql-sql@postgresql.org Sent: Tuesday, January 16, 2007 1:08:55 PM Subject: Re: [SQL] Droping indexes Mario Behring wrote: > Hi Tomas, > > Thank you. > > Please help me here.I am not a database guy...how do I use this > CLUSTER command and what does it do? Please keep in mind that I do not have > disk space left. If the disk is full you cannot use CLUSTER anyway. Suggestion: make a note of the indexes that exist. Drop them. Do a VACUUM FULL of the table. Create the indexes again. After that's done: 1. read the CLUSTER documentation, as it may help you in the future 2. consider restructuring the table so that redundant information is kept only on one place (for example, the eventackuser could probably be put on a separate table and on this one store just an integer ID). This will make the table and the index on that column a lot smaller. 3. add more disks to your installation 4. research a more effective VACUUM policy -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Want to start your own business? Learn how on Yahoo! Small Business. http://smallbusiness.yahoo.com/r-index
Re: [SQL] Droping indexes
Mario Behring wrote: Hi Alvaro, Thank you for your advise..I was thinking about doing exactly that, I wasn't sure on how to do it though, meaning, considering the info below, how should I use the CREATE INDEX command to create these indexes?? Have you read the documentation? http://www.postgresql.org/docs/8.2/interactive/sql-createindex.html -- erik jones <[EMAIL PROTECTED]> software development emma(r) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Removing CONTEXT message
Hi all, I know I can remove the CONTEXT messages from the file log with: SET log_error_verbosity = terse Is there any possibility to remove it from the client pgAdmin Query messages as well ? TIA, Sabin
Re: [SQL] Droping indexes
On Tue, 2007-01-16 at 07:51, Mario Behring wrote: > Hi all, > > Please, if I drop all indexes from a table, can I recreate them after > performing a vacuum full at this table? I mean, I do not know details > about the indexes, so what I am asking is if I issue a REINDEX on this > table, will it create the proper indexes again? The bad news: No. Once you drop an index it's just gone. More bad news: If it's a primary key index, you probably can't drop it either. Very good news: PostgreSQL stores an index definition for easy index recreation. Let's say your tablename is "mytable" This query will get you all the index creation statements for mytable: select indexdef from pg_indexes where tablename='mytable'; CREATE UNIQUE INDEX mytable_pkey ON mytable USING btree (id) CREATE UNIQUE INDEX mytable_i1_key ON mytable USING btree (i1) CREATE INDEX mytable_i1_i2 ON mytable USING btree (i1, i2) Note that if you're using a schema other than the public one, you might need to add tablespace='tablespacename' to the where clause. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Removing CONTEXT message
"Sabin Coanda" <[EMAIL PROTECTED]> writes: > I know I can remove the CONTEXT messages from the file log with: > SET log_error_verbosity = terse > Is there any possibility to remove it from the client pgAdmin Query > messages as well ? This is the wrong place to ask; try the pgAdmin support lists. MHO: if they don't have a way to adjust the verbosity of their error output, they definitely should. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Droping indexes
At 10:42 AM 1/16/07, Mario Behring wrote: Thank you for your advise..I was thinking about doing exactly that, I wasn't sure on how to do it though, meaning, considering the info below, how should I use the CREATE INDEX command to create these indexes?? This might provide a clue: pg_dump -t events | grep ^CREATE ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Droping indexes
At 11:27 AM 1/16/07, Frank Bax wrote: At 10:42 AM 1/16/07, Mario Behring wrote: Thank you for your advise..I was thinking about doing exactly that, I wasn't sure on how to do it though, meaning, considering the info below, how should I use the CREATE INDEX command to create these indexes?? This might provide a clue: pg_dump -t events | grep ^CREATE Sorry, perhaps this would be better pg_dump -s -t events | grep ^CREATE ---(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] Removing CONTEXT message
Hi Tom, In fact, I found setting the configuration parameter 'client_min_messages', I get the expected results according to the documentation, and it works equivalent to 'log_min_messages ' that controls the messages of the log file. I'd just wonder an equivalent parameter as 'log_error_verbosity', but for client messages, not log file. Is it something available ? TIA, Sabin "Tom Lane" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > "Sabin Coanda" <[EMAIL PROTECTED]> writes: >> I know I can remove the CONTEXT messages from the file log with: >> SET log_error_verbosity = terse >> Is there any possibility to remove it from the client pgAdmin Query >> messages as well ? > > This is the wrong place to ask; try the pgAdmin support lists. > > MHO: if they don't have a way to adjust the verbosity of their error > output, > they definitely should. > > regards, tom lane > > ---(end of broadcast)--- > TIP 7: You can help support the PostgreSQL project by donating at > >http://www.postgresql.org/about/donate > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Droping indexes RESOLVED
Hi all, Thank you very much for your help. I've found a create.sql script among the millions of files from openNMS installation that has all SQL statements for creating all tables, constraints, indexes and such. :-) I've just executed a drop table at the guilty table (32GB in size...), recreated it along with all constraints and indexesand everything is fine now !! Thank a lot for your support. All the best, Mario Behring - Original Message From: Frank Bax <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Sent: Tuesday, January 16, 2007 2:30:31 PM Subject: Re: [SQL] Droping indexes At 11:27 AM 1/16/07, Frank Bax wrote: >At 10:42 AM 1/16/07, Mario Behring wrote: >>Thank you for your advise..I was thinking about doing exactly >>that, I wasn't sure on how to do it though, meaning, considering the info >>below, how should I use the CREATE INDEX command to create these indexes?? > > >This might provide a clue: > >pg_dump -t events | grep ^CREATE Sorry, perhaps this would be better pg_dump -s -t events | grep ^CREATE ---(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 8:00? 8:25? 8:40? Find a flick in no time with the Yahoo! Search movie showtime shortcut. http://tools.search.yahoo.com/shortcuts/#news
Re: [SQL] Removing CONTEXT message
"Sabin Coanda" <[EMAIL PROTECTED]> writes: > I'd just wonder an equivalent parameter as 'log_error_verbosity', but for > client messages, not log file. Is it something available ? No, because it's the client-side code's responsibility to decide how much to display. In psql there's "\set VERBOSITY", in pgAdmin I dunno but you're asking the wrong person... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
