Re: update from select - Mailing list pgsql-sql
From | |
---|---|
Subject | Re: update from select |
Date | |
Msg-id | 0f0501c81a40$538ce1b0$1601a8c0@kbsc1 Whole thread Raw |
In response to | Re: update from select ("A. Kretschmer" <andreas.kretschmer@schollglas.com>) |
Responses |
Re: update from select
Re: update from select |
List | pgsql-sql |
Yes, both have varchar(50). Query: UPDATE owner SET picturemedium = dvds.picturemedium, title = dvds.title, titleOrder = dvds.title, releasedate = CAST(dvds.releasedate AS date) FROM (SELECT DISTINCTdetail_dvd.asin,detail_dvd.picturemedium,detail_dvd.title,detail_dvd.releasedateFROM detail_dvd WHERE releasedateIS NOT NULL AND releasedate <> '' AND (length(releasedate) = 10 OR length(releasedate) = 23) ) AS dvds WHERE owner.asin = dvds.asin; *********************************************************** EXPLAIN ANALYZE: Hash Join (cost=10827.45..25950.05 rows=4906 width=1191) (actual time=586.251..2852.691 rows=111306 loops=1) " Hash Cond: ((""owner"".asin)::text = (dvds.asin)::text)" " -> Seq Scan on ""owner"" (cost=0.00..14148.98 rows=230198 width=101) (actual time=0.050..968.028 rows=230198 loops=1)" -> Hash (cost=10825.02..10825.02 rows=194 width=1208) (actual time=584.463..584.463 rows=19489 loops=1) -> Subquery Scan dvds (cost=10820.66..10825.02 rows=194 width=1208) (actual time=435.005..545.213 rows=19489 loops=1) -> Unique (cost=10820.66..10823.08 rows=194 width=110) (actual time=435.002..520.725 rows=19489 loops=1) -> Sort (cost=10820.66..10821.14 rows=194 width=110) (actual time=434.998..491.487 rows=19489 loops=1) Sort Key: asin, picturemedium, title, releasedate -> Seq Scan on detail_dvd (cost=0.00..10813.29 rows=194 width=110) (actual time=0.042..166.493 rows=19489 loops=1) Filter: ((releasedate ISNOT NULL) AND ((releasedate)::text <> ''::text) AND ((length((releasedate)::text) = 10) OR (length((releasedate)::text) = 23))) Total runtime: 633548.404 ms *********************************************************** He is not using the index on asin? When I reduce the SELECT to 100, he is using the index! perhaps to many rows are affected? The DISTINCT in the SELECT is not really necessary. It's just for security reasons. And I did check it. It's unique! But as I said before. The SELECT takes around 1 second! I have 13 indices on the UPDATE table. So I did delete the one I dont need for this query... Now I have 3 left! And it takes around 2 Minutes! But thats also a problem, because I need the Indexes again! Is it possible to set the way, Postgres is building the indices? Or is the only way deleting the indeces before UPDATE and then creating them again? I also tried this query (PostgreSQL's extension): UPDATE owner SET picturemedium = detail_dvd.picturemedium, title = detail_dvd.title, titleOrder = detail_dvd.title, releasedate = CAST(detail_dvd.releasedate AS date) FROM detail_dvd WHERE owner.asin = detail_dvd.asin AND detail_dvd.releasedate IS NOT NULL AND detail_dvd.releasedate <> '' AND (length(detail_dvd.releasedate) = 10 OR length(detail_dvd.releasedate) = 23); But its also to slow: *********************************************************** EXPLAIN ANALYZE: Nested Loop (cost=0.00..28175.75 rows=2006 width=195) (actual time=0.138..127695.132 rows=111306 loops=1) -> Seq Scan on detail_dvd (cost=0.00..10813.29 rows=194 width=110) (actual time=0.035..615.511 rows=19489 loops=1) Filter: ((releasedate IS NOT NULL) AND ((releasedate)::text <> ''::text) AND ((length((releasedate)::text) = 10) OR (length((releasedate)::text) = 23))) " -> Index Scan using ""iidx-owner-asin"" on ""owner"" (cost=0.00..89.04 rows=26 width=99) (actual time=2.848..6.485 rows=6 loops=19489)" " Index Cond: ((""owner"".asin)::text = (detail_dvd.asin)::text)" Total runtime: 1039998.325 ms *********************************************************** Thaks for helping!! Bye the way, we are changing our system from MSSQL2000 to Postgres :-)! Regards Reto -----Ursprüngliche Nachricht----- Von: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] Im Auftrag von A. Kretschmer Gesendet: Montag, 29. Oktober 2007 11:45 An: pgsql-sql@postgresql.org Betreff: Re: [SQL] update from select am Mon, dem 29.10.2007, um 10:18:38 +0100 mailte dev@kbsolutions.ch folgendes: > > WHERE table1.column1 = temp_table.column1; table1.column1 and temp_table.column1 have the same type? > > > > The select by it?s own takes around 1 second. The Update is around 120?000 > rows. I got an index on column1. The whole query needs around 16 minutes. Show us the EXPLAIN ANALYSE - result. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate