X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id D6F379DCBB3 for ; Fri, 24 Feb 2006 10:26:45 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 30908-02 for ; Fri, 24 Feb 2006 10:26:49 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 18D169DCBA4 for ; Fri, 24 Feb 2006 10:26:42 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 3144730BAC; Fri, 24 Feb 2006 15:26:47 +0100 (MET) From: "jcfischer" X-Newsgroups: pgsql.performance Subject: Re: nested query on last n rows of huge table Date: 24 Feb 2006 06:26:40 -0800 Organization: http://groups.google.com Lines: 46 Message-ID: <1140791200.791076.26200@t39g2000cwt.googlegroups.com> References: <1140790410.923941.261250@p10g2000cwp.googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Complaints-To: groups-abuse@google.com In-Reply-To: <1140790410.923941.261250@p10g2000cwp.googlegroups.com> User-Agent: G2/0.2 X-HTTP-UserAgent: Mozilla/5.0 (Macintosh; U; PPC Mac OS X Mach-O; de; rv:1.8.0.1) Gecko/20060111 Firefox/1.5.0.1,gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: t39g2000cwt.googlegroups.com; posting-host=195.186.55.199; posting-account=y8Yb_A0AAAAqh4VDa55BdDUMWbuhwj4N To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200602/449 X-Sequence-Number: 17435 sorry: Postgres 8.0.2 server. The EXPLAIN ANALYZE for the query looks like this: explain analyze select syslog.logs.eventtime,assets.hosts.name,syslog.processes.name as process from syslog.logs,assets.hosts,assets.ipaddrs,assets.macaddrs,syslog.processes where msg like '%session opened for user root%' and syslog.logs.assets_ipaddr_id = assets.ipaddrs.id and assets.ipaddrs.macaddr_id = assets.macaddrs.id and assets.macaddrs.host_id = assets.hosts.id and syslog.processes.id = syslog.logs.process_id and eventtime > timestamp '2006-02-24 15:05:00' Nested Loop (cost=0.00..328832.34 rows=2 width=254) (actual time=49389.924..49494.665 rows=45 loops=1) -> Nested Loop (cost=0.00..328826.32 rows=1 width=90) (actual time=49365.709..49434.500 rows=45 loops=1) -> Nested Loop (cost=0.00..328820.30 rows=1 width=90) (actual time=49327.211..49360.043 rows=45 loops=1) -> Nested Loop (cost=0.00..328814.27 rows=1 width=90) (actual time=49327.183..49344.281 rows=45 loops=1) -> Seq Scan on logs (cost=0.00..328809.04 rows=1 width=16) (actual time=49314.928..49331.451 rows=45 loops=1) Filter: (((msg)::text ~~ '%session opened for user root%'::text) AND (eventtime > '2006-02-24 15:05:00'::timestamp without time zone)) -> Index Scan using "pk_syslog.processes" on processes (cost=0.00..5.21 rows=1 width=82) (actual time=0.278..0.280 rows=1 loops=45) Index Cond: (processes.id = "outer".process_id) -> Index Scan using "pk_assets.ipaddrs" on ipaddrs (cost=0.00..6.01 rows=1 width=8) (actual time=0.344..0.346 rows=1 loops=45) Index Cond: ("outer".assets_ipaddr_id = ipaddrs.id) -> Index Scan using "pk_assets.macaddrs" on macaddrs (cost=0.00..6.01 rows=1 width=8) (actual time=1.648..1.650 rows=1 loops=45) Index Cond: ("outer".macaddr_id = macaddrs.id) -> Index Scan using "pk_assets.hosts" on hosts (cost=0.00..6.01 rows=1 width=172) (actual time=1.330..1.331 rows=1 loops=45) Index Cond: ("outer".host_id = hosts.id) Total runtime: 49494.830 ms