pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDroping indexes
8+ messages / 5 participants
[nested] [flat]
* Droping indexes
@ 2007-01-16 13:51 Mario Behring <mariobehring@yahoo.com>
2007-01-16 16:24 ` Re: Droping indexes Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 8+ messages in thread
From: Mario Behring @ 2007-01-16 13:51 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Droping indexes
2007-01-16 13:51 Droping indexes Mario Behring <mariobehring@yahoo.com>
@ 2007-01-16 16:24 ` Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 8+ messages in thread
From: Scott Marlowe @ 2007-01-16 16:24 UTC (permalink / raw)
To: Mario Behring <mariobehring@yahoo.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Droping indexes
@ 2007-01-16 14:43 Mario Behring <mariobehring@yahoo.com>
2007-01-16 15:08 ` Re: Droping indexes Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 8+ messages in thread
From: Mario Behring @ 2007-01-16 14:43 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; +Cc: pgsql-sql
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 <tv@fuzzy.cz>
To: Mario Behring <mariobehring@yahoo.com>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Droping indexes
2007-01-16 14:43 Re: Droping indexes Mario Behring <mariobehring@yahoo.com>
@ 2007-01-16 15:08 ` Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 8+ messages in thread
From: Alvaro Herrera @ 2007-01-16 15:08 UTC (permalink / raw)
To: Mario Behring <mariobehring@yahoo.com>; +Cc: Tomas Vondra <tv@fuzzy.cz>; pgsql-sql
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 Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Droping indexes
@ 2007-01-16 15:42 Mario Behring <mariobehring@yahoo.com>
2007-01-16 15:48 ` Re: Droping indexes Erik Jones <erik@myemma.com>
2007-01-16 16:27 ` Re: Droping indexes Frank Bax <fbax@sympatico.ca>
2007-01-16 16:30 ` Re: Droping indexes Frank Bax <fbax@sympatico.ca>
0 siblings, 3 replies; 8+ messages in thread
From: Mario Behring @ 2007-01-16 15:42 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: pgsql-sql
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 <alvherre@commandprompt.com>
To: Mario Behring <mariobehring@yahoo.com>
Cc: Tomas Vondra <tv@fuzzy.cz>; 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 Herrera http://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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Droping indexes
2007-01-16 15:42 Re: Droping indexes Mario Behring <mariobehring@yahoo.com>
@ 2007-01-16 15:48 ` Erik Jones <erik@myemma.com>
2 siblings, 0 replies; 8+ messages in thread
From: Erik Jones @ 2007-01-16 15:48 UTC (permalink / raw)
To: Mario Behring <mariobehring@yahoo.com>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; pgsql-sql
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 <erik@myemma.com>
software development
emma(r)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Droping indexes
2007-01-16 15:42 Re: Droping indexes Mario Behring <mariobehring@yahoo.com>
@ 2007-01-16 16:27 ` Frank Bax <fbax@sympatico.ca>
2 siblings, 0 replies; 8+ messages in thread
From: Frank Bax @ 2007-01-16 16:27 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Droping indexes
2007-01-16 15:42 Re: Droping indexes Mario Behring <mariobehring@yahoo.com>
@ 2007-01-16 16:30 ` Frank Bax <fbax@sympatico.ca>
2 siblings, 0 replies; 8+ messages in thread
From: Frank Bax @ 2007-01-16 16:30 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-01-16 16:30 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-16 13:51 Droping indexes Mario Behring <mariobehring@yahoo.com>
2007-01-16 16:24 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-01-16 14:43 Re: Droping indexes Mario Behring <mariobehring@yahoo.com>
2007-01-16 15:08 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-01-16 15:42 Re: Droping indexes Mario Behring <mariobehring@yahoo.com>
2007-01-16 15:48 ` Erik Jones <erik@myemma.com>
2007-01-16 16:27 ` Frank Bax <fbax@sympatico.ca>
2007-01-16 16:30 ` Frank Bax <fbax@sympatico.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox