pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

two count columns?
3+ messages / 3 participants
[nested] [flat]

* two count columns?
@ 2006-02-17 23:56 Jan Danielsson <jan.danielsson@gmail.com>
  2006-02-18 00:47 ` Re: two count columns? Tom Lane <tgl@sss.pgh.pa.us>
  2006-02-18 00:51 ` Re: two count columns? Greg Stark <gsstark@mit.edu>
  0 siblings, 2 replies; 3+ messages in thread

From: Jan Danielsson @ 2006-02-17 23:56 UTC (permalink / raw)
  To: pgsql-sql

Hello all,

   I have written a small firewall, and sshd, logging system which uses
postgresql for storing the logs.

   The table that stores the firewall logs has, among other columns,
from_ip, port and a timestamp.

I want to construct a query which returns the columns:

from_ip | port 22 entries | total entries | last_access

I have managed to put this together:

select from_ip, count(from_ip) as entries, max(ts)::timestamp(0) as
last_access from log where to_port=22 and direction='in' group by from_ip

   So the only thing I'm missing is the total number of log entries
matching the from_ip, but that's where I'm stuck. My instinct is to try
to use subqueries:

select from_ip, count(from_ip) as entries, count(select * from log where
...) as tot_entries, max(ts)::timestamp(0) as last_access from log where
to_port=22 and direction='in' group by from_ip

..but count() doesn't take a subquery afaik, and how would I match the
from_ip in the inner select with the outer one? So I assume that
subqueries aren't the way yo go. Is there even a query to return those
columns in that configuration?

Thankful for any hints or tips.

-- 
Kind Regards,
Jan Danielsson
Te audire non possum. Musa sapientum fixa est in aure.

Attachments:

  signature.asc (application/pgp-signature, 188B) download

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: two count columns?
  2006-02-17 23:56 two count columns? Jan Danielsson <jan.danielsson@gmail.com>
@ 2006-02-18 00:47 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 2006-02-18 00:47 UTC (permalink / raw)
  To: Jan Danielsson <jan.danielsson@gmail.com>; +Cc: pgsql-sql

Jan Danielsson <jan.danielsson@gmail.com> writes:
>    So the only thing I'm missing is the total number of log entries
> matching the from_ip, but that's where I'm stuck. My instinct is to try
> to use subqueries:

> select from_ip, count(from_ip) as entries, count(select * from log where
> ...) as tot_entries, max(ts)::timestamp(0) as last_access from log where
> to_port=22 and direction='in' group by from_ip

> ...but count() doesn't take a subquery afaik, and how would I match the
> from_ip in the inner select with the outer one?

I think you want something like

  select ..., (select count(*) from log loginner where from_ip = log.from_ip)
  ... from log ...

You need to attach an alias to either the inner or the outer use of
"log" so that you can refer to the outer one from the inner SELECT.
Here I chose to alias the inner one, but it might be clearer to
alias the outer:

  select ..., (select count(*) from log where from_ip = logouter.from_ip)
  ... from log logouter ...

Note that anything like this is going to be pretty expensive if your log
table is large.  You might want to think about something involving
another layer of GROUP BY instead.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: two count columns?
  2006-02-17 23:56 two count columns? Jan Danielsson <jan.danielsson@gmail.com>
@ 2006-02-18 00:51 ` Greg Stark <gsstark@mit.edu>
  1 sibling, 0 replies; 3+ messages in thread

From: Greg Stark @ 2006-02-18 00:51 UTC (permalink / raw)
  To: Jan Danielsson <jan.danielsson@gmail.com>; +Cc: pgsql-sql


Jan Danielsson <jan.danielsson@gmail.com> writes:

> select from_ip, count(from_ip) as entries, count(select * from log where
> ...) as tot_entries, max(ts)::timestamp(0) as last_access from log where
> to_port=22 and direction='in' group by from_ip

select from_ip, 
       count(from_ip) as entries, 
       (select count(*) 
          from log as l
         where l.from_ip = log.from_ip
       ) as tot_entries,
       max(ts) as last_access
  from log
 where to_port=22
   and direction='in
 group by from_ip

expect it to be pretty slow though. For every from_ip it has to look up every
other entry with that from_ip.

> Thankful for any hints or tips.

There is a trick you could use to make it faster but it gets cumbersome and
pretty tricky to use when you're doing more than one thing at a time:

select from_ip
       sum(case when to_port=22 and direction='in' then 1 else 0 end) as entries,
       count(*) as tot_entries,
       max(case when to_port=22 and direction='in' then ts::timestamp(0) else null end) as last_access
  from log
 group by from_ip
 having entries > 0


Note that in either case you might want to look at ANALYZE results for the
query and try raising work_mem for this query using SET until you see the plan
using a hash aggregate. If it can use a hash aggregate for your query (more
likely for the first query than the second) without swapping it'll be faster
than sorting.

-- 
greg




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2006-02-18 00:51 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-17 23:56 two count columns? Jan Danielsson <jan.danielsson@gmail.com>
2006-02-18 00:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-18 00:51 ` Greg Stark <gsstark@mit.edu>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox