Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dX47R-00024c-7p for pgsql-performance@arkaria.postgresql.org; Mon, 17 Jul 2017 11:23:17 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dX47Q-0006tL-QT for pgsql-performance@arkaria.postgresql.org; Mon, 17 Jul 2017 11:23:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dX47P-0006so-NB for pgsql-performance@postgresql.org; Mon, 17 Jul 2017 11:23:15 +0000 Received: from mail-qk0-x22b.google.com ([2607:f8b0:400d:c09::22b]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dX47K-0005pS-DU for pgsql-performance@postgresql.org; Mon, 17 Jul 2017 11:23:15 +0000 Received: by mail-qk0-x22b.google.com with SMTP id p73so53984237qka.2 for ; Mon, 17 Jul 2017 04:23:09 -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=aJHnM+De86/twC/pR6YdEM967U46bhs7FYkE6qmgAAI=; b=hH7hpzPqOtf+K9qs27IzGJ0n+GcU1dJRtg/Hpz20W84lsk8npZSGF/tEC7BZaDedvv 58eiBTCDbmDkhx95O+VbhltKOdpKKUak6SolNw2tp5WXGtkxdnZ9dKTChxYOtpFFA3YN RXwPSrZr80xxjXZxskWzsRTNMfbvy2HsFf9iJwH6zbzQblRXkXpdtaggyhmJ2XdvIQjp aI6mRd+JTA/mD6yGZWlfWclRaZnU//6YKGgJOCJPf3qTq3VhzRSTYpEH+mwZ8sUPdytH fY4lSBiKCkVl2ogiC3ZAvGqJS3J18+klpAlUf/Os3m+ajQsreQo3mwc0CCg6qJn7KODa v+jQ== 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=aJHnM+De86/twC/pR6YdEM967U46bhs7FYkE6qmgAAI=; b=ucn4fVFZxfw1PPnWyOU9AR0ToHK6wRkBce4dQWXokVBfZmY0M94iORe+fdAaqD9KhN AzkbrDBQs7+ViROSgaPz5ZWKIqfq85UkYHWOANmqab36qCRVLbbYELT5+PZW9v4hwojv wpQGqeFvNbpp5RUnktgHJsKljrSOjScd6jZ07Xlu/W60b+9RFtfZ4z53EO1dIl2XNYSl 9V15AOKZ9smPJxTPpTewtQ59d5DQruwQM7mV5su1b4RVOfVk7+xtpwym3mGtNDGjhRmF SfqfucyI/tp6WCju7w5KpPEzYg4RNzd9Kh8tA8CwWciAgJtXZFY+PeO2Lxj4erNqzT7v Jw2A== X-Gm-Message-State: AIVw113/AQorctkfzqTTKH+CwIMwBe4iwP3IozPrgcTA8gwKsxZt7+PB hvavGHXCbevD5cK9vkrnN4xA0zo+Tg== X-Received: by 10.55.23.204 with SMTP id 73mr24603563qkx.157.1500290588121; Mon, 17 Jul 2017 04:23:08 -0700 (PDT) MIME-Version: 1.0 Received: by 10.12.157.11 with HTTP; Mon, 17 Jul 2017 04:22:47 -0700 (PDT) In-Reply-To: References: From: Charles Nadeau Date: Mon, 17 Jul 2017 13:22:47 +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="001a1147b0d8909ff10554819f90" 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 --001a1147b0d8909ff10554819f90 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Igor, The 1st clause of the where statement won't select addresses the same way as the one I wrote using the extension for IPv6 and IPv6 data types. flowstimestamp is a view: flows=3D# \d+ flowstimestamp View "public.flowstimestamp" Column | Type | Modifiers | Storage | Description -------------+--------------------------+-----------+---------+------------= - flow_id | bigint | | plain | unix_secs | bigint | | plain | unix_nsecs | bigint | | plain | sysuptime | bigint | | plain | exaddr | ip4 | | plain | dpkts | integer | | plain | doctets | bigint | | plain | first | bigint | | plain | last | bigint | | plain | engine_type | smallint | | plain | engine_id | smallint | | plain | srcaddr | ip4 | | plain | dstaddr | ip4 | | plain | nexthop | ip4 | | plain | input | integer | | plain | output | integer | | plain | srcport | integer | | plain | dstport | integer | | plain | prot | smallint | | plain | tos | smallint | | plain | tcp_flags | smallint | | plain | src_mask | smallint | | plain | dst_mask | smallint | | plain | src_as | integer | | plain | dst_as | integer | | plain | timestamp | timestamp with time zone | | plain | View definition: SELECT flows.flow_id, flows.unix_secs, flows.unix_nsecs, flows.sysuptime, flows.exaddr, flows.dpkts, flows.doctets, flows.first, flows.last, flows.engine_type, flows.engine_id, flows.srcaddr, flows.dstaddr, flows.nexthop, flows.input, flows.output, flows.srcport, flows.dstport, flows.prot, flows.tos, flows.tcp_flags, flows.src_mask, flows.dst_mask, flows.src_as, flows.dst_as, to_timestamp((flows.unix_secs + flows.unix_nsecs / 1000000000)::double precision) AS "timestamp" FROM flows; And it can use the indexes of flows: Indexes: "flows_pkey" PRIMARY KEY, btree (flow_id) "flows_dstaddr_dstport" btree (dstaddr, dstport) "flows_srcaddr_dstaddr_idx" btree (srcaddr, dstaddr) "flows_srcaddr_srcport" btree (srcaddr, srcport) "flows_srcport_dstport_idx" btree (srcport, dstport) Thanks! Charles On Fri, Jul 14, 2017 at 10:18 PM, Igor Neyman wrote: > > > > > *From:* pgsql-performance-owner@postgresql.org [mailto:pgsql-performance- > owner@postgresql.org] *On Behalf Of *Igor Neyman > *Sent:* Friday, July 14, 2017 3:13 PM > *To:* Charles Nadeau > > *Cc:* Jeff Janes ; pgsql-performance@postgresql.org > *Subject:* Re: [PERFORM] Very poor read performance, query independent > > > > *From:* Charles Nadeau [mailto:charles.nadeau@gmail.com > ] > *Sent:* Friday, July 14, 2017 11:35 AM > *To:* Igor Neyman > *Cc:* Jeff Janes ; pgsql-performance@postgresql.org > *Subject:* Re: [PERFORM] Very poor read performance, query independent > > > > 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=3D381= 154 > written=3D381154 > > -> Sort (cost=3D48135680.07..48137002.33 rows=3D2644514 width= =3D52) > (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= =3D5859671 > loops=3D1) > > Group Key: flows.srcaddr, flows.dstaddr, flows.dstpo= rt > > Buffers: shared hit=3D728798038 read=3D82974833, tem= p > read=3D381154 written=3D381154 > > -> Sort (cost=3D48059426.65..48060748.90 > rows=3D2644514 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=3D8297483= 3, > temp read=3D381154 written=3D381154 > > -> Gather (cost=3D30060688.07..48003007.07 > rows=3D2644514 width=3D20) (actual time=3D1268989.000..1991357.232 rows= =3D91745640 > loops=3D1) > > Workers Planned: 12 > > Workers Launched: 12 > > Buffers: shared hit=3D728798037 > read=3D82974833 > > -> 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=3D590692= 229 > 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=3D= 8) > (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=3D138102= 964 > 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) (act= ual > 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= =3D2 > 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 jumpe= d > 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 insid= e > 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::ip4r > > WHERE mynetworks.ipaddr IS NULL; > > Thanks! > > > > Charles > > > > Charles, > > > > Also, let=E2=80=99s try to simplify your query and see if it performs bet= ter. > > You are grouping by srcaddr, dstaddr, dstport, that makes DISTINCT not > needed. > > And after simplifying WHERE clause (let me know if the result is not what > you want), the query looks like: > > > > SELECT srcaddr, dstaddr, dstport, > > COUNT(*) AS conversation, > > SUM(doctets) / 1024 / 1024 AS mbytes > > FROM flowscompact > > WHERE srcaddr IN (SELECT ipaddr FROM mynetworks) > > AND dstaddr NOT IN (SELECT ipaddr FROM mynetworks) > > GROUP BY srcaddr, dstaddr, dstport > > ORDER BY mbytes DESC > > LIMIT 50; > > > > Now, you didn=E2=80=99t provide the definition of flowstimestamp table. > > If this table doesn=E2=80=99t have an index on (srcaddr, dstaddr, dstport= ) > creating one should help (I think). > > > > Igor > > > > > > > --=20 Charles Nadeau Ph.D. http://charlesnadeau.blogspot.com/ --001a1147b0d8909ff10554819f90 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Igor,

The 1st clause of the = where statement won't select addresses the same way as the one I wrote = using the extension for IPv6 and IPv6 data types.

= flowstimestamp is a view:
flows=3D# \d+ flowstimestamp=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 View "public.flowstimestamp"
=C2=A0 =C2= =A0Column =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Type =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | Modifiers | Storage | Description=C2=A0
-------------+--------------------------+-----------+---------+----------= ---
=C2=A0flow_id =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=A0unix_secs =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=A0unix_nsecs =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=A0s= ysuptime =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=A0exaddr =C2=A0 =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 =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 =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 =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 =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=A0engine_type | 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=A0engine_id =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=A0srcaddr =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=A0dstaddr =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=A0nexthop =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=A0input =C2=A0 =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=A0output =C2=A0 =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=A0srcport =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=A0dstport =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=A0p= rot =C2=A0 =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 =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 =C2=A0 | sm= allint =C2=A0 =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=A0src_mask = =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=A0dst_mask =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=A0src_as =C2=A0 =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=A0dst_as =C2=A0 =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= =A0timestamp =C2=A0 | timestamp with time zone | =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 | plain =C2=A0 |=C2=A0
View definition:
=C2= =A0SELECT flows.flow_id,
=C2=A0 =C2=A0 flows.unix_secs,
=C2=A0 =C2=A0 flows.unix_nsecs,
=C2=A0 =C2=A0 flows.sysuptime,
=C2=A0 =C2=A0 flows.exaddr,
=C2=A0 =C2=A0 flows.dpkts,
=C2=A0 =C2=A0 flows.doctets,
=C2=A0 =C2=A0 flows.first,<= /div>
=C2=A0 =C2=A0 flows.last,
=C2=A0 =C2=A0 flows.engine_ty= pe,
=C2=A0 =C2=A0 flows.engine_id,
=C2=A0 =C2=A0 flows.= srcaddr,
=C2=A0 =C2=A0 flows.dstaddr,
=C2=A0 =C2=A0 flo= ws.nexthop,
=C2=A0 =C2=A0 flows.input,
=C2=A0 =C2=A0 fl= ows.output,
=C2=A0 =C2=A0 flows.srcport,
=C2=A0 =C2=A0 = flows.dstport,
=C2=A0 =C2=A0 flows.prot,
=C2=A0 =C2=A0 = flows.tos,
=C2=A0 =C2=A0 flows.tcp_flags,
=C2=A0 =C2=A0= flows.src_mask,
=C2=A0 =C2=A0 flows.dst_mask,
=C2=A0 = =C2=A0 flows.src_as,
=C2=A0 =C2=A0 flows.dst_as,
=C2=A0= =C2=A0 to_timestamp((flows.unix_secs + flows.unix_nsecs / 1000000000)::dou= ble precision) AS "timestamp"
=C2=A0 =C2=A0FROM flows;<= /div>

And it can use the indexes of flows:
Ind= exes:
=C2=A0 =C2=A0 "flows_pkey" PRIMARY KEY, btree (fl= ow_id)
=C2=A0 =C2=A0 "flows_dstaddr_dstport" btree (dst= addr, dstport)
=C2=A0 =C2=A0 "flows_srcaddr_dstaddr_idx"= ; btree (srcaddr, dstaddr)
=C2=A0 =C2=A0 "flows_srcaddr_srcp= ort" btree (srcaddr, srcport)
=C2=A0 =C2=A0 "flows_srcp= ort_dstport_idx" btree (srcport, dstport)

Tha= nks!

Charles

On Fri, Jul 14, 2017 at 10:18 PM, Igor Neym= an <ineyman@perceptron.com> wrote:

=C2=A0

=C2=A0

From: pgsql-performance-owner@= postgresql.org [mailto:pgsql-performance-owner@postgresql.org= ] On Behalf Of Igor Neyman
Sent: Friday, July 14, 2017 3:13 PM
To: Charles Nadeau <charles.nadeau@gmail.com>


Cc: Jeff Janes <jeff.janes@gmail.com>; pgsql-performance@postgresql.org=
Subject: Re: [PERFORM] Very poor read performance, query independent=

=C2=A0

From: Charles Nadeau [mailto:charles.nadeau@gmail= .com]
Sent: Friday, July 14, 2017 11:35 AM
To: Igor Neyman <ineyman@perceptron.com>
Cc: Jeff Janes <jeff.janes@gmail.com>; pgsql= -performance@postgresql.org
Subject: Re: [PERFORM] Very poor read performance, query independent=

=C2=A0

Igor,

=C2=A0

Initially temp_buffer was left to its default value = (8MB). Watching the content of the directory that stores the temporary file= s, 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=

=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=

FROM

=C2=A0 =C2=A0flowscompact,

=C2=A0 =C2=A0mynetworks

WHERE

=C2=A0 =C2=A0mynetworks.ipaddr >>=3D flowscomp= act.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<= /u>

=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

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=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=3D= 82974833, temp read=3D381154 written=3D381154

=C2=A0 =C2=A0-> =C2=A0Unique =C2=A0(cost=3D481356= 80.07..48143613.62 rows=3D2644514 width=3D52) (actual time=3D2227678.1= 94..2227678.217 rows=3D50 loops=3D1)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hi= t=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=3D52) (act= ual 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(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: 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 writ= ten=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 r= ows=3D2644514 width=3D52) (actual time=3D2167909.030..2211446.192 rows=3D58= 59671 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=3D728798038 read=3D82974833, te= mp read=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=3D48059426.65..4806074= 8.90 rows=3D2644514 width=3D20) (actual time=3D2167896.815..2189107.20= 5 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=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: 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=3D72879803= 8 read=3D82974833, temp read=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=A0Gather =C2=A0(cost= =3D30060688.07..48003007.07 rows=3D2644514 width=3D20) (actual time=3D= 1268989.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 P= lanned: 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 L= aunched: 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=3D728798037 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= =A0Hash Semi Join =C2=A0(cost=3D30059688.07..47951761.31 rows=3D220376= 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<= /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=A0Nested Loop =C2=A0(cost=3D0.03..17891246.86 rows= =3D220376 width=3D20) (actual time=3D0.207..723790.283 rows=3D37910370 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=A0Buffers: shared hit=3D590692229 read=3D14= 991777

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=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) (actual time=3D= 0.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

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=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=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 r= ows=3D13305 width=3D4) (actual time=3D1268811.101..1268811.101 rows=3D38035= 08 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=A0Buckets: 4194304 (originally 16384) =C2= =A0Batches: 1 (originally 1) =C2=A0Memory 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 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=A0HashAggregate =C2=A0(cost=3D3= 0059561.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.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= =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 L= oop Anti Join =C2=A0(cost=3D0.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=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=A0Seq Scan on flows flows_1 =C2=A0(cost=3D0.00..17836152.73 ro= ws=3D661128576 width=3D4) (actual time=3D0.322..343152.274 rows=3D589831190= 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=A0Buffers: shared hit=3D138102915 read=3D67983056<= /u>

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=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=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.00..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=A0Planning time: 0.941 ms

=C2=A0Execution time: 2228345.171 ms

(48 rows)

=C2=A0

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 ar= oun 3% then it jumped 45% until almost the end of the query.=C2=A0

=C2=A0

flowscompact and dstexterne are actually views. I us= e 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;

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 View "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 | Modifiers | Storage | Desc= ription=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=A0dstaddr =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 | plain =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 | plai= n =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.doctets,<= /p>

=C2=A0 =C2=A0 flowstimestamp.first,

=C2=A0 =C2=A0 flowstimestamp.last,

=C2=A0 =C2=A0 flowstimestamp.srcaddr,<= /p>

=C2=A0 =C2=A0 flowstimestamp.dstaddr,<= /p>

=C2=A0 =C2=A0 flowstimestamp.srcport,<= /p>

=C2=A0 =C2=A0 flowstimestamp.dstport,<= /p>

=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 flowstimestamp;

mynetworks is a table having one column and 4 rows; = it contains a list of our network networks:

flows=3D# select * from mynetworks;

=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 | Stats ta= rget | 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 "mynetworks_ipaddr_idx" gist= (ipaddr)

dstexterne is a view listing all the destination IPv= 4 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 Vie= w "public.dstexterne"

=C2=A0Column =C2=A0| Type | Modifiers | Storage | De= scription=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 DISTINCT flowscompact.dstaddr=

=C2=A0 =C2=A0FROM flowscompact

=C2=A0 =C2=A0 =C2=A0LEFT JOIN mynetworks ON mynetwor= ks.ipaddr >> flowscompact.dstaddr::ip4r

=C2=A0 WHERE mynetworks.ipaddr IS NULL;

Thanks!

=C2=A0

Charles

=C2=A0

Charles,

=C2=A0

Also, let=E2=80=99s try to simplify y= our query and see if it performs better.

You are grouping by srcaddr, dstaddr,= dstport, that makes DISTINCT not needed.

And after simplifying WHERE clause (l= et me know if the result is not what you want), the query looks like:

=C2=A0

SELECT srcaddr, dstaddr, dstport,<= /u>

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

=C2=A0=C2=A0 SUM(doctets) / 1024 / 10= 24 AS mbytes

FROM flowscompact

WHERE srcaddr IN (SELECT ipadd= r FROM mynetworks)

=C2=A0=C2=A0=C2=A0=C2=A0 AND dstaddr = NOT IN (SELECT ipaddr FROM mynetworks)

GROUP BY srcaddr, dstaddr, dstport=

ORDER BY mbytes DESC

LIMIT 50;

=C2=A0

Now, you didn=E2=80=99t provid= e the definition of flowstimestamp table.

If this table doesn=E2=80=99t have an= index on (srcaddr, dstaddr, dstport) creating one should help (I think).

=C2=A0

Igor

=C2=A0

=C2=A0

=C2=A0




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