Hi all,

I'm looking for helping optimizing a query. It currently requires two
passes on the data per query, when I'd like to try and write it to only
require a single pass.

Here's the high level, it's parsing flow level network traffic and it's
interested in 4 fields:
src_ip, dst_ip, src_packets, dst_packets

In words:
'src_ip' sent 'src_packets' number of packets to 'dst_ip'
'dst_ip' sent 'dst_packets' number of packets to 'src_ip'

For every IP address, I want to count how many packets were sent to it.
This could come one of two ways, the IP is the source in the flow, and
it received dst_packets. Or, the IP is the destination in the flow, and
it received src_packets.

My current method is to split that into two queries and then take the
union. But, I was wondering if its possible to do this in one pass
through the data?

SELECT ip,sum(dst_packets)
FROM(
(SELECT dst_ip AS ip,sum(src_packets) AS dst_packets
FROM flows
WHERE interval='2005-02-01 00:00:00'
GROUP BY dst_ip)
UNION ALL
(SELECT src_ip AS ip,sum(dst_packets) AS dst_packets
FROM flows
WHERE interval='2005-02-01 00:00:00'
GROUP BY src_ip) )
AS stats
GROUP BY stats.ip
HAVING sum(dst_packets)>0
ORDER BY sum(dst_packets) DESC

- George

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 1 of 1 | next ›
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedMar 22, '08 at 7:11p
activeMar 22, '08 at 7:11p
posts1
users1
websitepostgresql.org
irc#postgresql

1 user in discussion

George Nychis: 1 post

People

Translate

site design / logo © 2022 Grokbase