From: Віталій Тимчишин <tivv00@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Delete performance again
Date: Thu, 9 Oct 2008 15:54:38 +0300
Message-ID: <331e40660810090554n4633de19gc2ee5fd4aa85457c@mail.gmail.com> (raw)
In-Reply-To: <CA896D7906BF224F8A6D74A1B7E54AB3039E8BEF@JENMAIL01.ad.intershop.net>
References: <331e40660810020242u2f0f5de2qe146312e448a7df4@mail.gmail.com>
<23315.1222949660@sss.pgh.pa.us>
<331e40660810020821w4c4f212br3943df435589f731@mail.gmail.com>
<CA896D7906BF224F8A6D74A1B7E54AB3039E8BEF@JENMAIL01.ad.intershop.net>
OK, I did try you proposal and correlated subselect.
I have a database ~900000 companies.
First try was to remove randomly selected 1000 companies
Uncorrelated subselect: 65899ms
Correlated subselect: 97467ms
using: 9605ms
my way: 104979ms. (without constraints recreate)
My is the worst because it is oriented on massive delete.
So I thought USING would perform better, so I did try 10000 companies
my way: 190527ms. (without constraints recreate)
using: 694144ms
I was a little shocked, but I did check plans and found out that it did
switch from Nested Loop to Hash Join.
I did disable Hash Join, it not show Merge Join. This was also disabled....
and I've got 747253ms.
Then I've tried combinations: Without hash join it was the best result of
402629ms, without merge join it was 1096116ms.
My conclusion: Until optimizer would take into account additional actions
needed (like constraints check/cascade deletes/triggers), it can not make
good plan.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tivv00@gmail.com
Subject: Re: Delete performance again
In-Reply-To: <331e40660810090554n4633de19gc2ee5fd4aa85457c@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox