473,238 Members | 1,590 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,238 software developers and data experts.

REINDEX slow?

Howdy all!

Quick question regarding REINDEX. I have a large table with 1077455 rows.
I have an index on the table with a WHERE clause that limits the rows to
around 10-50. When I REINDEX this index, it takes on the order of a minute
to do the reindex. Question: Does a REINDEX use the old version to build
the new version, or is it the semantic equivalent to "drop index; build
new index". If the first, any thoughts on why it is taking so long to
reindex? If the second, any particular reason not to use the current
(though possibly bloated) index to reduce work (essentially, all we are
doing is recompacting the same info as the original index with a
rebalance)?

Regards!
Ed

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #1
5 3380
Edmund Dengler <ed*****@eSentire.com> writes:
Question: Does a REINDEX use the old version to build
the new version, or is it the semantic equivalent to "drop index; build
new index".
The latter.
If the second, any particular reason


REINDEX is customarily used to recover from a corrupted-index situation.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #2
Edmund Dengler <ed*****@eSentire.com> writes:
Searching the web, I find lots of references to index bloat, as
well as recent discussions concerning index page recovery (such
items as reclaiming empty B+-tree pages and such). What is the
current state re bloat?


You tell me --- are you still seeing bloat problems with 7.4?
If so, can you provide more details about the index schema and
the table's update patterns?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3
Searching the web, I find lots of references to index bloat, as
well as recent discussions concerning index page recovery (such
items as reclaiming empty B+-tree pages and such). What is the
current state re bloat?

Basically, I just re-indexed a whole bunch of databases on one system, and
now queries are much faster (though the REINDEX itself took about an hour
and a half, and this was a specific index * 15 tables * 20 databases). If
bloat is still an issue, and REINDEX is still a recommended way to improve
performce (which at least a number of articles was suggesting, is this
still the case?), then is it possible to have a REINDEX version that can
utilize the old index if the index is not corrupted? Or would this be too
much work? I would prefer to keep the amounts of locks happening on tables
to a minimum.

Alternatively, if I created a second index, and then dropped the first,
would this be faster (though I would suppose that an ANALYZE would need to
be done to recognize the utility of the new index, thereby negating any
speed improvements)?

Regards!
Ed

On Sun, 11 Apr 2004, Tom Lane wrote:
Edmund Dengler <ed*****@eSentire.com> writes:
Question: Does a REINDEX use the old version to build
the new version, or is it the semantic equivalent to "drop index; build
new index".


The latter.
If the second, any particular reason


REINDEX is customarily used to recover from a corrupted-index situation.

regards, tom lane


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #4

Edmund Dengler <ed*****@eSentire.com> writes:
Alternatively, if I created a second index, and then dropped the first,
would this be faster (though I would suppose that an ANALYZE would need to
be done to recognize the utility of the new index, thereby negating any
speed improvements)?


Currently analyze doesn't check what indexes exist, it analyzes all the
columns of the table anyways. However I think this won't be true in 7.5 when
there are expression indexes, and there has been talk of changing this in
future to take into account indexes and foreign keys.

I don't think it would be any faster but it might avoid downtime. Reindex
seems to block any use of the index until the reindex completes, while doing
this two-step would avoid blocking queries. I haven't tried it myself though
so I'm not sure what gotchas might arise.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #5
>>>>> "TL" == Tom Lane <tg*@sss.pgh.pa.us> writes:

TL> Edmund Dengler <ed*****@eSentire.com> writes:
Searching the web, I find lots of references to index bloat, as
well as recent discussions concerning index page recovery (such
items as reclaiming empty B+-tree pages and such). What is the
current state re bloat?


TL> You tell me --- are you still seeing bloat problems with 7.4?
TL> If so, can you provide more details about the index schema and
TL> the table's update patterns?

I had *awful* bloat with 7.2. When I upgraded to 7.4, I noticed less
of it, but it was still there. Then I discovered two programs that
were sleeping for long times inside of a transaction. Restructuring
those to open transactions as needed fixed it all.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: kh***@kciLink.com Rockville, MD +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Matt Liverance | last post by:
I REALLY dont want to switch to oracle :( but I cant get these tables working any faster. I've got 2 dedicated servers, each with a slave, all run 32gig 15k rpm raid 5 on u320 perc raid...
0
by: robberjohn | last post by:
Hello all, I have a SQL 2000 Standard SP3 install. I was running low on free space so added a secondary data file on a separate hard drive. I did this for each of two databases. Since then, and...
2
by: G.W. Lucas | last post by:
I apologize if this is a RTFM question, but I have not been able to find a definitive answer elsewhere. Does a "REINDEX TABLE" lock the table while it is working? Can applications write data to...
1
by: Sally Sally | last post by:
I just started a REINDEX on a non-system table because I was getting "failed to re-find parent key" while analyzing. I realized a little too late that the docs said the pg server should have been...
5
by: Clodoaldo Pinto Neto | last post by:
I suspect there is something wrong because it takes 73s to delete a single line from a table whith 140 lines. So I tried to reindex the database: bash-2.05b$ postgres -P -O -D /var/lib/pgsql/data...
1
by: Ulrich Wisser | last post by:
Hello, the maintainance chapter in the docs gave me the impression that a reindex of some tables would be a good idea. Last night I took the database down, started up in single user mode and...
3
by: Raziq Shekha | last post by:
Hi Folks, SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran the command : dbcc dbreindex ('tablename') go for all tables in the database. Then I compared the dbcc...
1
by: paul | last post by:
Hi, i have several tables in production whose contents are renewd totally in 1 week. So everyd day we delete ~15% records and then insert 15% new. And after a few days, the performances drops : ...
0
by: shubhangi | last post by:
Hi, what are the unknown bad effects of executing "dbcc reindex" statement on a table. Does executing it affects the performance of server. thanks Shubhangi
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.