search

zclfly / collective-intelligence-framework

Automatically exported from code.google.com/p/collective-intelligence-framework
0 stars 0 forks source link

explain analyze restriction in the index #101

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
{{{
explain analyze SELECT DISTINCT ON (address,protocol,portlist) address, 
portlist, protocol, confidence, infrastructure_network.restriction, 
archive.uuid, archive.data
        FROM infrastructure_network
        LEFT JOIN apikeys_groups ON infrastructure_network.guid = apikeys_groups.guid
        LEFT JOIN archive ON infrastructure_network.uuid = archive.uuid
        WHERE 
            NOT EXISTS (SELECT uuid FROM domain where infrastructure_network.uuid = domain.uuid)
            AND NOT EXISTS (
                SELECT uuid FROM infrastructure_whitelist iw 
                WHERE 
                   iw.detecttime >= '2011-09-07 15:13:39+00'
                   AND iw.confidence >= 25 
                   AND infrastructure_network.address <<= iw.address 
                   LIMIT 1
            ) 
            AND detecttime >= '2011-09-07 15:13:39+00'
            AND infrastructure_network.confidence >= 40
            AND infrastructure_network.severity >= 'medium'
            AND infrastructure_network.restriction <= 'private'
            AND apikeys_groups.uuid = 'xxxxxxxxxxxxxx'
        ORDER BY address,protocol,portlist ASC, confidence DESC, severity DESC, restriction ASC, detecttime DESC, infrastructure_network.id DESC 
        LIMIT 50000;

Limit  (cost=4565.00..4565.85 rows=85 width=1554) (actual time=847.967..849.342 
rows=469 loops=1)
   ->  Unique  (cost=4565.00..4565.85 rows=85 width=1554) (actual time=847.965..848.880 rows=469 loops=1)
         ->  Sort  (cost=4565.00..4565.21 rows=85 width=1554) (actual time=847.963..848.209 rows=469 loops=1)
               Sort Key: infrastructure_network.address, infrastructure_network.protocol, infrastructure_network.portlist, infrastructure_network.confidence, infrastructure_network.severity, infrastructure_network.restriction, infrastructure_network.detecttime, infrastructure_network.id
               Sort Method:  quicksort  Memory: 617kB
               ->  Nested Loop Left Join  (cost=1.14..4562.27 rows=85 width=1554) (actual time=2.378..846.734 rows=469 loops=1)
                     ->  Nested Loop Anti Join  (cost=1.14..3893.07 rows=85 width=571) (actual time=2.366..842.584 rows=469 loops=1)
                           Join Filter: (infrastructure_network.uuid = public.domain.uuid)
                           ->  Nested Loop  (cost=1.14..152.83 rows=170 width=571) (actual time=2.306..825.429 rows=469 loops=1)
                                 Join Filter: (infrastructure_network.guid = apikeys_groups.guid)
                                 ->  Seq Scan on infrastructure_network  (cost=0.00..141.16 rows=234 width=587) (actual time=2.291..823.158 rows=469 loops=1)
                                       Filter: ((detecttime >= '2011-09-07 15:13:39+00'::timestamp with time zone) AND (confidence >= 40::double precision) AND (severity >= 'medium'::severity) AND (restriction <= 'private'::restriction) AND (NOT (SubPlan 1)))
                                       SubPlan 1
                                         ->  Limit  (cost=0.00..0.23 rows=1 width=16) (actual time=1.753..1.753 rows=0 loops=469)
                                               ->  Seq Scan on infrastructure_whitelist iw  (cost=0.00..323.95 rows=1384 width=16) (actual time=1.751..1.751 rows=0 loops=469)
                                                     Filter: ((detecttime >= '2011-09-07 15:13:39+00'::timestamp with time zone) AND (confidence >= 25::double precision) AND ($0 <<= address))
                                 ->  Materialize  (cost=1.14..1.16 rows=2 width=16) (actual time=0.001..0.002 rows=2 loops=469)
                                       ->  Seq Scan on apikeys_groups  (cost=0.00..1.14 rows=2 width=16) (actual time=0.005..0.007 rows=2 loops=1)
                                             Filter: (uuid = 'xxxxxxxxxxxxxxxxxx'::uuid)
                           ->  Append  (cost=0.00..43.77 rows=13 width=16) (actual time=0.033..0.033 rows=0 loops=469)
                                 ->  Index Scan using domain_uuid_key on domain  (cost=0.00..0.32 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=469)
                                       Index Cond: (infrastructure_network.uuid = public.domain.uuid)
                                 ->  Index Scan using domain_whitelist_uuid_key on domain_whitelist domain  (cost=0.00..3.74 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=469)
                                       Index Cond: (infrastructure_network.uuid = public.domain.uuid)
                                 ->  Index Scan using domain_fastflux_uuid_key on domain_fastflux domain  (cost=0.00..0.46 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=469)
                                       Index Cond: (infrastructure_network.uuid = public.domain.uuid)
                                 ->  Index Scan using domain_nameserver_uuid_key on domain_nameserver domain  (cost=0.00..8.43 rows=2 width=16) (actual time=0.003..0.003 rows=0 loops=469)
                                       Index Cond: (infrastructure_network.uuid = public.domain.uuid)
                                 ->  Index Scan using domain_malware_uuid_key on domain_malware domain  (cost=0.00..7.45 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=469)
                                       Index Cond: (infrastructure_network.uuid = public.domain.uuid)
                                 ->  Index Scan using domain_botnet_uuid_key on domain_botnet domain  (cost=0.00..8.60 rows=2 width=16) (actual time=0.003..0.003 rows=0 loops=469)
                                       Index Cond: (infrastructure_network.uuid = public.domain.uuid)
                                 ->  Index Scan using domain_passivedns_uuid_key on domain_passivedns domain  (cost=0.00..0.46 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=469)
                                       Index Cond: (infrastructure_network.uuid = public.domain.uuid)
                                 ->  Index Scan using domain_search_uuid_key on domain_search domain  (cost=0.00..0.32 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=469)
                                       Index Cond: (infrastructure_network.uuid = public.domain.uuid)
                                 ->  Index Scan using domain_phishing_uuid_key on domain_phishing domain  (cost=0.00..9.30 rows=2 width=16) (actual time=0.004..0.004 rows=0 loops=469)
                                       Index Cond: (infrastructure_network.uuid = public.domain.uuid)
                                 ->  Index Scan using domain_suspicious_uuid_key on domain_suspicious domain  (cost=0.00..4.70 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=469)
                                       Index Cond: (infrastructure_network.uuid = public.domain.uuid)
                     ->  Index Scan using archive_uuid_key on archive  (cost=0.00..7.86 rows=1 width=999) (actual time=0.005..0.006 rows=1 loops=469)
                           Index Cond: (infrastructure_network.uuid = archive.uuid)
 Total runtime: 849.818 ms
}}}

Original issue reported on code.google.com by saxjazm...@gmail.com on 10 Sep 2011 at 2:15

GoogleCodeExporter commented 9 years ago

Original comment by saxjazm...@gmail.com on 28 Dec 2011 at 2:13

GoogleCodeExporter commented 9 years ago 
wrapping this into another ticket.

Original comment by saxjazm...@gmail.com on 3 Jan 2012 at 7:01