Search Postgresql Archives

Re: good experience with performance in 8.2 for multi column indexes

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,

your query cant perform well on 8.1
better use a query like

delete from pluext1 using pluext2 where pluext1.plunmbr = pluext2.plunmbr and pluext1.pluexttype = pluext2.pluexttype

it should perform much faster. be sure to use indizes

regards
thomas

Michael Enke schrieb:
Hi lists,
I want to let you take part in my experience of performance boost for delete operations
where more than one column is part of a primary key.

For my setup, in 8.1 a delete query which deletes 200000 entries depending on rows in another table
runs about 7h, in 8.2 (and later) it runs 9s!

I have two tables looking exactly the same, with two columns in the pk, one varchar(20) and one char(1).
Both tables contain the same contents.

Explain produces the following difference:
8.1:

tplinux=> explain delete from pluext1 where (plunmbr,pluexttype) in (select plunmbr,pluexttype from pluext2);
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Hash Join  (cost=24267.10..155886.35 rows=48236 width=6)
   Hash Cond: ("outer".pluexttype = "inner".pluexttype)
   Join Filter: ("outer".plunmbr = "inner".plunmbr)
   ->  Seq Scan on pluext1  (cost=0.00..6945.00 rows=138900 width=46)
   ->  Hash  (cost=24116.37..24116.37 rows=13891 width=40)
         ->  Unique  (cost=23074.62..24116.37 rows=13891 width=40)
               ->  Sort  (cost=23074.62..23421.87 rows=138900 width=40)
                     Sort Key: pluext2.plunmbr, pluext2.pluexttype
-> Seq Scan on pluext2 (cost=0.00..6945.00 rows=138900 width=40)
(9 rows)

(this delete took 7h)

8.2:

tplinux=> explain delete from pluext1 where (plunmbr,pluexttype) in (select plunmbr,pluexttype from pluext2);
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Nested Loop IN Join  (cost=0.00..13362.14 rows=41106 width=6)
   ->  Seq Scan on pluext1  (cost=0.00..6411.25 rows=128225 width=46)
-> Index Scan using pluext2_pk on pluext2 (cost=0.00..0.50 rows=3 width=40) Index Cond: ((pluext1.plunmbr = pluext2.plunmbr) AND (pluext2.pluexttype = pluext1.pluexttype))
(4 rows)

(this delete took 9s)

I could not find an explanation for this in the release notes for 8.2,
I thought it was much earlier that multi column indexes could be used.
Anyway, it saved my life that new version is fast. Many thanks to the developer!

BTW I do not understand the output of the 8.2 explain:
From my understanding it should do a seq scan on pluext2 (and not pluext1)
and an index scan on pluext1 (and not pluext2).

Regards,
Michael


begin:vcard
fn:Thomas Markus
n:Markus;Thomas
org:proventis GmbH
adr:;;Zimmerstr. 79-80;Berlin;Berlin;10117;Germany
email;internet:t.markus@xxxxxxxxxxxxx
tel;work:+49 30 29 36 399 22
x-mozilla-html:FALSE
url:http://www.proventis.net
version:2.1
end:vcard


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux