Re: update from select - Mailing list pgsql-sql

2007/10/29, dev@kbsolutions.ch <dev@kbsolutions.ch>:
>
> 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 DISTINCT
>         detail_dvd.asin,
>         detail_dvd.picturemedium,
>         detail_dvd.title,
>         detail_dvd.releasedate
>         FROM detail_dvd
>         WHERE releasedate IS 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 IS NOT 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 don't need for this query... Now I have 3 left! And it
> takes around 2 Minutes! But that's 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
> ***********************************************************
>

try to up statististics on table detail_dvd.release_date. Maybe there
is other problem. The casting from (probably) date to text in
releasedate column. Is it correct?

what is original type for releasedate column?

Pavel
>
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>