From: Eric Jain <Eric.Jain@isb-sib.ch>
To: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: UPDATE with subquery too slow
Date: Wed, 18 Feb 2004 20:11:58 +0100
Message-ID: <013401c3f653$14f95970$c300000a@caliente> (raw)
References: <00ed01c3f552$ec054e80$c300000a@caliente>
> I can't get the following statement to complete with reasonable time.
Upgraded to 7.4.1, and realized that NOT IN is far more efficient than
IN, EXISTS or NOT EXISTS, at least for the amount and distribution of
data that I have. Here are some numbers from before and after performing
the problematic clean up operation:
| Before | After
------------------------+-----------+-----------
COUNT(*) | 6'104'075 | 6'104'075
COUNT(session) | 5'945'272 | 3'640'659
COUNT(DISTINCT session) | 2'865'570 | 560'957
The following query completes within less than three hours on a machine
with a high load, versa many many hours for any of the alternatives:
UPDATE requests
SET session = NULL
WHERE session NOT IN
(
SELECT r.session
FROM requests r
WHERE r.session IS NOT NULL
GROUP BY r.session
HAVING COUNT(*) > 1
);
Note that in order to correctly reverse an IN subquery, IS NOT NULL
needs to be added.
Interestingly, the query planner believes that using EXISTS would be
more efficient than NOT IN, and IN only slightly less efficient; I
assume the query planner is not able to accurately estimate the number
of rows returned by the subquery.
EXISTS 351'511
NOT IN 376'577
IN 386'780
LEFT JOIN 18'263'826
NOT EXISTS 7'241'815'330
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: Eric.Jain@isb-sib.ch
Subject: Re: UPDATE with subquery too slow
In-Reply-To: <013401c3f653$14f95970$c300000a@caliente>
* 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