pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
best strategy doing a large copy and using indexes 2+ messages / 2 participants [nested] [flat]
* best strategy doing a large copy and using indexes @ 2000-01-11 15:21 Dirk Lutzebaeck <lutzeb@aeccom.com> 2000-01-11 16:14 ` Re: [SQL] best strategy doing a large copy and using indexes Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 2+ messages in thread From: Dirk Lutzebaeck @ 2000-01-11 15:21 UTC (permalink / raw) To: pgsql-sql@hub.org I have a table with 7 individual indexes (it models an object store with different types) and need to copy large data lists into this table regularly. I'm using COPY for it but it is unacceptable slow (4min for 1000 rows on a Linux/PII-400). I'm anticipating more than 20000 rows to copy. What combination of the following options is the best strategy to make this more efficient? a) drop indexes before COPY and recreate them after COPY b) put COPY in a transaction c) do VACUUM before (or after?) d) do VACUUM ANALYZE before (or after?) e) COPY to temp table and then make an INSERT SELECT FROM temp table f) anything else I'm using -f for the backend already. Dirk ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] best strategy doing a large copy and using indexes 2000-01-11 15:21 best strategy doing a large copy and using indexes Dirk Lutzebaeck <lutzeb@aeccom.com> @ 2000-01-11 16:14 ` Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2000-01-11 16:14 UTC (permalink / raw) To: Dirk Lutzebaeck <lutzeb@aeccom.com>; +Cc: pgsql-sql@hub.org Dirk Lutzebaeck <lutzeb@aeccom.com> writes: > I have a table with 7 individual indexes (it models an object store > with different types) and need to copy large data lists into this > table regularly. I'm using COPY for it but it is unacceptable slow > (4min for 1000 rows on a Linux/PII-400). I'm anticipating more than > 20000 rows to copy. What combination of the following options is the > best strategy to make this more efficient? > a) drop indexes before COPY and recreate them after COPY That should certainly help a great deal (unless the 20000 rows are a small number compared to what's already in the table --- in that case, the cost of reindexing the existing rows might outweigh the efficiency of indexing the new rows "wholesale" instead of "retail"). Also, if you have any triggers or anything like that, you might consider turning them off for the duration of the copy. > b) put COPY in a transaction Won't do anything --- COPY is only one transaction command anyway. > c) do VACUUM before (or after?) > d) do VACUUM ANALYZE before (or after?) These wouldn't directly affect the speed of COPY, afaik. You should consider doing a VACUUM ANALYZE after adding any large amount of data to a table, so that the optimizer has reasonably up-to-date info about the size of the table. But that's only going to affect subsequent queries, not the COPY itself. > e) COPY to temp table and then make an INSERT SELECT FROM temp table That would be more total elapsed time, but if your main concern is to minimize the downtime of your primary table, I suppose there could be reason to do it that way. regards, tom lane ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-01-11 16:14 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2000-01-11 15:21 best strategy doing a large copy and using indexes Dirk Lutzebaeck <lutzeb@aeccom.com> 2000-01-11 16:14 ` Tom Lane <tgl@sss.pgh.pa.us>
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