Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 14ADB2E002E for ; Mon, 31 Mar 2008 09:04:14 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 51995-01 for ; Mon, 31 Mar 2008 09:04:13 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from rn-out-0910.google.com (rn-out-0910.google.com [64.233.170.190]) by developer.postgresql.org (Postfix) with ESMTP id E7FD22E002C for ; Mon, 31 Mar 2008 09:04:12 -0300 (ADT) Received: by rn-out-0910.google.com with SMTP id s46so447526rnb.3 for ; Mon, 31 Mar 2008 05:04:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=Qnd3kJEj9Ft46IUlEmmNxavA7egyZ7dGm89sI+IyWyo=; b=E/dXJ7wNrGlrUFcK7q0UMpOR/BALtcszl1J569B7AiZFWSLFMijoVZvGlyEnmw48lP0RdpOS8snLCnSXQ2636KoADfRdiNcdDRh0VMXqSIWQ/gCfSutd4nIwgX7ENQSYMnxb4CvqEgrFAiP3xBtv3etXnq95cU63vUcCFezFV9Q= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=message-id:date:from:to:subject:mime-version:content-type; b=o+iB4fMh5VFZSekglZdLhwV4N4ysRen3ILicgMURdEYGmGLjn66jdwZ0gXIgvaJVj/5GRAzct08pXcUMH0kRqhoQND4gOLMS8bwdmdQrV0u1B92cjHp/qFFYzhivJyrzKzJU/9emCpRR3pP2qTEL83Zo9zKdytLMr5pSnTDR49c= Received: by 10.150.150.3 with SMTP id x3mr3302841ybd.23.1206964628903; Mon, 31 Mar 2008 04:57:08 -0700 (PDT) Received: by 10.151.14.4 with HTTP; Mon, 31 Mar 2008 04:57:08 -0700 (PDT) Message-ID: Date: Mon, 31 Mar 2008 13:57:08 +0200 From: "Frits Hoogland" To: pgsql-performance@postgresql.org Subject: optimizing query performance MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_20296_29624923.1206964628883" X-Archive-Number: 200803/528 X-Sequence-Number: 29482 ------=_Part_20296_29624923.1206964628883 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi! I've got the following statement: SELECT DISTINCT sub.os, COUNT(sub.os) as total FROM ( SELECT split_part(system.name, ' ', 1) as os FROM system, attacks WHERE 1 = 1 AND timestamp >= 1205708400 AND timestamp <= 1206313200 AND attacks.source = system.ip_addr AND NOT attacks.source IN ( SELECT exclusion FROM org_excl WHERE orgid=2 ) ) as sub GROUP BY sub.os ORDER BY total DESC LIMIT 5 which has the following execution plan: Limit (cost=1831417.45..1831417.48 rows=5 width=34) (actual time= 1599.915..1599.925 rows=3 loops=1) -> Unique (cost=1831417.45..1831417.75 rows=41 width=34) (actualtime= 1599.912..1599.918 rows=3 loops=1) -> Sort (cost=1831417.45..1831417.55 rows=41 width=34) (actual time=1599.911..1599.913 rows=3 loops=1) Sort Key: count(split_part(("system".name)::text, ''::text, 1)), split_part(("system".name)::text, ' '::text, 1) -> HashAggregate (cost=1831415.63..1831416.35 rows=41 width=34) (actual time=1599.870..1599.876 rows=3 loops=1) -> Nested Loop (cost=23.77..1829328.68 rows=417390 width=34) (actual time=0.075..1474.260 rows=75609 loops=1) -> Index Scan using index_attacks_timestamp on attacks (cost=23.77..2454.92 rows=36300 width=11) (actual time= 0.041..137.045 rows=72380 loops=1) Index Cond: (("timestamp" >= 1205708400) AND ("timestamp" <= 1206313200)) Filter: (NOT (hashed subplan)) SubPlan -> Seq Scan on org_excl (cost=0.00..23.75rows=6 width=32) (actual time= 0.014..0.014 rows=0 loops=1) Filter: (orgid = 2) -> Index Scan using ip_addr_name_index on "system" (cost=0.00..50.15 rows=12 width=45) (actual time=0.009..0.012rows=1 loops=72380) Index Cond: ("outer".source = "system".ip_addr) Total runtime: 1600.056 ms the NL (nested loop) is accountable for most of the total query time. Is there any way to avoid the NL and/or speed up the query? Thanks, Frits ------=_Part_20296_29624923.1206964628883 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi! I've got the following statement:

SELECT DISTINCT sub.os,
             COUNT(sub.os) as total
FROM (
            SELECT split_part(system.name, ' ', 1) as os
            FROM system, attacks
            WHERE 1 = 1
            AND  timestamp >= 1205708400
            AND timestamp <= 1206313200
            AND attacks.source = system.ip_addr
            AND NOT attacks.source IN (
                    SELECT exclusion
                    FROM org_excl
                    WHERE orgid=2
                     )
              ) as sub
             GROUP BY sub.os
             ORDER BY total DESC LIMIT 5

which has the following execution plan:

Limit  (cost=1831417.45..1831417.48 rows=5 width=34) (actual time=1599.915..1599.925 rows=3 loops=1)
  ->  Unique  (cost=1831417.45..1831417.75 rows=41 width=34) (actualtime=1599.912..1599.918 rows=3 loops=1)
        ->  Sort  (cost=1831417.45..1831417.55 rows=41 width=34) (actual time=1599.911..1599.913 rows=3 loops=1)
              Sort Key: count(split_part(("system".name)::text, ''::text, 1)), split_part(("system".name)::text, ' '::text, 1)
              ->  HashAggregate  (cost=1831415.63..1831416.35 rows=41 width=34) (actual time=1599.870..1599.876 rows=3 loops=1)
                    ->  Nested Loop  (cost=23.77..1829328.68 rows=417390 width=34) (actual time=0.075..1474.260 rows=75609 loops=1)
                          ->  Index Scan using index_attacks_timestamp on attacks  (cost=23.77..2454.92 rows=36300 width=11) (actual time=0.041..137.045 rows=72380 loops=1)
                                Index Cond: (("timestamp" >= 1205708400) AND ("timestamp" <= 1206313200))
                                Filter: (NOT (hashed subplan))
                                SubPlan
                                  ->  Seq Scan on org_excl (cost=0.00..23.75 rows=6 width=32) (actual time=0.014..0.014 rows=0 loops=1)
                                        Filter: (orgid = 2)
                          ->  Index Scan using ip_addr_name_index on "system"  (cost=0.00..50.15 rows=12 width=45) (actual time=0.009..0.012 rows=1 loops=72380)
                                Index Cond: ("outer".source = "system".ip_addr)

Total runtime: 1600.056 ms


the NL (nested loop) is accountable for most of the total query time. Is there any way to avoid the NL and/or speed up the query?

Thanks,

Frits
------=_Part_20296_29624923.1206964628883--