Interesting question - Mailing list pgsql-hackers

Greetings,   I have made the following table(s),indexes,etc.  I wonder if there
is an index (or something else), I can create to make the query use a
"better" plan.  (not that it's slow at the moment, but as the table
grows...). 

Schema:

--
-- Selected TOC Entries:
--
\connect - neteng
--
-- TOC Entry ID 2 (OID 18735)
--
-- Name: attack_types_id_seq Type: SEQUENCE Owner: neteng
--

CREATE SEQUENCE "attack_types_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1  cache 1 ;

--
-- TOC Entry ID 3 (OID 18754)
--
-- Name: attack_types Type: TABLE Owner: neteng
--

CREATE TABLE "attack_types" ("id" integer DEFAULT nextval('"attack_types_id_seq"'::text) NOT NULL,"attack_type"
charactervarying(30),Constraint "attack_types_pkey" Primary Key ("id")
 
);

--
-- TOC Entry ID 4 (OID 18769)
--
-- Name: attack_db Type: TABLE Owner: neteng
--

CREATE TABLE "attack_db" ("attack_type" integer,"start_time" timestamp with time zone,"end_time" timestamp with time
zone,"src_router"inet,"input_int" integer,"output_int" integer,"src_as" integer,"src_ip" inet,"src_port"
integer,"dst_as"integer,"dst_ip" inet,"dst_port" integer,"protocol" integer,"tos" integer,"pr_flags" integer,"pkts"
bigint,"bytes"bigint,"next_hop" inet
 
);

--
-- TOC Entry ID 5 (OID 19897)
--
-- Name: protocols Type: TABLE Owner: neteng
--

CREATE TABLE "protocols" ("proto" integer,"proto_name" text
);

\connect - ler
--
-- TOC Entry ID 12 (OID 20362)
--
-- Name: "getattack_type" (integer) Type: FUNCTION Owner: ler
--

CREATE FUNCTION "getattack_type" (integer) RETURNS text AS 'SELECT CAST(attack_type as text) from attack_types
where id = $1;' LANGUAGE 'sql';

--
-- TOC Entry ID 13 (OID 20462)
--
-- Name: "format_port" (integer,integer) Type: FUNCTION Owner: ler
--

CREATE FUNCTION "format_port" (integer,integer) RETURNS text AS 'SELECT CASE    WHEN $1 = 1 THEN trim(to_char(($2 >> 8)
&255, ''09'')) || ''-'' ||                    trim(to_char($2 & 255,''09''))   WHEN $1 > 1 THEN
trim(to_char($2,''00009''))  END;' LANGUAGE 'sql';
 

--
-- TOC Entry ID 14 (OID 20508)
--
-- Name: "get_protocol" (integer) Type: FUNCTION Owner: ler
--

CREATE FUNCTION "get_protocol" (integer) RETURNS text AS 'SELECT proto_name FROM protocols   WHERE proto = $1;'
LANGUAGE'sql';
 

--
-- TOC Entry ID 15 (OID 20548)
--
-- Name: "format_protocol" (integer) Type: FUNCTION Owner: ler
--

CREATE FUNCTION "format_protocol" (integer) RETURNS text AS 'SELECT CASE    WHEN get_protocol($1) IS NOT NULL THEN
trim(get_protocol($1))  ELSE CAST($1 as text)   END;' LANGUAGE 'sql';
 

--
-- TOC Entry ID 10 (OID 20816)
--
-- Name: "plpgsql_call_handler" () Type: FUNCTION Owner: ler
--

CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS '/usr/local/pgsql/lib/plpgsql.so', 'plpgsql_call_handler'
LANGUAGE'C';
 

--
-- TOC Entry ID 11 (OID 20817)
--
-- Name: plpgsql Type: PROCEDURAL LANGUAGE Owner: 
--

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL';

--
-- TOC Entry ID 16 (OID 20831)
--
-- Name: "tcp_flags" (integer) Type: FUNCTION Owner: ler
--

CREATE FUNCTION "tcp_flags" (integer) RETURNS text AS 'DECLARE flag ALIAS for $1;           ret text;
BEGIN     IF (flag & 128) = 128 THEN      ret := ''C'';    ELSE ret := '' '';    END IF;    IF (flag & 64) = 64 THEN
ret:= ret || ''E'';    ELSE ret := ret || '' '';    END IF;    IF (flag & 32) = 32 THEN ret := ret || ''U'';    ELSE
ret:= ret || '' '';    END IF;    IF (flag & 16) = 16 THEN ret := ret || ''A'';    ELSE ret := ret || '' '';    END IF;
  IF (flag & 8) = 8 THEN   ret := ret || ''P'';    ELSE ret := ret || '' '';    END IF;    IF (flag & 4) = 4 THEN   ret
:=ret || ''R'';    ELSE ret := ret || '' '';    END IF;    IF (flag & 2) = 2 THEN   ret := ret || ''S'';    ELSE ret :=
ret|| '' '';    END IF;    IF (flag & 1) = 1 THEN   ret := ret || ''F'';    ELSE ret := ret || '' '';    END IF;
RETURNret;
 
END;' LANGUAGE 'plpgsql';

--
-- TOC Entry ID 6 (OID 21918)
--
-- Name: exempt_ips Type: TABLE Owner: ler
--

CREATE TABLE "exempt_ips" ("ip" inet
);

--
-- TOC Entry ID 7 (OID 21918)
--
-- Name: exempt_ips Type: ACL Owner: 
--

REVOKE ALL on "exempt_ips" from PUBLIC;
GRANT ALL on "exempt_ips" to PUBLIC;
GRANT ALL on "exempt_ips" to "ler";

--
-- TOC Entry ID 17 (OID 22324)
--
-- Name: "format_flags" (integer,integer) Type: FUNCTION Owner: ler
--

CREATE FUNCTION "format_flags" (integer,integer) RETURNS text AS 'SELECT CASE    WHEN $1 = 6 THEN tcp_flags($2)   ELSE
''N/A''  END;' LANGUAGE 'sql';
 

\connect - neteng
--
-- TOC Entry ID 8 (OID 18769)
--
-- Name: "end_index" Type: INDEX Owner: neteng
--

CREATE  INDEX "end_index" on "attack_db" using btree ( "end_time" "timestamp_ops" );

--
-- TOC Entry ID 9 (OID 18769)
--
-- Name: "start_index" Type: INDEX Owner: neteng
--

CREATE  INDEX "start_index" on "attack_db" using btree ( "start_time" "timestamp_ops" );

--
-- TOC Entry ID 20 (OID 18802)
--
-- Name: "RI_ConstraintTrigger_18801" Type: TRIGGER Owner: neteng
--

CREATE CONSTRAINT TRIGGER "attack_type" AFTER INSERT OR UPDATE ON "attack_db"  FROM "attack_types" NOT DEFERRABLE
INITIALLYIMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('attack_type', 'attack_db', 'attack_types',
'UNSPECIFIED','attack_type', 'id');
 

--
-- TOC Entry ID 18 (OID 18804)
--
-- Name: "RI_ConstraintTrigger_18803" Type: TRIGGER Owner: neteng
--

CREATE CONSTRAINT TRIGGER "attack_type" AFTER DELETE ON "attack_types"  FROM "attack_db" NOT DEFERRABLE INITIALLY
IMMEDIATEFOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('attack_type', 'attack_db', 'attack_types',
'UNSPECIFIED','attack_type', 'id');
 

--
-- TOC Entry ID 19 (OID 18806)
--
-- Name: "RI_ConstraintTrigger_18805" Type: TRIGGER Owner: neteng
--

CREATE CONSTRAINT TRIGGER "attack_type" AFTER UPDATE ON "attack_types"  FROM "attack_db" NOT DEFERRABLE INITIALLY
IMMEDIATEFOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('attack_type', 'attack_db', 'attack_types',
'UNSPECIFIED','attack_type', 'id');
 

Query: EXPLAINSELECT to_char(start_time,'MM/DD/YY') as mmddyy,         to_char(start_time,'HH24:MI:SS') as hhmmss,
  getattack_type(attack_type) as type,         src_router as router,         input_int as ii,         output_int as oi,
       src_as as srcas,host(src_ip) || '/' || masklen(src_ip) || ':' ||          format_port(protocol,src_port) as
src_address,        dst_as as dstas,host(dst_ip) || '/' || masklen(dst_ip) || ':' ||
format_port(protocol,dst_port)as dst_address,         format_protocol(protocol) as prot,
tos,format_flags(protocol,pr_flags)as tcpflags,         pkts,bytes,         bytes/pkts as bytes_per_packet,
to_char(end_time,'MM/DD/YY')as end_mmddyy,         to_char(end_time,'HH24:MI:SS') as end_hhmmss,         next_hop
FROMattack_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)
fromexempt_ips)    ORDER BY bytes DESC;   ;
 


Explain Output:

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)
 


-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749