pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Drop indexes inside transaction? 3+ messages / 2 participants [nested] [flat]
* Drop indexes inside transaction? @ 2004-02-06 03:48 Steve Lane <slane@moyergroup.com> 0 siblings, 1 reply; 3+ messages in thread From: Steve Lane @ 2004-02-06 03:48 UTC (permalink / raw) To: pgsql-admin Hello all: Question here about how best to optimize a large data load. Data load is ad hoc and so needs to be able to run during production hours. We have a piece of software written in a desktop RAD environment (FileMaker Pro). Our users are using this tool to generate data that we need to get into postgresql. We're sending it through a middleware layer written in PHP. A single data load will generate from 10K-100K rows. Initially we did everything procedurally through PHP. 100K inserts, each one called through several layers of PHP abstraction. Bad idea. Current idea is to have PHP dump the data to a file, and suck the whole file in at once somehow. So far, so good: PHP can create the file in 6 minutes for 100K rows. That's actually acceptable. Now we want to use COPY to bring the data in. The target table has 6 indexes. Without indexes, naturally, we can load 80K rows in 2 seconds. With indexes, 46 seconds. (oddly, ONE index contributes 40+ seconds of that, yet they're all similar, single-column indexes. Anyway, that's another story). Normally, in a batch-like environment, I'd feel free to drop the indexes, load, reindex. That's perfectly fast. But the environment needs to be live, and those indexes are vital to a reporting engine that can be hit at any time. So we can't just drop them, even briefly. So I hit on the idea of doing the same thing, but inside a transaction. In theory that should affect no one else. To my delight, the transaction drop-copy-reindex ran in 7 seconds. I guess I'm just wondering how that's possible. I hate to sound like a superstitious goof, but it sounds to good to be true. At best, I figured to pay the whole penalty at the time of COMMIT -- that it would be fast up to that point, and then of course need to do exactly the same work as the transactionless version, as far as reconciling indexes or whatever the more accurately technical term is. So: is this too good to be true? Or is this actually a good way to do this? Any other observations on the whole process? Is there a better or different approach, or other things we should consider? Any and all thoughts are welcome. -- sgl ======================================================= Steve Lane Vice President The Moyer Group 14 North Peoria St Suite 2H Chicago, IL 60607 Voice: (312) 433-2421 Email: slane@moyergroup.com Fax: (312) 850-3930 Web: http://www.moyergroup.com ======================================================= ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Drop indexes inside transaction? @ 2004-02-06 06:23 Tom Lane <tgl@sss.pgh.pa.us> parent: Steve Lane <slane@moyergroup.com> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2004-02-06 06:23 UTC (permalink / raw) To: Steve Lane <slane@moyergroup.com>; +Cc: pgsql-admin Steve Lane <slane@moyergroup.com> writes: > Now we want to use COPY to bring the data in. The target table has 6 > indexes. Without indexes, naturally, we can load 80K rows in 2 seconds. With > indexes, 46 seconds. (oddly, ONE index contributes 40+ seconds of that, yet > they're all similar, single-column indexes. Anyway, that's another story). No, I'd say that's the key part of the story. Details? > So I hit on the idea of doing the same thing, but inside a transaction. In > theory that should affect no one else. ... other than locking them out of the table while the transaction runs. That doesn't sound like what you want to do. In any case, reindexing the table will get slower and slower as the pre-existing data in the table expands. regards, tom lane ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Drop indexes inside transaction? @ 2004-02-06 06:54 Steve Lane <slane@moyergroup.com> parent: Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 3+ messages in thread From: Steve Lane @ 2004-02-06 06:54 UTC (permalink / raw) To: pgsql-admin On 2/6/04 12:23 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > Steve Lane <slane@moyergroup.com> writes: >> Now we want to use COPY to bring the data in. The target table has 6 >> indexes. Without indexes, naturally, we can load 80K rows in 2 seconds. With >> indexes, 46 seconds. (oddly, ONE index contributes 40+ seconds of that, yet >> they're all similar, single-column indexes. Anyway, that's another story). > > No, I'd say that's the key part of the story. Details? I have another post out there called "index re-creation speed" that delves into this. Here's the table: Table "test_responses_2" Attribute | Type | Modifier -------------------+-------------------+---------- id_response | integer | id_code | integer | id_survey | integer | id_topic | integer | id_item | integer | id_administration | integer | response_int | smallint | response_txt | character varying | rec_handle | character varying | Indices: id_administration_test_key, id_code_test_key, id_item_test_key, id_response_test_key, id_survey_test_key, id_topic_test_key Id_item_test_key is the one that drives up the speed of the COPY dramatically. 65 distinct values in that column for the given data set. > >> So I hit on the idea of doing the same thing, but inside a transaction. In >> theory that should affect no one else. > > ... other than locking them out of the table while the transaction runs. Ha! I knew that that tasty-looking lunch had to have a price tag. > That doesn't sound like what you want to do. In any case, reindexing > the table will get slower and slower as the pre-existing data in the > table expands. Yes, I've been running some tests and it stops being very acceptable around 3-5M rows. So am I more or less out of luck? The issue, again, is how to insert at most 80-100K rows into a running system, at a time determined by users, into a fairly heavily-indexed table. I thought of dropping the indexes and deferring index recreation -- maybe even having a very frequent cron job rebuild the indexes. All that buys me is being able to return control to the initiating user quickly. It still has the issue of expanding reindex time and, I'm guessing, the issue of locking out other users as well. Am I simply asking too much of my tools here? -- sgl ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-02-06 06:54 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2004-02-06 03:48 Drop indexes inside transaction? Steve Lane <slane@moyergroup.com> 2004-02-06 06:23 ` Tom Lane <tgl@sss.pgh.pa.us> 2004-02-06 06:54 ` Steve Lane <slane@moyergroup.com>
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