From: <dev@kbsolutions.ch>
To: <pgsql-sql@postgresql.org>
References: <0d5e01c81a0c$b0f67250$1601a8c0@kbsc1>
	<20071029104529.GB16653@a-kretschmer.de>
Subject: Re: update from select
Date: Mon, 29 Oct 2007 16:28:15 +0100
Message-ID: <0f0501c81a40$538ce1b0$1601a8c0@kbsc1>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <20071029104529.GB16653@a-kretschmer.de>
thread-index: AcgaGJTqqKNQCpRCRdm/tVaQzkAA3wAASJ9Q


Yes, both have varchar(50).

Query:

UPDATE owner SET picturemedium =3D dvds.picturemedium, title =3D =
dvds.title,
titleOrder =3D dvds.title, releasedate =3D CAST(dvds.releasedate AS =
date) FROM
(
	SELECT DISTINCT
	detail_dvd.asin,
	detail_dvd.picturemedium,
	detail_dvd.title,
	detail_dvd.releasedate
	FROM detail_dvd=20
	WHERE releasedate IS NOT NULL AND releasedate <> '' AND
(length(releasedate) =3D 10 OR length(releasedate) =3D 23)
)=20
AS dvds WHERE owner.asin =3D dvds.asin;

***********************************************************
EXPLAIN ANALYZE:

Hash Join  (cost=3D10827.45..25950.05 rows=3D4906 width=3D1191) (actual
time=3D586.251..2852.691 rows=3D111306 loops=3D1)
"  Hash Cond: ((""owner"".asin)::text =3D (dvds.asin)::text)"
"  ->  Seq Scan on ""owner""  (cost=3D0.00..14148.98 rows=3D230198 =
width=3D101)
(actual time=3D0.050..968.028 rows=3D230198 loops=3D1)"
  ->  Hash  (cost=3D10825.02..10825.02 rows=3D194 width=3D1208) (actual
time=3D584.463..584.463 rows=3D19489 loops=3D1)
        ->  Subquery Scan dvds  (cost=3D10820.66..10825.02 rows=3D194
width=3D1208) (actual time=3D435.005..545.213 rows=3D19489 loops=3D1)
              ->  Unique  (cost=3D10820.66..10823.08 rows=3D194 =
width=3D110)
(actual time=3D435.002..520.725 rows=3D19489 loops=3D1)
                    ->  Sort  (cost=3D10820.66..10821.14 rows=3D194 =
width=3D110)
(actual time=3D434.998..491.487 rows=3D19489 loops=3D1)
                          Sort Key: asin, picturemedium, title, =
releasedate
                          ->  Seq Scan on detail_dvd  =
(cost=3D0.00..10813.29
rows=3D194 width=3D110) (actual time=3D0.042..166.493 rows=3D19489 =
loops=3D1)
                                Filter: ((releasedate IS NOT NULL) AND
((releasedate)::text <> ''::text) AND ((length((releasedate)::text) =3D =
10) OR
(length((releasedate)::text) =3D 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 don=92t need for this query... Now I have 3 left! And =
it
takes around 2 Minutes! But that=92s 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 =3D detail_dvd.picturemedium, title =3D
detail_dvd.title, titleOrder =3D detail_dvd.title, releasedate =3D
CAST(detail_dvd.releasedate AS date)=20
FROM detail_dvd
WHERE owner.asin =3D detail_dvd.asin=20
AND detail_dvd.releasedate IS NOT NULL=20
AND detail_dvd.releasedate <> ''
AND (length(detail_dvd.releasedate) =3D 10 OR =
length(detail_dvd.releasedate) =3D
23);

But its also to slow:

***********************************************************
EXPLAIN ANALYZE:

Nested Loop  (cost=3D0.00..28175.75 rows=3D2006 width=3D195) (actual
time=3D0.138..127695.132 rows=3D111306 loops=3D1)
  ->  Seq Scan on detail_dvd  (cost=3D0.00..10813.29 rows=3D194 =
width=3D110)
(actual time=3D0.035..615.511 rows=3D19489 loops=3D1)
        Filter: ((releasedate IS NOT NULL) AND ((releasedate)::text <>
''::text) AND ((length((releasedate)::text) =3D 10) OR
(length((releasedate)::text) =3D 23)))
"  ->  Index Scan using ""iidx-owner-asin"" on ""owner""  =
(cost=3D0.00..89.04
rows=3D26 width=3D99) (actual time=3D2.848..6.485 rows=3D6 =
loops=3D19489)"
"        Index Cond: ((""owner"".asin)::text =3D =
(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=FCngliche 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:
>=20
> WHERE table1.column1 =3D temp_table.column1;

table1.column1 and temp_table.column1 have the same type?

>=20
> =20
>=20
> 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
--=20
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