pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (4+ messages)

Message-ID: <013401c3f653$14f95970$c300000a@caliente>
Permalink:  ../013401c3f653$14f95970$c300000a@caliente/
Also on:    postgresql.org/message-id/013401c3f653$14f95970$c300000a@caliente

 · 

reply

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