Re: Interesting question

Larry Rosenman <ler(at)lerctr(dot)org> writes:
> EXPLAIN
> SELECT ...
> FROM attack_db
> WHERE (start_time >= now() - '02:00:00'::interval OR
> end_time >= now() - '02:00:00'::interval)
> AND host(src_ip) NOT IN (select host(ip) from exempt_ips)
> AND host(dst_ip) NOT IN (select host(ip) from exempt_ips)
> ORDER BY bytes DESC;

> NOTICE: QUERY PLAN:

> Sort (cost=10870.77..10870.77 rows=5259 width=120)
> -> Seq Scan on attack_db (cost=0.00..10358.95 rows=5259 width=120)
> SubPlan
> -> Seq Scan on exempt_ips (cost=0.00..1.04 rows=4 width=12)
> -> Seq Scan on exempt_ips (cost=0.00..1.04 rows=4 width=12)

Making use of the indexes on start_time and end_time would be a good
thing. The reason it's not doing that is it doesn't think that the
expressions "now() - '02:00:00'::interval" reduce to constants. We
may have a proper solution for that by the time 7.2 comes out, but
in the meantime you could fake it with a function that hides the
noncacheable function and operator --- see previous discussions of
this identical issue in the archives.

The NOT INs are pretty ugly too (and do you need the host() conversion
there? Seems like a waste of cycles...). You might be able to live
with that if the timestamp condition will always be pretty restrictive,
but otherwise they'll be a no go. Consider NOT EXISTS with an index
on exempt_ips(ip).

regards, tom lane