Re: Interesting question

* Larry Rosenman <ler(at)lerctr(dot)org> [010518 20:25]:
> 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...).
(Oh, one point, this is 7.2devel...)

>
> 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" character varying(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;
> RETURN ret;
> 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 INITIALLY IMMEDIATE 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 IMMEDIATE FOR 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 IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('attack_type', 'attack_db', 'attack_types', 'UNSPECIFIED', 'attack_type', 'id');
>
> Query:
> EXPLAIN
> SELECT 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
> 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; ;
>
>
> 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(at)lerctr(dot)org
> US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

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