Re: [SPAM] - Performance of UPDATE SET = FROM vs UPDATE SET = (SELECT ...)
От | pbj@cmicdo.com |
---|---|
Тема | Re: [SPAM] - Performance of UPDATE SET = FROM vs UPDATE SET = (SELECT ...) |
Дата | |
Msg-id | 1415047006.57773.YahooMailBasic@web161705.mail.bf1.yahoo.com обсуждение исходный текст |
Ответ на | Re: [SPAM] - Performance of UPDATE SET = FROM vs UPDATE SET = (SELECT ...) (Igor Neyman <ineyman@perceptron.com>) |
Список | pgsql-general |
> > On Mon, 11/3/14, Igor Neyman <ineyman@perceptron.com> wrote: > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] > On Behalf Of pbj@cmicdo.com > Sent: Monday, November 03, 2014 11:34 AM > To: pgsql-general@postgresql.org > Subject: [SPAM] - [GENERAL] Performance of UPDATE SET = FROM > vs UPDATE SET = (SELECT ...) > > Why does the UPDATE SET = FROM choose a more poorly > performing plan than the UPDATE SET = (SELECT ...)? It > seems to me that it is the same join. > > I'm using 9.3.5. > > CREATE TABLE orig > ( > key1 VARCHAR(11) > PRIMARY KEY, > time1 TIME > ); > > INSERT INTO orig (key1, time1) > SELECT > a::TEXT, > (((random()*100)::INT % > 24)::TEXT || ':' || > ((random()*100)::INT % > 60)::TEXT)::TIME FROM generate_series(80000000000, > 80002000000) a; > > CREATE INDEX odx ON orig(key1); > > CREATE TABLE second (LIKE orig); > > INSERT INTO second (key1) > SELECT > (80000000000+(((random()*1000000)::INT) % 1000000))::TEXT > FROM generate_series(1,400000); > > EXPLAIN ANALYZE > UPDATE second SET time1 = orig.time1 > FROM orig > WHERE second.key1 = orig.key1; > [.....] > > UPDATE second SET time1 = NULL; > > EXPLAIN ANALYZE > UPDATE second SET time1 = (SELECT orig.time1 FROM > orig,second > > WHERE orig.key1 = second.key1 > LIMIT 1); > [.....] > > These 2 queries are not the same. > > The first query updates rows in the "second" table with the > orig.time1 values based on key1 column match. > The second query finds first possible match (based on key1 > column) and assigns orig.time1 value from the matched row to > every record in "second" table. > > Regards, > Igor Neyman I see that now. I was trying to reproduce something from work from memory and got tripped up on a sublety of UPDATE ... SELECT. The query I ran at work was like this: EXPLAIN ANALYZE UPDATE second se SET time1 = (SELECT time1 FROM orig WHERE orig.key1 = se.key1); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Update on second se (cost=0.00..3390627.00 rows=400000 width=18) (actual time=18698.795..18698.795 rows=0 loops=1) -> Seq Scan on second se (cost=0.00..3390627.00 rows=400000 width=18) (actual time=7.558..16694.600 rows=400000 loops=1) SubPlan 1 -> Index Scan using odx on orig (cost=0.43..8.45 rows=1 width=8) (actual time=0.033..0.035 rows=1 loops=400000) Index Cond: ((key1)::text = (se.key1)::text) Total runtime: 18698.865 ms (6 rows) This does correctly match and update all of the second table entries. The plan actually runs longer than the UPDATE ... FROM, which squares with a comment the fine manual. Thanks! PJ
В списке pgsql-general по дате отправления: