Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bUDUs-0000Ew-MF for pgsql-performance@arkaria.postgresql.org; Mon, 01 Aug 2016 13:43:10 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bUDUs-0008P9-2F for pgsql-performance@arkaria.postgresql.org; Mon, 01 Aug 2016 13:43:10 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bUDSn-0006IL-L8 for pgsql-performance@postgresql.org; Mon, 01 Aug 2016 13:41:02 +0000 Received: from post.visena.com ([46.226.10.50]) by makus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1bUDSi-0008RU-JX for pgsql-performance@postgresql.org; Mon, 01 Aug 2016 13:41:00 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=visena.com; s=20141101.wh; h=Content-Type:MIME-Version:Subject:In-Reply-To:Message-ID:To:From:Date; bh=2UOF+qy3hSayxE8fP5dA1WTnzk+GKqeQoo9EspevZRw=; b=UftsPxj3PzNrgEJevRswQ6cXGfbNG018Y4CWAMXWCyBTQAiw2xnZGALR89feTgcRuG+tCZiM5PcE/O7YQpKAupAzHeaR+mvYIpIMDVLkDU100SnMKdILUxuTxdZ/x8p+ax8ujreQtsbufIjXoHykj+X0zRWpXvQuBetZZdFa2iQ=; Received: from [10.0.1.10] (helo=tc7-visena.wh.internal.visena.com) by post.visena.com with esmtp (Exim 4.82) (envelope-from ) id 1bUDSa-0003Hq-LQ for pgsql-performance@postgresql.org; Mon, 01 Aug 2016 15:40:54 +0200 Received: from localhost ([127.0.0.1] helo=tc7-visena.wh.internal.visena.com) by tc7-visena.wh.internal.visena.com with esmtp (Exim 4.82) (envelope-from ) id 1bUDSa-0002wT-JH for pgsql-performance@postgresql.org; Mon, 01 Aug 2016 15:40:48 +0200 Date: Mon, 1 Aug 2016 15:40:48 +0200 (CEST) From: Andreas Joseph Krogh To: pgsql-performance@postgresql.org Message-ID: In-Reply-To: Subject: Re: Very poor performance with Nested Loop Anti Join MIME-Version: 1.0 X-Mailer: Visena Mail 2.0.2-SNAPSHOT X-Spam-Score: -1.0 X-Spam-Report: SpamAssasin (score=-1.0, required 5.0 ALL_TRUSTED=-1,HTML_MESSAGE=0.001) X-Pg-Spam-Score: -3.2 (---) Content-Type: multipart/related; boundary="----=_Part_161_624504965.1470058848475" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org ------=_Part_160_1777116725.1470058848475 Content-Type: multipart/related; boundary="----=_Part_161_624504965.1470058848475" ------=_Part_161_624504965.1470058848475 Content-Type: multipart/alternative; boundary="----=_Part_162_912047205.1470058848495" ------=_Part_162_912047205.1470058848495 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable P=C3=A5 mandag 01. august 2016 kl. 15:33:04, skrev Andreas Joseph Krogh < andreas@visena.com >: I have this schema: =C2=A0 CREATE TABLE onp_crm_person( id serial PRIMARY KEY, onp_user_id bigint=20 referencesonp_user(id) deferrable initially deferred, is_resource boolean n= ot=20 null default false, UNIQUE(onp_user_id) ); CREATE TABLE onp_crm_activity_lo= g( id bigserial PRIMARY KEY, relation_id integer REFERENCES=20 onp_crm_relation(entity_id), logged_forint references=20 onp_crm_person(onp_user_id), durationbigint ); CREATE TABLE onp_crm_invoice= (=20 entity_idbigint PRIMARY KEY REFERENCES onp_crm_entity(entity_id), status_ke= y=20 VARCHAR NOT NULL, credit_against bigint REFERENCES onp_crm_invoice(entity_i= d),=20 sent_dateDATE, UNIQUE(credit_against) deferrable INITIALLY DEFERRED --=20 invoice_print_template_id is added after creation of=20 origo_invoice_print_template); CREATE TABLE onp_crm_invoice_line ( id SERIA= L=20 PRIMARY KEY, invoice_id INTEGER NOT NULL REFERENCES onp_crm_invoice (entity= _id)=20 );CREATE TABLE onp_crm_calendarentry_invoice_membership( invoice_line_id=20 INTEGER NOT NULL REFERENCESonp_crm_invoice_line(id) ON DELETE CASCADE,=20 calendar_entry_idINTEGER NOT NULL REFERENCES onp_crm_activity_log(id), uniq= ue (invoice_line_id, calendar_entry_id)DEFERRABLE INITIALLY DEFERRED );=20 =C2=A0 This query performs terribly slow (=C2=A0~26 minutes,=C2=A01561346.597ms): =C2=A0 explain analyze SELECT log.relation_id as company_id , sum(log.duration) A= S=20 durationFROM onp_crm_activity_log log JOIN onp_crm_person logfor ON=20 logfor.onp_user_id =3Dlog.logged_for AND logfor.is_resource =3D FALSE WHERE= 1 =3D 1=20 -- Filter out already invoiced before 2016-06-27 AND NOT EXISTS( SELECT * F= ROM=20 onp_crm_calendarentry_invoice_membership cemJOIN onp_crm_invoice_line il ON= =20 cem.invoice_line_id =3D il.idJOIN onp_crm_invoice inv ON il.invoice_id =3D= =20 inv.entity_idWHERE cem.calendar_entry_id =3D log.id AND inv.status_key =3D= =20 'INVOICE_STATUS_INVOICED' AND inv.sent_date <=3D '2016-06-27' AND NOT EXIST= S(=20 SELECT* FROM onp_crm_invoice creditnote WHERE il.invoice_id =3D=20 creditnote.credit_againstAND creditnote.status_key =3D 'INVOICE_STATUS_INVO= ICED'=20 ANDcreditnote.sent_date <=3D '2016-06-27' ) ) GROUP BY log.relation_id ;= =20 =C2=A0 Explain output: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=20 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLA= N=20 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------- HashAggregate =C2=A0(cost=3D13778.63..13796.39 rows=3D1421 width=3D12) (ac= tual=20 time=3D1561343.861..1561344.042 rows=3D724 loops=3D1) =C2=A0=C2=A0Group Key: log.relation_id =C2=A0=C2=A0-> =C2=A0Nested Loop Anti Join =C2=A0(cost=3D741.35..13768.63 = rows=3D2000 width=3D12)=20 (actual time=3D471.973..1561221.929 rows=3D96095 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Join Filter: (cem.calendar= _entry_id =3D log.id) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Rows Removed by Join Filte= r: 11895758618 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Hash Join =C2=A0(= cost=3D86.56..9729.03 rows=3D2000 width=3D20) (actual=20 time=3D0.170..668.911 rows=3D181644 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0Hash Cond: (log.logged_for =3D logfor.onp_user_id) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0-> =C2=A0Seq Scan on onp_crm_activity_log log =C2=A0(cost=3D0.0= 0..8930.98=20 rows=3D184398 width=3D24) (actual time=3D0.007..538.893 rows=3D182378 loops= =3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0-> =C2=A0Hash =C2=A0(cost=3D39.46..39.46 rows=3D3768 width=3D8)= (actual=20 time=3D0.126..0.126 rows=3D36 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Buckets: 4096 =C2=A0Batches= : 1 =C2=A0Memory Usage: 34kB =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Bitmap Heap Scan o= n onp_crm_person logfor=20 =C2=A0(cost=3D3.69..39.46 rows=3D3768 width=3D8) (actual time=3D0.040..0.10= 6 rows=3D36 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0Recheck Cond: (onp_user_id IS NOT NULL) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0Filter: (NOT is_resource) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0Rows Removed by Filter: 5 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0Heap Blocks: exact=3D10 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0-> =C2=A0Bitmap Index Scan on onp_crm_person_onp_id_idx=20 =C2=A0(cost=3D0.00..2.75 rows=3D41 width=3D0) (actual time=3D0.019..0.019 r= ows=3D41 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Materialize =C2= =A0(cost=3D654.79..4009.60 rows=3D1 width=3D4) (actual=20 time=3D0.000..2.829 rows=3D65490 loops=3D181644) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0-> =C2=A0Nested Loop =C2=A0(cost=3D654.79..4009.59 rows=3D1 wid= th=3D4) (actual=20 time=3D9.056..386.835 rows=3D85668 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Nested Loop =C2=A0= (cost=3D654.50..4009.27 rows=3D1 width=3D8)=20 (actual time=3D9.046..165.280 rows=3D88151 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0-> =C2=A0Hash Anti Join =C2=A0(cost=3D654.21..4008.72 rows=3D1=20 width=3D8) (actual time=3D9.016..40.672 rows=3D76174 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Hash Cond: (il.invoice_id =3D= =20 creditnote.credit_against) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Seq Scan on onp_crm_i= nvoice_line il=20 =C2=A0(cost=3D0.00..3062.01 rows=3D78001 width=3D8) (actual time=3D0.005..1= 1.259 rows=3D78614=20 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Hash =C2=A0(cost=3D51= 0.56..510.56 rows=3D11492=20 width=3D8) (actual time=3D8.940..8.940 rows=3D372 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0Buckets: 16384 =C2=A0Batches: 1 =C2=A0Memory=20 Usage: 143kB =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0-> =C2=A0Seq Scan on onp_crm_invoice=20 creditnote =C2=A0(cost=3D0.00..510.56 rows=3D11492 width=3D8) (actual time= =3D0.014..7.882=20 rows=3D11507 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Filter: ((sent_date <=3D=20 '2016-06-27'::date) AND ((status_key)::text =3D 'INVOICE_STATUS_INVOICED'::= text)) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Rows Removed by Filter: 149 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0-> =C2=A0Index Only Scan using=20 onp_crm_calendarentry_invoice_invoice_line_id_calendar_entr_key on=20 onp_crm_calendarentry_invoice_membership cem =C2=A0(cost=3D0.29..0.45 rows= =3D9 width=3D8)=20 (actual time=3D0.001..0.001 rows=3D1 loops=3D76174) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Index Cond: (invoice_line_id = =3D il.id) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Heap Fetches: 4371 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Index Scan using o= np_crm_invoice_pkey on=20 onp_crm_invoice inv =C2=A0(cost=3D0.29..0.31 rows=3D1 width=3D8) (actual ti= me=3D0.002..0.002=20 rows=3D1 loops=3D88151) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0Index Cond: (entity_id =3D il.invoice_id) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0Filter: ((sent_date <=3D '2016-06-27'::date) AND=20 ((status_key)::text =3D 'INVOICE_STATUS_INVOICED'::text)) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0Rows Removed by Filter: 0 Planning time: 3.307 ms Execution time: 1561346.597 ms (36 rows) =C2=A0 =C2=A0 Here is with=C2=A0set enable_nestloop to false; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=20 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN=20 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------- HashAggregate =C2=A0(cost=3D15936.07..15953.83 rows=3D1421 width=3D12) (ac= tual=20 time=3D337.878..338.045 rows=3D724 loops=3D1) =C2=A0=C2=A0Group Key: log.relation_id =C2=A0=C2=A0-> =C2=A0Hash Anti Join =C2=A0(cost=3D6258.35..15926.07 rows= =3D2000 width=3D12) (actual=20 time=3D139.976..317.402 rows=3D96097 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Hash Cond: (log.id =3D cem= .calendar_entry_id) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Hash Join =C2=A0(= cost=3D86.56..9729.03 rows=3D2000 width=3D20) (actual=20 time=3D0.164..132.935 rows=3D181646 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0Hash Cond: (log.logged_for =3D logfor.onp_user_id) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0-> =C2=A0Seq Scan on onp_crm_activity_log log =C2=A0(cost=3D0.0= 0..8930.98=20 rows=3D184398 width=3D24) (actual time=3D0.006..89.170 rows=3D182380 loops= =3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0-> =C2=A0Hash =C2=A0(cost=3D39.46..39.46 rows=3D3768 width=3D8)= (actual=20 time=3D0.118..0.118 rows=3D36 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Buckets: 4096 =C2=A0Batches= : 1 =C2=A0Memory Usage: 34kB =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Bitmap Heap Scan o= n onp_crm_person logfor=20 =C2=A0(cost=3D3.69..39.46 rows=3D3768 width=3D8) (actual time=3D0.037..0.10= 1 rows=3D36 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0Recheck Cond: (onp_user_id IS NOT NULL) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0Filter: (NOT is_resource) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0Rows Removed by Filter: 5 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0Heap Blocks: exact=3D10 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0-> =C2=A0Bitmap Index Scan on onp_crm_person_onp_id_idx=20 =C2=A0(cost=3D0.00..2.75 rows=3D41 width=3D0) (actual time=3D0.017..0.017 r= ows=3D41 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Hash =C2=A0(cost= =3D6171.78..6171.78 rows=3D1 width=3D4) (actual=20 time=3D139.779..139.779 rows=3D85668 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0Buckets: 131072 (originally 1024) =C2=A0Batches: 1 (originally = 1)=20 =C2=A0Memory Usage: 4036kB =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0-> =C2=A0Hash Join =C2=A0(cost=3D4562.41..6171.78 rows=3D1 widt= h=3D4) (actual=20 time=3D92.471..125.417 rows=3D85668 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Hash Cond: (cem.invoice_lin= e_id =3D il.id) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Seq Scan on onp_cr= m_calendarentry_invoice_membership=20 cem =C2=A0(cost=3D0.00..1278.44 rows=3D88244 width=3D8) (actual time=3D0.00= 5..7.941=20 rows=3D88734 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Hash =C2=A0(cost= =3D4562.40..4562.40 rows=3D1 width=3D4) (actual=20 time=3D92.444..92.444 rows=3D75570 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0Buckets: 131072 (originally 1024) =C2=A0Batches: 1=20 (originally 1) =C2=A0Memory Usage: 3681kB =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0-> =C2=A0Hash Join =C2=A0(cost=3D4008.74..4562.40 rows=3D1=20 width=3D4) (actual time=3D61.797..79.981 rows=3D75570 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Hash Cond: (inv.entity_id =3D = il.invoice_id) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Seq Scan on onp_crm_i= nvoice inv=20 =C2=A0(cost=3D0.00..510.56 rows=3D11492 width=3D8) (actual time=3D0.027..4.= 489 rows=3D11507=20 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0Filter: ((sent_date <=3D=20 '2016-06-27'::date) AND ((status_key)::text =3D 'INVOICE_STATUS_INVOICED'::= text)) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0Rows Removed by Filter: 151 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Hash =C2=A0(cost=3D40= 08.72..4008.72 rows=3D1=20 width=3D8) (actual time=3D61.751..61.751 rows=3D76182 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0Buckets: 131072 (originally 1024)=20 =C2=A0Batches: 1 (originally 1) =C2=A0Memory Usage: 4000kB =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0-> =C2=A0Hash Anti Join=20 =C2=A0(cost=3D654.21..4008.72 rows=3D1 width=3D8) (actual time=3D12.568..47= .911 rows=3D76182=20 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Hash Cond: (il.invoice_id =3D=20 creditnote.credit_against) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Seq Scan on=20 onp_crm_invoice_line il =C2=A0(cost=3D0.00..3062.01 rows=3D78001 width=3D8)= (actual=20 time=3D0.008..12.476 rows=3D78622 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Hash =C2=A0(cost=3D510.5= 6..510.56=20 rows=3D11492 width=3D8) (actual time=3D12.477..12.477 rows=3D372 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0Buckets: 16384 =C2=A0Batches: 1=20 =C2=A0Memory Usage: 143kB =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0-> =C2=A0Seq Scan on=20 onp_crm_invoice creditnote =C2=A0(cost=3D0.00..510.56 rows=3D11492 width=3D= 8) (actual=20 time=3D0.008..10.963 rows=3D11507 loops=3D1) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Filter: ((sent_date=20 <=3D '2016-06-27'::date) AND ((status_key)::text =3D=20 'INVOICE_STATUS_INVOICED'::text)) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Rows Removed by=20 Filter: 151 Planning time: 3.510 ms Execution time: 338.349 ms (39 rows) So my question is is there something I can do to make PG favor a Hash Anti= =20 Join instead of a Nested Loop Anti Join (which I assume is the problem)? Can the nested NOT EXISTS be re-written to be more performant? =C2=A0 Thanks. =C2=A0 If I leave out the JOIN with onp_crm_person the query-plan is also much mor= e=20 efficient: =C2=A0 explain analyze SELECT log.relation_id as company_id , sum(log.duration) A= S=20 durationFROM onp_crm_activity_log log WHERE 1 =3D 1 -- Filter out already i= nvoiced AND NOTEXISTS( SELECT * FROM onp_crm_calendarentry_invoice_membership cem J= OIN=20 onp_crm_invoice_line ilON cem.invoice_line_id =3D il.id JOIN onp_crm_invoic= e inv=20 ONil.invoice_id =3D inv.entity_id WHERE cem.calendar_entry_id =3D log.id AN= D=20 inv.status_key =3D'INVOICE_STATUS_INVOICED' AND inv.sent_date <=3D '2016-06= -27' AND=20 NOTEXISTS( SELECT * FROM onp_crm_invoice creditnote WHERE il.invoice_id =3D= =20 creditnote.credit_againstAND creditnote.status_key =3D 'INVOICE_STATUS_INVO= ICED'=20 ANDcreditnote.sent_date <=3D '2016-06-27' ) ) GROUP BY log.relation_id ; = =C2=A0 QUERY PLAN=20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------- HashAggregate (cost=3D16192.30..16210.07 rows=3D1421 width=3D12) (actual ti= me =3D562.978..563.144rows=3D724 loops=3D1) Group Key: log.relation_id -> Hash= Anti Join=20 (cost=3D4009.60..15270.19 rows=3D184423 width=3D12) (actual time=3D422.215.= .542.871 rows =3D96712 loops=3D1) Hash Cond: (log.id =3D cem.calendar_entry_id) -> Seq Sc= an on=20 onp_crm_activity_loglog (cost=3D0.00..8932.24 rows=3D184424 width=3D20) (ac= tual time =3D0.007..83.262rows=3D182380 loops=3D1) -> Hash (cost=3D4009.59..4009.59 r= ows=3D1 width=3D4 ) (actualtime=3D422.178..422.178 rows=3D85668 loops=3D1) Buckets: 131072 (o= riginally=20 1024) Batches: 1 (originally 1) Memory Usage: 4036kB -> Nested Loop (cost =3D654.79..4009.59rows=3D1 width=3D4) (actual time=3D12.210..397.383 rows= =3D85668 loops=3D1 ) -> NestedLoop (cost=3D654.50..4009.27 rows=3D1 width=3D8) (actual time =3D12.189..173.380rows=3D88201 loops=3D1) -> Hash Anti Join (cost=3D654.21.= .4008.72 rows =3D1 width=3D8) (actual time=3D12.099..47.757 rows=3D76197 loops=3D1) Hash = Cond:=20 (il.invoice_id =3D creditnote.credit_against) -> Seq Scanon onp_crm_invoice= _line=20 il (cost=3D0.00..3062.01 rows=3D78001 width=3D8) (actual time=3D0.009..13.7= 97 rows=3D78637 loops=3D1) -> Hash (cost=3D510.56..510.56 rows=3D11492 width=3D8) (actual t= ime =3D12.012..12.012rows=3D372 loops=3D1) Buckets: 16384 Batches: 1 Memory Usa= ge: 143kB=20 -> Seq Scanon onp_crm_invoice creditnote (cost=3D0.00..510.56 rows=3D11492 = width=3D8)=20 (actualtime=3D0.026..10.564 rows=3D11507 loops=3D1) Filter: ((sent_date <= =3D=20 '2016-06-27'::date) AND ((status_key)::text =3D 'INVOICE_STATUS_INVOICED'::= text))=20 RowsRemoved by Filter: 155 -> Index Only Scan using=20 onp_crm_calendarentry_invoice_invoice_line_id_calendar_entr_keyon=20 onp_crm_calendarentry_invoice_membership cem (cost=3D0.29..0.45 rows=3D9 wi= dth=3D8)=20 (actualtime=3D0.001..0.001 rows=3D1 loops=3D76197) Index Cond: (invoice_lin= e_id =3D=20 il.id) Heap Fetches:4421 -> Index Scan using onp_crm_invoice_pkey on=20 onp_crm_invoice inv (cost=3D0.29..0.31 rows=3D1 width=3D8) (actual time=3D0= .002..0.002=20 rows=3D1 loops=3D88201) Index Cond: (entity_id =3D il.invoice_id) Filter: (= (sent_date=20 <=3D'2016-06-27'::date) AND ((status_key)::text =3D 'INVOICE_STATUS_INVOICE= D'::text ))Rows Removed by Filter: 0 Planning time: 2.763 ms Execution time: 563.403= ms ( 26rows)=20 =C2=A0 But of course I cannot do that... It's just for information... =C2=A0 -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com www.visena.com =C2=A0 ------=_Part_162_912047205.1470058848495 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
P=C3=A5 mandag 01. august 2016 kl. 15:33:04, skrev Andreas Joseph Krog= h <andreas@visena.com>:
I have this schema:
=C2=A0
CREATE TABLE onp_crm_person(
    id serial PRIMARY KEY,
    onp_user_id bigint refe=
rences onp_user(id) =
deferrable initially deferred,
    is_resource boolean not=
 null default false,
    UNIQUE(onp_user_=
id)
);

CREATE TABLE onp_crm=
_activity_log(
    id bigserial PRIMARY KE=
Y,
    relation_id integer REF=
ERENCES onp_crm_relation(entity_id),
    logged_for int referenc=
es onp_crm_person(onp_user_id),
    duration bigint
);

CREATE TABLE onp_crm=
_invoice(
    entity_id bigint PRIMAR=
Y KEY REFERENCES onp_crm_entity(entity_id),
    status_key VARCHAR NOT =
NULL,
    credit_against bigint R=
EFERENCES onp_crm_invoice(entity_id),
    sent_date DATE,
    UNIQUE(credit_ag=
ainst) deferrable INITIALLY=
 DEFERRED
    -- invoice_print_template_id is add=
ed after creation of origo_invoice_print_template
);

CREATE TABLE onp_crm=
_invoice_line (
    id         SERIAL PRIMA=
RY KEY,
    invoice_id INTEGER NOT =
NULL REFERENCES onp_crm_invoice (entity_id)
);

CREATE TABLE onp_crm=
_calendarentry_invoice_membership(
    invoice_line_id INTEGER=
 NOT NULL REFERENCES onp_crm_invoice_line(id) ON DELETE CASCADE,
    calendar_entry_id INTEG=
ER NOT NULL REFERENCES onp_crm_activity_log(id),
    unique(invoice_l=
ine_id, calendar_entry_id) =
DEFERRABLE INITIALLY DEFERRED
);
=C2=A0
This query performs terribly slow (=C2=A0~26 = minutes,=C2=A01561346.597ms<= /span>):
=C2=A0
explain analyze
SELECT
      log.rel=
ation_id as company_=
id
    , sum(log.duration) AS duration
FROM onp_crm_activit=
y_log log
    JOIN onp_=
crm_person logfor ON logfor.onp_user_id =3D log=
.logged_for AND logfor.is_resource =3D F=
ALSE

WHERE 1 =3D 1
      -- Filter out already invoiced before 2016-06-27
      AND NOT EXISTS(
    SELECT * FROM onp_crm_calendarentry_=
invoice_membership cem
        JOIN onp_crm=
_invoice_line il ON =
cem.invoice_line_id =3D il.id
        JOIN onp_crm=
_invoice inv ON il.i=
nvoice_id =3D inv.entity_id
    WHERE cem.calend=
ar_entry_id =3D log.=
id
          AND inv.st=
atus_key =3D 'INVOICE_STATU=
S_INVOICED'
          AND inv.sent_date <=
=3D '2016-06-27'
          AND NOT EXISTS(
        SELECT * FROM onp_crm_invoice cr=
editnote
        WHERE il.inv=
oice_id =3D creditnote.credit_against
              AND cr=
editnote.status_key =3D 'IN=
VOICE_STATUS_INVOICED'
              AND creditnote.sent=
_date <=3D '2016-06-27'
    )
)

GROUP BY
    log.relat=
ion_id
;
=C2=A0
Explain output:
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN =C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0
---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------
HashAggregate =C2=A0(cost=3D13778.63..13796.39 rows=3D1421 width=3D12) (act= ual time=3D1561343.861..1561344.042 rows=3D724 loops=3D1)
=C2=A0=C2=A0Group Key: log.relation_id
=C2=A0=C2=A0-> =C2=A0Nested Loop Anti Join =C2=A0(cost=3D741.35..13768.6= 3 rows=3D2000 width=3D12) (actual time=3D471.973..1561221.929 rows=3D96095 = loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Join Filter: (cem.calendar_= entry_id =3D log.id)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Rows Removed by Join Filter= : 11895758618
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Hash Join =C2= =A0(cost=3D86.56..9729.03 rows=3D2000 width=3D20) (actual time=3D0.170..668= .911 rows=3D181644 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0Hash Cond: (log.logged_for =3D logfor.onp_user_id)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0-> =C2=A0Seq Scan on onp_crm_activity_log log =C2=A0(cost=3D0.0= 0..8930.98 rows=3D184398 width=3D24) (actual time=3D0.007..538.893 rows=3D1= 82378 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0-> =C2=A0Hash =C2=A0(cost=3D39.46..39.46 rows=3D3768 width=3D8)= (actual time=3D0.126..0.126 rows=3D36 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Buckets: 4096 =C2=A0Batches: 1= =C2=A0Memory Usage: 34kB
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Bitmap Heap Scan o= n onp_crm_person logfor =C2=A0(cost=3D3.69..39.46 rows=3D3768 width=3D8) (a= ctual time=3D0.040..0.106 rows=3D36 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0Recheck Cond: (onp_user_id IS NOT NULL)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0Filter: (NOT is_resource)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0Rows Removed by Filter: 5
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0Heap Blocks: exact=3D10
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0-> =C2=A0Bitmap Index Scan on onp_crm_person_onp_id_idx =C2=A0(cos= t=3D0.00..2.75 rows=3D41 width=3D0) (actual time=3D0.019..0.019 rows=3D41 l= oops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Materialize =C2= =A0(cost=3D654.79..4009.60 rows=3D1 width=3D4) (actual time=3D0.000..2.829 = rows=3D65490 loops=3D181644)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0-> =C2=A0Nested Loop =C2=A0(cost=3D654.79..4009.59 rows=3D1 wid= th=3D4) (actual time=3D9.056..386.835 rows=3D85668 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Nested Loop =C2=A0= (cost=3D654.50..4009.27 rows=3D1 width=3D8) (actual time=3D9.046..165.280 r= ows=3D88151 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0-> =C2=A0Hash Anti Join =C2=A0(cost=3D654.21..4008.72 rows=3D1 wid= th=3D8) (actual time=3D9.016..40.672 rows=3D76174 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Hash Cond: (il.invoice_id =3D cre= ditnote.credit_against)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Seq Scan on onp_crm_i= nvoice_line il =C2=A0(cost=3D0.00..3062.01 rows=3D78001 width=3D8) (actual = time=3D0.005..11.259 rows=3D78614 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Hash =C2=A0(cost=3D51= 0.56..510.56 rows=3D11492 width=3D8) (actual time=3D8.940..8.940 rows=3D372= loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0Buckets: 16384 =C2=A0Batches: 1 =C2=A0Memory Usage: 143kB
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0-> =C2=A0Seq Scan on onp_crm_invoice creditnote =C2=A0(cost=3D0.00..5= 10.56 rows=3D11492 width=3D8) (actual time=3D0.014..7.882 rows=3D11507 loop= s=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Filter: ((sent_date <=3D '2016-06= -27'::date) AND ((status_key)::text =3D 'INVOICE_STATUS_INVOICED'::text)) =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Rows Removed by Filter: 149
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0-> =C2=A0Index Only Scan using onp_crm_calendarentry_invoice_invoi= ce_line_id_calendar_entr_key on onp_crm_calendarentry_invoice_membership ce= m =C2=A0(cost=3D0.29..0.45 rows=3D9 width=3D8) (actual time=3D0.001..0.001 = rows=3D1 loops=3D76174)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Index Cond: (invoice_line_id =3D = il.id)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Heap Fetches: 4371
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Index Scan using o= np_crm_invoice_pkey on onp_crm_invoice inv =C2=A0(cost=3D0.29..0.31 rows=3D= 1 width=3D8) (actual time=3D0.002..0.002 rows=3D1 loops=3D88151)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0Index Cond: (entity_id =3D il.invoice_id)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0Filter: ((sent_date <=3D '2016-06-27'::date) AND ((status_key)::te= xt =3D 'INVOICE_STATUS_INVOICED'::text))
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0Rows Removed by Filter: 0
Planning time: 3.307 ms
Execution time: 1561346.597 ms
(36 rows)

=C2=A0
=C2=A0
Here is with=C2=A0set enable_nestloop to false;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0QUERY PLAN =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0
---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------
HashAggregate =C2=A0(cost=3D15936.07..15953.83 rows=3D1421 width=3D12) (act= ual time=3D337.878..338.045 rows=3D724 loops=3D1)
=C2=A0=C2=A0Group Key: log.relation_id
=C2=A0=C2=A0-> =C2=A0Hash Anti Join =C2=A0(cost=3D6258.35..15926.07 rows= =3D2000 width=3D12) (actual time=3D139.976..317.402 rows=3D96097 loops=3D1)=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Hash Cond: (log.id =3D cem.= calendar_entry_id)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Hash Join =C2= =A0(cost=3D86.56..9729.03 rows=3D2000 width=3D20) (actual time=3D0.164..132= .935 rows=3D181646 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0Hash Cond: (log.logged_for =3D logfor.onp_user_id)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0-> =C2=A0Seq Scan on onp_crm_activity_log log =C2=A0(cost=3D0.0= 0..8930.98 rows=3D184398 width=3D24) (actual time=3D0.006..89.170 rows=3D18= 2380 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0-> =C2=A0Hash =C2=A0(cost=3D39.46..39.46 rows=3D3768 width=3D8)= (actual time=3D0.118..0.118 rows=3D36 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Buckets: 4096 =C2=A0Batches: 1= =C2=A0Memory Usage: 34kB
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Bitmap Heap Scan o= n onp_crm_person logfor =C2=A0(cost=3D3.69..39.46 rows=3D3768 width=3D8) (a= ctual time=3D0.037..0.101 rows=3D36 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0Recheck Cond: (onp_user_id IS NOT NULL)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0Filter: (NOT is_resource)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0Rows Removed by Filter: 5
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0Heap Blocks: exact=3D10
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0-> =C2=A0Bitmap Index Scan on onp_crm_person_onp_id_idx =C2=A0(cos= t=3D0.00..2.75 rows=3D41 width=3D0) (actual time=3D0.017..0.017 rows=3D41 l= oops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Hash =C2=A0(cos= t=3D6171.78..6171.78 rows=3D1 width=3D4) (actual time=3D139.779..139.779 ro= ws=3D85668 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0Buckets: 131072 (originally 1024) =C2=A0Batches: 1 (originally 1) = =C2=A0Memory Usage: 4036kB
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0-> =C2=A0Hash Join =C2=A0(cost=3D4562.41..6171.78 rows=3D1 widt= h=3D4) (actual time=3D92.471..125.417 rows=3D85668 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Hash Cond: (cem.invoice_line_i= d =3D il.id)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Seq Scan on onp_cr= m_calendarentry_invoice_membership cem =C2=A0(cost=3D0.00..1278.44 rows=3D8= 8244 width=3D8) (actual time=3D0.005..7.941 rows=3D88734 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Hash =C2=A0(cost= =3D4562.40..4562.40 rows=3D1 width=3D4) (actual time=3D92.444..92.444 rows= =3D75570 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0Buckets: 131072 (originally 1024) =C2=A0Batches: 1 (originally 1) =C2= =A0Memory Usage: 3681kB
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0-> =C2=A0Hash Join =C2=A0(cost=3D4008.74..4562.40 rows=3D1 width= =3D4) (actual time=3D61.797..79.981 rows=3D75570 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Hash Cond: (inv.entity_id =3D il.= invoice_id)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Seq Scan on onp_crm_i= nvoice inv =C2=A0(cost=3D0.00..510.56 rows=3D11492 width=3D8) (actual time= =3D0.027..4.489 rows=3D11507 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0Filter: ((sent_date <=3D '2016-06-27'::date) AND ((status_key)::text = =3D 'INVOICE_STATUS_INVOICED'::text))
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0Rows Removed by Filter: 151
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Hash =C2=A0(cost=3D40= 08.72..4008.72 rows=3D1 width=3D8) (actual time=3D61.751..61.751 rows=3D761= 82 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0Buckets: 131072 (originally 1024) =C2=A0Batches: 1 (originally 1) =C2=A0= Memory Usage: 4000kB
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0-> =C2=A0Hash Anti Join =C2=A0(cost=3D654.21..4008.72 rows=3D1 width= =3D8) (actual time=3D12.568..47.911 rows=3D76182 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Hash Cond: (il.invoice_id =3D credit= note.credit_against)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Seq Scan on onp_crm_invo= ice_line il =C2=A0(cost=3D0.00..3062.01 rows=3D78001 width=3D8) (actual tim= e=3D0.008..12.476 rows=3D78622 loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-> =C2=A0Hash =C2=A0(cost=3D510.5= 6..510.56 rows=3D11492 width=3D8) (actual time=3D12.477..12.477 rows=3D372 = loops=3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= Buckets: 16384 =C2=A0Batches: 1 =C2=A0Memory Usage: 143kB
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= -> =C2=A0Seq Scan on onp_crm_invoice creditnote =C2=A0(cost=3D0.00..510.= 56 rows=3D11492 width=3D8) (actual time=3D0.008..10.963 rows=3D11507 loops= =3D1)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Filter: ((sent_date <=3D '2016-06-27= '::date) AND ((status_key)::text =3D 'INVOICE_STATUS_INVOICED'::text))
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Rows Removed by Filter: 151
Planning time: 3.510 ms
Execution time: 338.349 ms
(39 rows)

So my question is is there something I can do to make PG favor a Hash Anti = Join instead of a Nested Loop Anti Join (which I assume is the problem)?
Can the nested NOT EXISTS be re-written to be more performant?
=C2=A0
Thanks.
=C2=A0
If I leave out the JOIN with onp_crm_person the query-plan is also muc= h more efficient:
=C2=A0
explain analyze
SELECT
      log.rel=
ation_id as company_=
id
    , sum(log.duration) AS duration
FROM onp_crm_activit=
y_log log

WHERE 1 =3D 1
      -- Filter out already invoiced
      AND NOT EXISTS(
    SELECT * FROM onp_crm_calendarentry_=
invoice_membership cem
        JOIN onp_crm=
_invoice_line il ON =
cem.invoice_line_id =3D il.id
        JOIN onp_crm=
_invoice inv ON il.i=
nvoice_id =3D inv.entity_id
    WHERE cem.calend=
ar_entry_id =3D log.=
id
          AND inv.st=
atus_key =3D 'INVOICE_STATU=
S_INVOICED'
          AND inv.sent_date <=
=3D '2016-06-27'
          AND NOT EXISTS(
        SELECT * FROM onp_crm_invoice cr=
editnote
        WHERE il.inv=
oice_id =3D creditnote.credit_against
              AND cr=
editnote.status_key =3D 'IN=
VOICE_STATUS_INVOICED'
              AND creditnote.sent=
_date <=3D '2016-06-27'
    )
)

GROUP BY
    log.relat=
ion_id
;
=C2=A0
                                           =
                                                                          <=
span style=3D"color:#000080;font-weight:bold;">QUERY PLAN           =
                                                                           =
                               =20
--------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------
 HashAggrega=
te  (cost=3D16192.30=
..16210.07 rows=3D1421 width=3D12) (actual tim=
e=3D562.978..563.144 rows=3D724 loops=3D1)
   Group Key: log.relation_id
   ->  Hash Anti =
Join  (cost=3D4009.60..15270.19 rows=3D184423 width=3D12)=
 (actual time=3D422.=
215..542.871 rows=3D=
96712 loops=3D1)
         Hash Cond: =
(log.id =3D cem.cale=
ndar_entry_id)
         ->  Seq Scan on=
 onp_crm_activity_log log  (cost=
=3D0.00..8932.24 rows=3D184424 width=3D20) (actual time=3D0.007..83.262 rows=3D182380 loops=3D1)
         ->  Hash  (cost=3D4009.59..4=
009.59 rows=3D1 width=3D4<=
/span>) (actual time=
=3D422.178..422.178 rows=3D85668 loops=3D1)
               Buckets: 131072 (origi=
nally 1024)  Batches: 1 (originally 1)  Memory Usage: 4036kB
               ->  Nested Loop  (cost=
=3D654.79..4009.59 rows=3D1 width=3D4) (actual time=3D12.210..397.383 rows=3D85668 loops=3D1)
                     ->  Nested Loop  (cost<=
/span>=3D654.50..4009.27 ro=
ws=3D1 width=3D8) (actual time=3D12.189..173.380 rows=3D88201 loops=
=3D1)
                           ->  Hash Anti Joi=
n  (cost=3D65=
4.21..4008.72 rows=
=3D1 width=3D8) (actual ti=
me=3D12.099..47.757 =
rows=3D76197 loops=3D1)
                                 Hash Cond: (il.invoice_id =3D creditnote.credit_against)
                                 ->  Seq Scan on onp_crm_invoice_line il  (cost=3D0.00..3062.01 rows=3D78001 width=3D8) (actual time=3D0.009..13.797 rows=3D78637 loops=3D1)
                                 ->  Hash  (=
cost=3D510.56..510.56 rows=3D11492 width=3D8) (actual time=3D12.012..12.012 rows=3D372 lo=
ops=3D1)
                                       Buckets: 16384  Batches: 1  Memory =
Usage: 143kB
                                       ->  Seq Scan on onp_crm_invoice creditnote  (cost=3D0.00..510.56 rows=3D11492 width=3D8) (ac=
tual time=3D0.026..1=
0.564 rows=3D11507 loops=3D1)
                                             Filter: ((sent_date <=3D '2016-06-27'::date) AND ((status_key)::text =3D 'INVOI=
CE_STATUS_INVOICED'::text))
                                             Rows Removed by Filter: 155
                           -&g=
t;  Index Only Scan =
using onp_crm_calend=
arentry_invoice_invoice_line_id_calendar_entr_key on onp_crm_calendarentry_invoice_membership c=
em  (cost=3D0.29..0.=
45 rows=3D9 width=3D8) (actual time=3D0=
.001..0.001 rows=3D<=
span style=3D"color:#0000ff;">1 loops=3D76197)
                                 Index Cond: (invoice_line_id =3D il.id)
                                 Heap Fetches: 4421
                     ->  Index Scan using onp_crm_invoice_pkey on onp_crm_invoice inv  =
(cost=3D0.29..0.31 <=
span style=3D"color:#000080;font-weight:bold;">rows=3D1 width=3D8) =
(actual time=3D0.002=
..0.002 rows=3D1 loops=3D8=
8201)
                           =
Index Cond: (entity_id =3D il.invoice_id)
                           =
Filter: ((sent_date <=3D '2016-06-27'::date) AND (=
(status_key)::text =
=3D 'INVOICE_STATUS_INVOICE=
D'::text))
                           =
Rows Removed by Filt=
er: 0
 Planning time: 2.7=
63 ms
 Execution time: 563.403 ms
(26 rows)
=C2=A0
But of course I cannot do that... It's just for information...
=C2=A0
--
Andrea= s Joseph Krogh
CTO / Partner<= /span> - Visena AS
Mobile: +47 90= 9 56 963
=3D""
=C2=A0
------=_Part_162_912047205.1470058848495-- ------=_Part_161_624504965.1470058848475 Content-Type: image/png Content-Transfer-Encoding: base64 Content-Disposition: inline Content-ID: iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgIfAhkiAAABzBJREFU aEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrAUgWRK/C6Al8H3lTgy0PGbzFdQc4VJP/H ADs43q6kROeJNbOYgQACIAgCWJKng4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/NPtk9uub7Gfd t5mBx1NhqSEo8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3saQaHt7fY70X949On aS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8WfCi80Xgf9vVlrhg9yRONe /f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl94bAniukEbo/n7YPmuTETzIab4Y9ZWCrKexd 8M58lxPCvvD6aihfvexbEQoPYO8NQRO0JnddGO6FJYaVsBde7cXj7KRk4LsqDxQzCYeGsJNgaXZe +JXkyGgWINq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O6x1IvtD40GWyJYYdkF0jIbYZlN16xygI gl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk79DEPdQ5u0g1vCvvqKXIW8mZYW+F3Tg4r8HvZ kQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMH QPwReg2wW8umEe/+L8T/LEzBGF9nXjzZ46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aY hdiCnRC7jaj7eiK2ETInC5PRF6LYkSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZTDUEj8u6IVhu EhRUJ2U2g6UlugyNb03Hl9obHwlxJbcRzcYjO4QPjVfGFTQav4vrmp7cpMp2qbHnBxWJbisbho2Q XI6C1sIHDUFhH4Hij4UbIev66eANeiwbkA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8U BIYhBd6oSUgYMijOkecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6Okjzx JO5afGe6kmhBicWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdSPAvxHYin6YmN9YBX QmTYZZNh14vHhhhal5vtcIrJjmvszPRJdExH3MWHvykoBEc9CoCGWJisOLOGoCORs1FvIMbYA8z3 kwM59oemy6LlWrLxFLmWgiQAfEGd8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNj v/gANOdQxPfciP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcPc4rV NjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5pqCuK01k40kSNtnKiKIl UfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/bdb9WRjmMk3jyUMAbIoyJq9e4cVmgzKt9j5gN J/aYDhk+hhjExwaPcz5PObA5Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCWqBQ9nCjJ5neivC82 Nr4LeS2j1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3TpGRpk0Tzpj3ZEJXi/GR a6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqpHLu9jBrhUuhNwVGbpyZrzrl0 HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1t/C/SU+cAGtdGo+fVnzycUX5wmn6eCKPmWYJG2E/ ppTsuRCbvUD9fwquksG5GqLVKhzDQ3GrEyLKSXhsiK3T5j9EyxffCFOYi2wUlPyFFDQAhehESDjQ GoX0ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnHqcas/72wvWF+mSf7N/Watp9kTXoluzeS0fB9 9CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9ITbScZWs7xOYNt9pwxSZtYDsX/GE3xTkrXgwAsXm fud08FiTeC+m2/o7ppo+PTS/NBK5ARpDG5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofs sxBA1gj5NXGvfJu/Qh7HwQh/VDUEyUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+ olK0A1EKRCE4snsh42w8Mv/Zhxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsYKyZxvxZSZBAgjhIz YDz+AbfD37GtbaoSKzgGt+lKfI/GZtay6vG4VXTpPsh+IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuu huC4pCGGx+YD2fcc5tfIAA0h/Et4/jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3 CHU2mTMwZ+B/y8DfSMBLLOYXVuEAAAAASUVORK5CYII= ------=_Part_161_624504965.1470058848475-- ------=_Part_160_1777116725.1470058848475--