Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dW2ca-0003lE-TN for pgsql-performance@arkaria.postgresql.org; Fri, 14 Jul 2017 15:35:13 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dW2cZ-0004nJ-Uw for pgsql-performance@arkaria.postgresql.org; Fri, 14 Jul 2017 15:35:12 +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 1dW2cY-0004mS-BL for pgsql-performance@postgresql.org; Fri, 14 Jul 2017 15:35:11 +0000 Received: from mail-qk0-x22e.google.com ([2607:f8b0:400d:c09::22e]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dW2cT-0002XP-Vv for pgsql-performance@postgresql.org; Fri, 14 Jul 2017 15:35:08 +0000 Received: by mail-qk0-x22e.google.com with SMTP id p73so14832478qka.2 for ; Fri, 14 Jul 2017 08:35:05 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=3FprpC1sgM0qocKY+ApD7cFNKfG1F0TxrBoCBR+liws=; b=Pbfb/WE9g/yIHHQ3seFNMhDV9uvhGohnO/uy8t1OG/1S36KZhlH7+eYzSwADm2KzuW V+H3xsLNtkoGARvgNAu1Qk3k09ykxqhCeewPc4lIg0aA/m2cTtPp/4+MgQ3wZ5WHlQh3 8W9ictwwiiF7mLWgfAew0CjTL5mRaVLgYENLNP1nZZmClKRWBUDUNkxxiIvAbcRJ2Dv+ aefCUihUZuJsKLK69MipTxHy4ZCFsQcgwwVS5cv+cGLKlZXxBvGRWekZP8BmcB2sWkGT TaE4DDoDSmTRHa3ihwkeqPNmDeEBs4qwXj/NhL+o2ALVPtiHwPtJvLQEDytkbX1zyk4+ Og2g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=3FprpC1sgM0qocKY+ApD7cFNKfG1F0TxrBoCBR+liws=; b=rEFTON8g1F0Mz81UuLTW5up+LmHDVl1QOm2jVRUT+zBBVbdYyirxTI+9/HKKcrDnJC aniKln5RwxcWWGdjM74QfZMQDZSpG2caIES/v/Wqo3CNve9Kq8QVNvxFPYRu6x3DKqco LYhK5FwG48JsiQFir66xAcLyhX3Td0MXsEG0Mgii/MavIjQX8Nxo7FQkMusTX+104J2E 9WJO1iQigAPUXsg7emsmFvUg6dXrYDCsEWoRmFYYEDJNvAF+g2CKZ2jgYF94jpvXxzJ5 CHbEzetssEPOPDBBsQpQboZp1wbqraxBoW4Fw722ugv2gSIx/Cf/ltj1aAOcVbH8MxHI Fp7Q== X-Gm-Message-State: AIVw111DMaNVWrOYEVEBMd/f4c8Ih37WosHJZQBNQWhFAPN11WlCOYs2 XCclb7PZHmGWkorvrK7hiaYTKLi7Mw== X-Received: by 10.55.167.211 with SMTP id q202mr12997221qke.77.1500046504275; Fri, 14 Jul 2017 08:35:04 -0700 (PDT) MIME-Version: 1.0 Received: by 10.12.157.11 with HTTP; Fri, 14 Jul 2017 08:34:43 -0700 (PDT) In-Reply-To: References: From: Charles Nadeau Date: Fri, 14 Jul 2017 17:34:43 +0200 Message-ID: Subject: Re: Very poor read performance, query independent To: Igor Neyman Cc: Jeff Janes , "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="001a114fc71008b548055448cb8b" 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 --001a114fc71008b548055448cb8b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Igor, Initially temp_buffer was left to its default value (8MB). Watching the content of the directory that stores the temporary files, I found that I need at most 21GB of temporary files space. Should I set temp_buffer to 21GB? Here is the explain you requested with work_mem set to 6GB: flows=3D# set work_mem=3D'6GB'; SET flows=3D# explain (analyze, buffers) SELECT DISTINCT srcaddr, dstaddr, dstport, COUNT(*) AS conversation, SUM(doctets) / 1024 / 1024 AS mbytes FROM flowscompact, mynetworks WHERE mynetworks.ipaddr >>=3D flowscompact.srcaddr AND dstaddr IN ( SELECT dstaddr FROM dstexterne ) GROUP BY srcaddr, dstaddr, dstport ORDER BY mbytes DESC LIMIT 50; QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------ Limit (cost=3D48135680.07..48135680.22 rows=3D50 width=3D52) (actual time=3D2227678.196..2227678.223 rows=3D50 loops=3D1) Buffers: shared hit=3D728798038 read=3D82974833, temp read=3D381154 written=3D381154 -> Unique (cost=3D48135680.07..48143613.62 rows=3D2644514 width=3D52) (actual time=3D2227678.194..2227678.217 rows=3D50 loops=3D1) Buffers: shared hit=3D728798038 read=3D82974833, temp read=3D38115= 4 written=3D381154 -> Sort (cost=3D48135680.07..48137002.33 rows=3D2644514 width=3D= 52) (actual time=3D2227678.192..2227678.202 rows=3D50 loops=3D1) Sort Key: (((sum(flows.doctets) / '1024'::numeric) / '1024'::numeric)) DESC, flows.srcaddr, flows.dstaddr, flows.dstport, (count(*)) Sort Method: quicksort Memory: 654395kB Buffers: shared hit=3D728798038 read=3D82974833, temp read=3D381154 written=3D381154 -> GroupAggregate (cost=3D48059426.65..48079260.50 rows=3D2644514 width=3D52) (actual time=3D2167909.030..2211446.192 rows=3D5= 859671 loops=3D1) Group Key: flows.srcaddr, flows.dstaddr, flows.dstport Buffers: shared hit=3D728798038 read=3D82974833, temp read=3D381154 written=3D381154 -> Sort (cost=3D48059426.65..48060748.90 rows=3D2644= 514 width=3D20) (actual time=3D2167896.815..2189107.205 rows=3D91745640 loops= =3D1) Sort Key: flows.srcaddr, flows.dstaddr, flows.dstport Sort Method: external merge Disk: 3049216kB Buffers: shared hit=3D728798038 read=3D82974833, temp read=3D381154 written=3D381154 -> Gather (cost=3D30060688.07..48003007.07 rows=3D2644514 width=3D20) (actual time=3D1268989.000..1991357.232 rows=3D9= 1745640 loops=3D1) Workers Planned: 12 Workers Launched: 12 Buffers: shared hit=3D728798037 read=3D829= 74833 -> Hash Semi Join (cost=3D30059688.07..47951761.31 rows=3D220376 width=3D20) (actual time=3D1268845.181..2007864.725 rows=3D7057357 loops=3D13) Hash Cond: (flows.dstaddr =3D flows_1.dstaddr) Buffers: shared hit=3D728795193 read=3D82974833 -> Nested Loop (cost=3D0.03..17891246.86 rows=3D220376 width=3D20) (actual time=3D0.207..723790.283 rows=3D37910370 loops=3D13) Buffers: shared hit=3D59069222= 9 read=3D14991777 -> Parallel Seq Scan on flows (cost=3D0.00..16018049.14 rows=3D55094048 width=3D20) (actual time=3D0.152..566179.117 rows=3D45371630 loops=3D13) Buffers: shared hit=3D860990 read=3D14991777 -> Index Only Scan using mynetworks_ipaddr_idx on mynetworks (cost=3D0.03..0.03 rows=3D1 width=3D8) (actual time=3D0.002..0.002 rows=3D1 loops=3D589831190) Index Cond: (ipaddr >>= =3D (flows.srcaddr)::ip4r) Heap Fetches: 0 Buffers: shared hit=3D589831203 -> Hash (cost=3D30059641.47..30059641.47 rows=3D13305 width=3D4) (actual time=3D1268811.101..1268811.101 rows=3D3803508 loops=3D13) Buckets: 4194304 (originally 16384) Batches: 1 (originally 1) Memory Usage: 166486kB Buffers: shared hit=3D13810296= 4 read=3D67983056 -> HashAggregate (cost=3D30059561.64..30059601.56 rows=3D13305 width=3D4) (actual time=3D1265248.165..1267432.083 rows=3D3803508 loops=3D13) Group Key: flows_1.dstaddr Buffers: shared hit=3D138102964 read=3D67983056 -> Nested Loop Anti Join (cost=3D0.00..29729327.92 rows=3D660467447 width=3D4) (actual time=3D0.389..1201072.707 rows=3D125838232 loops=3D13) Join Filter: (mynetworks_1.ipaddr >> (flows_1.dstaddr)::ip4r) Rows Removed by Join Filter: 503353617 Buffers: shared hit=3D138102964 read=3D67983056 -> Seq Scan on flows flows_1 (cost=3D0.00..17836152.73 rows=3D661128576 width=3D4) (actua= l time=3D0.322..343152.274 rows=3D589831190 loops=3D13) Buffers: shared hit=3D138102915 read=3D67983056 -> Materialize (cost=3D0.00..1.02 rows=3D4 width=3D8) (actual time=3D0.000..0.000 rows=3D= 2 loops=3D7667805470) Buffers: shared hit=3D13 -> Seq Scan on mynetworks mynetworks_1 (cost=3D0.00..1.01 rows=3D4 width=3D8) (actual time=3D0.006..0.007 rows=3D4 loops=3D13) Buffers: shared hit=3D13 Planning time: 0.941 ms Execution time: 2228345.171 ms (48 rows) With a work_mem at 6GB, I noticed that for the first 20 minutes the query was running, the i/o wait was much lower, hovering aroun 3% then it jumped 45% until almost the end of the query. flowscompact and dstexterne are actually views. I use views to simplify query writing and to "abstract" queries that are use often in other queries. flowscompact is a view built on table flows (having about 590 million rows), it only keeps the most often used fields. flows=3D# \d+ flowscompact; View "public.flowscompact" Column | Type | Modifiers | Storage | Description -----------+--------------------------+-----------+---------+------------- flow_id | bigint | | plain | sysuptime | bigint | | plain | exaddr | ip4 | | plain | dpkts | integer | | plain | doctets | bigint | | plain | first | bigint | | plain | last | bigint | | plain | srcaddr | ip4 | | plain | dstaddr | ip4 | | plain | srcport | integer | | plain | dstport | integer | | plain | prot | smallint | | plain | tos | smallint | | plain | tcp_flags | smallint | | plain | timestamp | timestamp with time zone | | plain | View definition: SELECT flowstimestamp.flow_id, flowstimestamp.sysuptime, flowstimestamp.exaddr, flowstimestamp.dpkts, flowstimestamp.doctets, flowstimestamp.first, flowstimestamp.last, flowstimestamp.srcaddr, flowstimestamp.dstaddr, flowstimestamp.srcport, flowstimestamp.dstport, flowstimestamp.prot, flowstimestamp.tos, flowstimestamp.tcp_flags, flowstimestamp."timestamp" FROM flowstimestamp; mynetworks is a table having one column and 4 rows; it contains a list of our network networks: flows=3D# select * from mynetworks; ipaddr ---------------- 192.168.0.0/24 10.112.12.0/30 10.112.12.4/30 10.112.12.8/30 (4 row) flows=3D# \d+ mynetworks; Table "public.mynetworks" Column | Type | Modifiers | Storage | Stats target | Description --------+------+-----------+---------+--------------+------------- ipaddr | ip4r | | plain | | Indexes: "mynetworks_ipaddr_idx" gist (ipaddr) dstexterne is a view listing all the destination IPv4 addresses not inside our network; it has one column and 3.8 million rows. flows=3D# \d+ dstexterne; View "public.dstexterne" Column | Type | Modifiers | Storage | Description ---------+------+-----------+---------+------------- dstaddr | ip4 | | plain | View definition: SELECT DISTINCT flowscompact.dstaddr FROM flowscompact LEFT JOIN mynetworks ON mynetworks.ipaddr >> flowscompact.dstaddr::ip4= r WHERE mynetworks.ipaddr IS NULL; Thanks! Charles On Wed, Jul 12, 2017 at 6:39 PM, Igor Neyman wrote= : > > > > > *From:* pgsql-performance-owner@postgresql.org [mailto:pgsql-performance- > owner@postgresql.org ] *On Behalf > Of *Charles Nadeau > *Sent:* Wednesday, July 12, 2017 6:05 AM > *To:* Jeff Janes > *Cc:* pgsql-performance@postgresql.org > *Subject:* Re: [PERFORM] Very poor read performance, query independent > > > > > > flows=3D# explain (analyze, buffers) SELECT DISTINCT > > flows-# srcaddr, > > flows-# dstaddr, > > flows-# dstport, > > flows-# COUNT(*) AS conversation, > > flows-# SUM(doctets) / 1024 / 1024 AS mbytes > > flows-# FROM > > flows-# flowscompact, > > flows-# mynetworks > > flows-# WHERE > > flows-# mynetworks.ipaddr >>=3D flowscompact.srcaddr > > flows-# AND dstaddr IN > > flows-# ( > > flows(# SELECT > > flows(# dstaddr > > flows(# FROM > > flows(# dstexterne > > flows(# ) > > flows-# GROUP BY > > flows-# srcaddr, > > flows-# dstaddr, > > flows-# dstport > > flows-# ORDER BY > > flows-# mbytes DESC LIMIT 50; > > LOG: temporary file: path "pg_tblspc/36238/PG_9.6_ > 201608131/pgsql_tmp/pgsql_tmp14573.6", size 1073741824 > > LOG: temporary file: path "pg_tblspc/36238/PG_9.6_ > 201608131/pgsql_tmp/pgsql_tmp14573.7", size 1073741824 > > LOG: temporary file: path "pg_tblspc/36238/PG_9.6_ > 201608131/pgsql_tmp/pgsql_tmp14573.8", size 639696896 > > LOG: duration: 2765020.327 ms statement: explain (analyze, buffers) > SELECT DISTINCT > > srcaddr, > > dstaddr, > > dstport, > > COUNT(*) AS conversation, > > SUM(doctets) / 1024 / 1024 AS mbytes > > FROM > > flowscompact, > > mynetworks > > WHERE > > mynetworks.ipaddr >>=3D flowscompact.srcaddr > > AND dstaddr IN > > ( > > SELECT > > dstaddr > > FROM > > dstexterne > > ) > > GROUP BY > > srcaddr, > > dstaddr, > > dstport > > ORDER BY > > mbytes DESC LIMIT 50; > > > QUERY PLAN > > > > ------------------------------------------------------------ > ------------------------------------------------------------ > ------------------------------------------------------------ > -------------------------------------------------- > > Limit (cost=3D37762321.83..37762321.98 rows=3D50 width=3D52) (actual > time=3D2764548.863..2764548.891 rows=3D50 loops=3D1) > > Buffers: shared hit=3D1116590560 read=3D15851133, temp read=3D340244 > written=3D340244 > > I/O Timings: read=3D5323746.860 > > -> Unique (cost=3D37762321.83..37769053.57 rows=3D2243913 width=3D52= ) > (actual time=3D2764548.861..2764548.882 rows=3D50 loops=3D1) > > Buffers: shared hit=3D1116590560 read=3D15851133, temp read=3D34= 0244 > written=3D340244 > > I/O Timings: read=3D5323746.860 > > -> Sort (cost=3D37762321.83..37763443.79 rows=3D2243913 width= =3D52) > (actual time=3D2764548.859..2764548.872 rows=3D50 loops=3D1) > > Sort Key: (((sum(flows.doctets) / '1024'::numeric) / > '1024'::numeric)) DESC, flows.srcaddr, flows.dstaddr, flows.dstport, > (count(*)) > > Sort Method: quicksort Memory: 563150kB > > Buffers: shared hit=3D1116590560 read=3D15851133, temp > read=3D340244 written=3D340244 > > I/O Timings: read=3D5323746.860 > > -> GroupAggregate (cost=3D37698151.34..37714980.68 > rows=3D2243913 width=3D52) (actual time=3D2696721.610..2752109.551 rows= =3D4691734 > loops=3D1) > > Group Key: flows.srcaddr, flows.dstaddr, flows.dstpo= rt > > Buffers: shared hit=3D1116590560 read=3D15851133, te= mp > read=3D340244 written=3D340244 > > I/O Timings: read=3D5323746.860 > > -> Sort (cost=3D37698151.34..37699273.29 > rows=3D2243913 width=3D20) (actual time=3D2696711.428..2732781.705 rows= =3D81896988 > loops=3D1) > > Sort Key: flows.srcaddr, flows.dstaddr, > flows.dstport > > Sort Method: external merge Disk: 2721856kB > > Buffers: shared hit=3D1116590560 read=3D158511= 33, > temp read=3D340244 written=3D340244 > > I/O Timings: read=3D5323746.860 > > -> Gather (cost=3D19463936.00..37650810.19 > rows=3D2243913 width=3D20) (actual time=3D1777219.713..2590530.887 rows= =3D81896988 > loops=3D1) > > Workers Planned: 9 > > Workers Launched: 9 > > Buffers: shared hit=3D1116590559 > read=3D15851133 > > I/O Timings: read=3D5323746.860 > > -> Hash Semi Join > (cost=3D19462936.00..37622883.23 rows=3D249324 width=3D20) (actual > time=3D1847579.360..2602039.780 rows=3D8189699 loops=3D10) > > Hash Cond: (flows.dstaddr =3D > flows_1.dstaddr) > > Buffers: shared hit=3D1116588309 > read=3D15851133 > > I/O Timings: read=3D5323746.860 > > -> Nested Loop > (cost=3D0.03..18159012.30 rows=3D249324 width=3D20) (actual > time=3D1.562..736556.583 rows=3D45499045 loops=3D10) > > Buffers: shared hit=3D996551= 813 > read=3D15851133 > > I/O Timings: read=3D5323746.= 860 > > -> Parallel Seq Scan on > flows (cost=3D0.00..16039759.79 rows=3D62330930 width=3D20) (actual > time=3D1.506..547485.066 rows=3D54155970 loops=3D10) > > Buffers: shared > hit=3D1634 read=3D15851133 > > I/O Timings: > read=3D5323746.860 > > -> Index Only Scan using > mynetworks_ipaddr_idx on mynetworks (cost=3D0.03..0.03 rows=3D1 width=3D= 8) > (actual time=3D0.002..0.002 rows=3D1 loops=3D541559704) > > Index Cond: (ipaddr >>= =3D > (flows.srcaddr)::ip4r) > > Heap Fetches: 59971474 > > Buffers: shared > hit=3D996550152 > > -> Hash > (cost=3D19462896.74..19462896.74 rows=3D11210 width=3D4) (actual > time=3D1847228.894..1847228.894 rows=3D3099798 loops=3D10) > > Buckets: 4194304 (originally > 16384) Batches: 1 (originally 1) Memory Usage: 141746kB > > Buffers: shared hit=3D120036= 496 > > -> HashAggregate > (cost=3D19462829.48..19462863.11 rows=3D11210 width=3D4) (actual > time=3D1230049.015..1845955.764 rows=3D3099798 loops=3D10) > > Group Key: > flows_1.dstaddr > > Buffers: shared > hit=3D120036496 > > -> Nested Loop Anti > Join (cost=3D0.12..19182620.78 rows=3D560417390 width=3D4) (actual > time=3D0.084..831832.333 rows=3D113420172 loops=3D10) > > Join Filter: > (mynetworks_1.ipaddr >> (flows_1.dstaddr)::ip4r) > > Rows Removed by > Join Filter: 453681377 > > Buffers: shared > hit=3D120036496 > > -> Index Only > Scan using flows_srcaddr_dstaddr_idx on flows flows_1 > (cost=3D0.12..9091067.70 rows=3D560978368 width=3D4) (actual > time=3D0.027..113052.437 rows=3D541559704 loops=3D10) > > Heap > Fetches: 91 > > Buffers: > shared hit=3D120036459 > > -> Materialize > (cost=3D0.00..1.02 rows=3D4 width=3D8) (actual time=3D0.000..0.000 rows= =3D2 > loops=3D5415597040) > > Buffers: > shared hit=3D10 > > -> Seq > Scan on mynetworks mynetworks_1 (cost=3D0.00..1.01 rows=3D4 width=3D8) (= actual > time=3D0.007..0.008 rows=3D4 loops=3D10) > > > Buffers: shared hit=3D10 > > Planning time: 6.689 ms > > Execution time: 2764860.853 ms > > (58 rows) > > > > Regarding "Also using dstat I can see that iowait time is at about 25%", = I > don't think the server was doing anything else. If it is important, I can > repeat the benchmarks. > > Thanks! > > > > Charles > > > > Charles, > > > > In your original posting I couldn=E2=80=99t find what value you set for > temp_buffers. > > Considering you have plenty of RAM, try setting temp_buffers=3D=E2=80=996= GB=E2=80=99 and > then run =E2=80=98explain (analyze, buffers) select=E2=80=A6=E2=80=99 in = the same session. This > should alleviate =E2=80=9Cdisk sort=E2=80=99 problem. > > > > Also, could you post the structure of flowscompact, mynetworks, and > dstextern tables with all the indexes and number of rows. Actually, are > they all =E2=80=93 tables, or some of them =E2=80=93 views? > > > > Igor > > > > > > Sorry, I misstated the parameter to change. > > It is work_mem (not temp_buffers) you should try to increase to 6GB. > > > > Igor > > > > > --=20 Charles Nadeau Ph.D. http://charlesnadeau.blogspot.com/ --001a114fc71008b548055448cb8b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Igor,

Initially temp_buffer = was left to its default value (8MB). Watching the content of the directory = that stores the temporary files, I found that I need at most 21GB of tempor= ary files space. Should I set temp_buffer to 21GB?
Here is the ex= plain you requested with work_mem set to 6GB:
flows=3D# set work_mem=3D'= 6GB';
SET
flows=3D# explain (analyze, buffers) SELE= CT DISTINCT
=C2=A0 =C2=A0srcaddr,
=C2=A0 =C2=A0dstaddr,=
=C2=A0 =C2=A0dstport,
=C2=A0 =C2=A0COUNT(*) AS convers= ation,
=C2=A0 =C2=A0SUM(doctets) / 1024 / 1024 AS mbytes
FROM
=C2=A0 =C2=A0flowscompact,
=C2=A0 =C2=A0mynetwor= ks
WHERE
=C2=A0 =C2=A0mynetworks.ipaddr >>=3D flo= wscompact.srcaddr
=C2=A0 =C2=A0AND dstaddr IN
=C2=A0 = =C2=A0(
=C2=A0 =C2=A0 =C2=A0 SELECT
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0dstaddr
=C2=A0 =C2=A0 =C2=A0 FROM
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0dstexterne
=C2=A0 =C2=A0)
GROUP BY
=C2=A0 =C2=A0srcaddr,
=C2=A0 =C2=A0dstaddr,<= /div>
=C2=A0 =C2=A0dstport
ORDER BY
=C2=A0 =C2=A0mb= ytes DESC LIMIT 50;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=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=A0Limit =C2=A0(cost=3D48135680.07..48135680.22 rows=3D50 width= =3D52) (actual time=3D2227678.196..2227678.223 rows=3D50 loops=3D1)
=C2=A0 =C2=A0Buffers: shared hit=3D728798038 read=3D82974833, temp read= =3D381154 written=3D381154
=C2=A0 =C2=A0-> =C2=A0Unique =C2=A0= (cost=3D48135680.07..48143613.62 rows=3D2644514 width=3D52) (actual time=3D= 2227678.194..2227678.217 rows=3D50 loops=3D1)
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0Buffers: shared hit=3D728798038 read=3D82974833, temp read= =3D381154 written=3D381154
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->= ; =C2=A0Sort =C2=A0(cost=3D48135680.07..48137002.33 rows=3D2644514 width=3D= 52) (actual time=3D2227678.192..2227678.202 rows=3D50 loops=3D1)
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Sort Key: (((sum(flo= ws.doctets) / '1024'::numeric) / '1024'::numeric)) DESC, fl= ows.srcaddr, flows.dstaddr, flows.dstport, (count(*))
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Sort Method: quicksort =C2=A0M= emory: 654395kB
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Buffers: shared hit=3D728798038 read=3D82974833, temp read=3D381154 w= ritten=3D381154
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0-> =C2=A0GroupAggregate =C2=A0(cost=3D48059426.65..48079260.50 row= s=3D2644514 width=3D52) (actual time=3D2167909.030..2211446.192 rows=3D5859= 671 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0Group Key: flows.srcaddr, flows.dstaddr, flows.d= stport
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D728798038 read=3D82974833, temp r= ead=3D381154 written=3D381154
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Sort =C2=A0(cost=3D480= 59426.65..48060748.90 rows=3D2644514 width=3D20) (actual time=3D2167896.815= ..2189107.205 rows=3D91745640 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=A0So= rt Key: flows.srcaddr, flows.dstaddr, flows.dstport
=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0Sort Method: external merge =C2=A0Disk: 3049216kB
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D728798038 read=3D82974833, temp r= ead=3D381154 written=3D381154
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0G= ather =C2=A0(cost=3D30060688.07..48003007.07 rows=3D2644514 width=3D20) (ac= tual time=3D1268989.000..1991357.232 rows=3D91745640 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=A0Workers Planned: 12
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Workers Launched: 12
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D72879803= 7 read=3D82974833
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&= gt; =C2=A0Hash Semi Join =C2=A0(cost=3D30059688.07..47951761.31 rows=3D2203= 76 width=3D20) (actual time=3D1268845.181..2007864.725 rows=3D7057357 loops= =3D13)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=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: (flows.dstaddr =3D flows_1.dstaddr)
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared = hit=3D728795193 read=3D82974833
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=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=3D0.0= 3..17891246.86 rows=3D220376 width=3D20) (actual time=3D0.207..723790.283 r= ows=3D37910370 loops=3D13)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D59069= 2229 read=3D14991777
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Parallel Seq Scan = on flows =C2=A0(cost=3D0.00..16018049.14 rows=3D55094048 width=3D20) (actua= l time=3D0.152..566179.117 rows=3D45371630 loops=3D13)
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D860990 read=3D14991777
<= div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=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 mynetworks_ipaddr_idx on= mynetworks =C2=A0(cost=3D0.03..0.03 rows=3D1 width=3D8) (actual time=3D0.0= 02..0.002 rows=3D1 loops=3D589831190)
=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=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: (ipaddr >>=3D (flows.srcaddr)::ip4r)
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=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: 0
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0Buffers: shared hit=3D589831203
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=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=3D30059641.47..30059641.47 rows=3D13305 width=3D4) (actual time=3D126= 8811.101..1268811.101 rows=3D3803508 loops=3D13)
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Bu= ckets: 4194304 (originally 16384) =C2=A0Batches: 1 (originally 1) =C2=A0Mem= ory Usage: 166486kB
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D138102964 re= ad=3D67983056
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0HashAggregate =C2=A0(cost= =3D30059561.64..30059601.56 rows=3D13305 width=3D4) (actual time=3D1265248.= 165..1267432.083 rows=3D3803508 loops=3D13)
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0Group Key: flows_1.dstaddr
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0Buffers: shared hit=3D138102964 read=3D67983056
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=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 Anti Join =C2=A0(cost=3D= 0.00..29729327.92 rows=3D660467447 width=3D4) (actual time=3D0.389..1201072= .707 rows=3D125838232 loops=3D13)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Join Filter: (mynetworks_1.ipaddr >> (flows_1= .dstaddr)::ip4r)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=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 Join Filter: 503353617
=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D138102964 read=3D67= 983056
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&= gt; =C2=A0Seq Scan on flows flows_1 =C2=A0(cost=3D0.00..17836152.73 rows=3D= 661128576 width=3D4) (actual time=3D0.322..343152.274 rows=3D589831190 loop= s=3D13)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D138102915 read=3D67983056
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Mate= rialize =C2=A0(cost=3D0.00..1.02 rows=3D4 width=3D8) (actual time=3D0.000..= 0.000 rows=3D2 loops=3D7667805470)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D13
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=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 mynetworks mynetworks_1 =C2=A0(cost=3D0.0= 0..1.01 rows=3D4 width=3D8) (actual time=3D0.006..0.007 rows=3D4 loops=3D13= )
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D13
=C2=A0= Planning time: 0.941 ms
=C2=A0Execution time: 2228345.171 ms
(48 rows)

With a work_mem at 6G= B, I noticed that for the first 20 minutes the query was running, the i/o w= ait was much lower, hovering aroun 3% then it jumped 45% until almost the e= nd of the query.=C2=A0

flowscompact and dstexterne= are actually views. I use views to simplify query writing and to "abs= tract" queries that are use often in other queries. flowscompact is a = view built on table flows (having about 590 million rows), it only keeps th= e most often used fields.
flows=3D# \d+ flowscompact;
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 V= iew "public.flowscompact"
=C2=A0 Column =C2=A0 | =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 Type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Modi= fiers | Storage | Description=C2=A0
-----------+-----------------= ---------+-----------+---------+-------------
=C2=A0flow_id =C2= =A0 | bigint =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | plain =C2=A0 |=C2=A0
=C2= =A0sysuptime | bigint =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | plain =C2=A0 |=C2=A0
=C2=A0exaddr =C2=A0 =C2=A0| ip4 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 | plain =C2=A0 |=C2=A0
=C2=A0dpkts =C2=A0 =C2=A0 | integer = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | plain =C2=A0 |=C2=A0
=C2=A0doctets =C2= =A0 | bigint =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | plain =C2=A0 |=C2=A0
=C2= =A0first =C2=A0 =C2=A0 | bigint =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | plain =C2=A0 |= =C2=A0
=C2=A0last =C2=A0 =C2=A0 =C2=A0| bigint =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 | plain =C2=A0 |=C2=A0
=C2=A0srcaddr =C2=A0 | ip4 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | plain =C2=A0 |=C2=A0
=C2=A0dsta= ddr =C2=A0 | ip4 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | plain =C2=A0 |= =C2=A0
=C2=A0srcport =C2=A0 | integer =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | p= lain =C2=A0 |=C2=A0
=C2=A0dstport =C2=A0 | integer =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 | plain =C2=A0 |=C2=A0
=C2=A0prot =C2=A0 =C2=A0 =C2=A0= | smallint =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 | plain =C2=A0 |=C2=A0
=C2=A0tos =C2= =A0 =C2=A0 =C2=A0 | smallint =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | plain =C2=A0 |=C2=A0
=C2=A0tcp_flags | smallint =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | plain =C2=A0 |=C2=A0
=C2=A0timestamp | timestamp with time zone | =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 | plain =C2=A0 |=C2=A0
View definition:
= =C2=A0SELECT flowstimestamp.flow_id,
=C2=A0 =C2=A0 flowstimestamp= .sysuptime,
=C2=A0 =C2=A0 flowstimestamp.exaddr,
=C2=A0= =C2=A0 flowstimestamp.dpkts,
=C2=A0 =C2=A0 flowstimestamp.doctet= s,
=C2=A0 =C2=A0 flowstimestamp.first,
=C2=A0 =C2=A0 fl= owstimestamp.last,
=C2=A0 =C2=A0 flowstimestamp.srcaddr,
=C2=A0 =C2=A0 flowstimestamp.dstaddr,
=C2=A0 =C2=A0 flowstimest= amp.srcport,
=C2=A0 =C2=A0 flowstimestamp.dstport,
=C2= =A0 =C2=A0 flowstimestamp.prot,
=C2=A0 =C2=A0 flowstimestamp.tos,=
=C2=A0 =C2=A0 flowstimestamp.tcp_flags,
=C2=A0 =C2=A0 = flowstimestamp."timestamp"
=C2=A0 =C2=A0FROM flowstimes= tamp;
mynetworks is a table having one column and 4 = rows; it contains a list of our network networks:
flows=3D# select * from my= networks;
=C2=A0 =C2=A0 =C2=A0ipaddr =C2=A0 =C2=A0=C2=A0
----------------
=
(4 row)
flows=3D# \d+ mynetworks;
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Table "public.= mynetworks"
=C2=A0Column | Type | Modifiers | Storage | Stat= s target | Description=C2=A0
--------+------+-----------+--------= -+--------------+-------------
=C2=A0ipaddr | ip4r | =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | plain =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0|=C2=A0
Indexes:
=C2=A0 =C2=A0 "m= ynetworks_ipaddr_idx" gist (ipaddr)
dstexterne = is a view listing all the destination IPv4 addresses not inside our network= ; it has one column and 3.8 million rows.
flows=3D# \d+ dstexterne;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 View "public.dstext= erne"
=C2=A0Column =C2=A0| Type | Modifiers | Storage | Desc= ription=C2=A0
---------+------+-----------+---------+------------= -
=C2=A0dstaddr | ip4 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = | plain =C2=A0 |=C2=A0
View definition:
=C2=A0SELECT DI= STINCT flowscompact.dstaddr
=C2=A0 =C2=A0FROM flowscompact
<= div>=C2=A0 =C2=A0 =C2=A0LEFT JOIN mynetworks ON mynetworks.ipaddr >> = flowscompact.dstaddr::ip4r
=C2=A0 WHERE mynetworks.ipaddr IS NULL= ;
Thanks!

Charles

On Wed, Jul 12,= 2017 at 6:39 PM, Igor Neyman <ineyman@perceptron.com> = wrote:

=C2=A0

=C2=A0

From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance= -owner@postgresql.org] On Behalf Of Charles Nadeau
Sent: Wednesday, July 12, 2017 6:05 AM
To: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Very poor read performance, query independent=

=C2=A0

=C2=A0

flows=3D# explain (analyze, buffers) SELECT DISTINCT=

flows-# =C2=A0 =C2=A0srcaddr,

flows-# =C2=A0 =C2=A0dstaddr,

flows-# =C2=A0 =C2=A0dstport,

flows-# =C2=A0 =C2=A0COUNT(*) AS conversation,

flows-# =C2=A0 =C2=A0SUM(doctets) / 1024 / 1024 AS m= bytes=C2=A0

flows-# FROM

flows-# =C2=A0 =C2=A0flowscompact,

flows-# =C2=A0 =C2=A0mynetworks=C2=A0<= /p>

flows-# WHERE

flows-# =C2=A0 =C2=A0mynetworks.ipaddr >>=3D f= lowscompact.srcaddr=C2=A0

flows-# =C2=A0 =C2=A0AND dstaddr IN=C2=A0<= /u>

flows-# =C2=A0 =C2=A0(

flows(# =C2=A0 =C2=A0 =C2=A0 SELECT

flows(# =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0dstaddr=C2= =A0

flows(# =C2=A0 =C2=A0 =C2=A0 FROM

flows(# =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0dstexterne=

flows(# =C2=A0 =C2=A0)

flows-# GROUP BY

flows-# =C2=A0 =C2=A0srcaddr,

flows-# =C2=A0 =C2=A0dstaddr,

flows-# =C2=A0 =C2=A0dstport=C2=A0

flows-# ORDER BY

flows-# =C2=A0 =C2=A0mbytes DESC LIMIT 50;=

LOG: =C2=A0temporary file: path "pg_tblspc/3623= 8/PG_9.6_201608131/pgsql_tmp/pgsql_tmp14573.6", size 1073741= 824

LOG: =C2=A0temporary file: path "pg_tblspc/3623= 8/PG_9.6_201608131/pgsql_tmp/pgsql_tmp14573.7", size 1073741= 824

LOG: =C2=A0temporary file: path "pg_tblspc/3623= 8/PG_9.6_201608131/pgsql_tmp/pgsql_tmp14573.8", size 6396968= 96

LOG: =C2=A0duration: 2765020.327 ms =C2=A0statement:= explain (analyze, buffers) SELECT DISTINCT

=C2=A0 =C2=A0srcaddr,

=C2=A0 =C2=A0dstaddr,

=C2=A0 =C2=A0dstport,

=C2=A0 =C2=A0COUNT(*) AS conversation,=

=C2=A0 =C2=A0SUM(doctets) / 1024 / 1024 AS mbytes=C2= =A0

FROM

=C2=A0 =C2=A0flowscompact,

=C2=A0 =C2=A0mynetworks=C2=A0

WHERE

=C2=A0 =C2=A0mynetworks.ipaddr >>=3D flowscomp= act.srcaddr=C2=A0

=C2=A0 =C2=A0AND dstaddr IN=C2=A0

=C2=A0 =C2=A0(

=C2=A0 =C2=A0 =C2=A0 SELECT

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0dstaddr=C2=A0

=C2=A0 =C2=A0 =C2=A0 FROM

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0dstexterne<= u>

=C2=A0 =C2=A0)

GROUP BY

=C2=A0 =C2=A0srcaddr,

=C2=A0 =C2=A0dstaddr,

=C2=A0 =C2=A0dstport=C2=A0

ORDER BY

=C2=A0 =C2=A0mbytes DESC LIMIT 50;

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY 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=A0Limit =C2=A0(cost=3D37762321.83..37762321.98 rows=3D50 width=3D52) (actual time=3D2764548.863..2764548.891 rows=3D50= loops=3D1)

=C2=A0 =C2=A0Buffers: shared hit=3D1116590560 read= =3D15851133, temp read=3D340244 written=3D340244

=C2=A0 =C2=A0I/O Timings: read=3D5323746.860<= u>

=C2=A0 =C2=A0-> =C2=A0Unique =C2=A0(cost=3D377623= 21.83..37769053.57 rows=3D2243913 width=3D52) (actual time=3D2764548.8= 61..2764548.882 rows=3D50 loops=3D1)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hi= t=3D1116590560 read=3D15851133, temp read=3D340244 written=3D340244<= u>

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0I/O Timings: read= =3D5323746.860

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Sort = =C2=A0(cost=3D37762321.83..37763443.79 rows=3D2243913 width=3D52) (act= ual time=3D2764548.859..2764548.872 rows=3D50 loops=3D1)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Sort Key: (((sum(flows.doctets) / '1024'::numeric) / '1024&#= 39;::numeric)) DESC, flows.srcaddr, flows.dstaddr, flows.dstport, (count(*)= )

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Sort Method: quicksort =C2=A0Memory: 563150kB

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Buffers: shared hit=3D1116590560 read=3D15851133, temp read=3D340244 wri= tten=3D340244

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0I/O Timings: read=3D5323746.860

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0-> =C2=A0GroupAggregate =C2=A0(cost=3D37698151.34..37714980.68 r= ows=3D2243913 width=3D52) (actual time=3D2696721.610..2752109.551 rows=3D46= 91734 loops=3D1)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Group Key: flows.srcaddr, flows.dstaddr, flows.dstp= ort

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D1116590560 read=3D15851133, t= emp read=3D340244 written=3D340244

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0I/O Timings: read=3D5323746.860

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Sort =C2=A0(cost=3D37698151.34..3769927= 3.29 rows=3D2243913 width=3D20) (actual time=3D2696711.428..2732781.70= 5 rows=3D81896988 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=A0Sort Key: flows.srcaddr, flows= .dstaddr, flows.dstport

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Sort Method: external merge = =C2=A0Disk: 2721856kB

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D11165905= 60 read=3D15851133, temp read=3D340244 written=3D340244

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0I/O Timings: read=3D5323746.86= 0

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Gather =C2=A0(cost= =3D19463936.00..37650810.19 rows=3D2243913 width=3D20) (actual time=3D= 1777219.713..2590530.887 rows=3D81896988 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=A0Workers P= lanned: 9

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Workers L= aunched: 9

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: = shared hit=3D1116590559 read=3D15851133

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0I/O Timin= gs: read=3D5323746.860

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=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 Semi Join =C2=A0(cost=3D19462936.00..37622883.23 rows=3D249324= width=3D20) (actual time=3D1847579.360..2602039.780 rows=3D8189699 loops= =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=A0Hash Cond: (flows.dstaddr =3D flows_1.dstaddr)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0Buffers: shared hit=3D1116588309 read=3D15851133=

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0I/O Timings: read=3D5323746.860

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=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=3D0.03..18159012.30 rows= =3D249324 width=3D20) (actual time=3D1.562..736556.583 rows=3D45499045 loop= s=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=A0Buffers: shared hit=3D996551813 read=3D15= 851133

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0I/O Timings: read=3D5323746.860=

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Parallel Seq Scan on flows = =C2=A0(cost=3D0.00..16039759.79 rows=3D62330930 width=3D20) (actual time=3D= 1.506..547485.066 rows=3D54155970 loops=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=A0Buffers: shared hit= =3D1634 read=3D15851133

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0I/O Timings: read=3D= 5323746.860

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=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 mynetwo= rks_ipaddr_idx on mynetworks =C2=A0(cost=3D0.03..0.03 rows=3D1 width=3D8) (= actual time=3D0.002..0.002 rows=3D1 loops=3D541559704)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=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: (ipaddr = >>=3D (flows.srcaddr)::ip4r)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=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: 599714= 74

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit= =3D996550152

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=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=3D19462896.74..19462896.74 r= ows=3D11210 width=3D4) (actual time=3D1847228.894..1847228.894 rows=3D30997= 98 loops=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=A0Buckets: 4194304 (originally 16384) =C2= =A0Batches: 1 (originally 1) =C2=A0Memory Usage: 141746kB

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D120036496=

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0HashAggregate =C2=A0(cost=3D1= 9462829.48..19462863.11 rows=3D11210 width=3D4) (actual time=3D1230049= .015..1845955.764 rows=3D3099798 loops=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=A0Group Key: flows_1.d= staddr

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit= =3D120036496

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=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 L= oop Anti Join =C2=A0(cost=3D0.12..19182620.78 rows=3D560417390 width=3D4) (= actual time=3D0.084..831832.333 rows=3D113420172 loops=3D10)<= /p>

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Join Filter: (mynetworks_1.ipaddr >> (flows_1.dstaddr)::ip4r)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=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 Join Filter: 453681377

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Buffers: shared hit=3D120036496

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=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 flows_srcaddr_dstaddr_idx on flows flo= ws_1 =C2=A0(cost=3D0.12..9091067.70 rows=3D560978368 width=3D4) (actual tim= e=3D0.027..113052.437 rows=3D541559704 loops=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=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Heap Fetches: 91

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D120036459

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0-> =C2=A0Materialize =C2=A0(cost=3D0.00..1.02 rows=3D4 width=3D8) (ac= tual time=3D0.000..0.000 rows=3D2 loops=3D5415597040)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=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=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Seq Scan on mynetworks mynetworks_1 =C2= =A0(cost=3D0.00..1.01 rows=3D4 width=3D8) (actual time=3D0.007..0.008 rows= =3D4 loops=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=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D10

=C2=A0Planning time: 6.689 ms

=C2=A0Execution time: 2764860.853 ms

(58 rows)

=C2=A0

Regarding "Also using dstat I can see that iowa= it time is at about 25%", I don't think the server was doing anyth= ing else. If it is important, I can repeat the benchmarks.

Thanks!

=C2=A0

Charles

=C2=A0

Charles,

=C2=A0

In your original posting I couldn=E2= =80=99t find what value you set for temp_buffers.

Considering you have plenty of RAM, t= ry setting temp_buffers=3D=E2=80=996GB=E2=80=99 and then run =E2=80=98expla= in (analyze, buffers) select=E2=80=A6=E2=80=99 in the same session. This sh= ould alleviate =E2=80=9Cdisk sort=E2=80=99 problem.

=C2=A0

Also, could you post the structure of flowscompact, mynetworks, and dstextern tables with all the indexes and number of rows. Actually, are they all =E2= =80=93 tables, or some of them =E2=80=93 views?

=C2=A0

Igor

= =C2=A0

=C2=A0

Sorry, I misstated the pa= rameter to change.

It is work_mem (not temp_buffers) you= should try to increase to 6GB.

=C2=A0

Igor

=C2=A0

=C2=A0




--
Charles Nadeau Ph.D= .
http:= //charlesnadeau.blogspot.com/
--001a114fc71008b548055448cb8b--