Re: Interesting question

Larry Rosenman <ler@lerctr.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