X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id DB648D1B51F for ; Mon, 29 Sep 2003 14:02:53 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 89534-02 for ; Mon, 29 Sep 2003 11:02:29 -0300 (ADT) Received: from bast.unixathome.org (bast.unixathome.org [66.11.174.150]) by svr1.postgresql.org (Postfix) with ESMTP id D4BCDD1B51A for ; Mon, 29 Sep 2003 11:02:24 -0300 (ADT) Received: from wocker (wocker.unixathome.org [192.168.0.99]) by bast.unixathome.org (Postfix) with ESMTP id AC3633F52; Mon, 29 Sep 2003 10:02:06 -0400 (EDT) From: "Dan Langille" To: Jean-Luc Lachance Date: Mon, 29 Sep 2003 10:03:19 -0400 MIME-Version: 1.0 Subject: Re: Cc: Dan Langille , pgsql-sql@postgresql.org Message-ID: <3F780367.30103.33E0CA71@localhost> In-reply-to: <3F783BD8.48101D85@nsd.ca> X-mailer: Pegasus Mail for Windows (v4.02a) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/384 X-Sequence-Number: 15362 On 29 Sep 2003 at 10:04, Jean-Luc Lachance wrote: > Wouldn't: > > insert into r > select count(*) > from users > where date( lastlogin) > current_date - MaxDays * interval '' 1 day'' > group by date( lastlogin); > > be more efficient? Yes it would, by a factor of 5. freshports=# explain analyse select * from LoginCounts(3); QUERY PLAN ---------------------------------------------------------------------- -------------------------------------------- Function Scan on logincounts (cost=0.00..12.50 rows=1000 width=8) (actual time=1141.04..1141.06 rows=3 loops=1) Total runtime: 1141.13 msec (2 rows) freshports=# explain analyse select count(*) freshports-# from users freshports-# where date( lastlogin) > current_date - 3 * interval ' 1 day' freshports-# group by date( lastlogin); QUERY PLAN ---------------------------------------------------------------------- ----------------------------------------------------- Aggregate (cost=539.78..552.75 rows=173 width=8) (actual time=197.54..198.97 rows=3 loops=1) -> Group (cost=539.78..548.42 rows=1730 width=8) (actual time=196.97..198.43 rows=110 loops=1) -> Sort (cost=539.78..544.10 rows=1730 width=8) (actual time=196.95..197.39 rows=110 loops=1) Sort Key: date(lastlogin) -> Seq Scan on users (cost=0.00..446.75 rows=1730 width=8) (actual time=0.87..195.38 rows=110 loops=1) Filter: ((date(lastlogin))::timestamp without time zone > (('now'::text)::date - '3 days'::interval)) Total runtime: 199.33 msec (7 rows) freshports=# Thank you. -- Dan Langille : http://www.langille.org/