X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 612FF3A3E1A for ; Fri, 5 Nov 2004 18:09:31 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 08549-07 for ; Fri, 5 Nov 2004 18:09:28 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.197]) by svr1.postgresql.org (Postfix) with ESMTP id AC05A3A3D6B for ; Fri, 5 Nov 2004 18:09:27 +0000 (GMT) Received: by rproxy.gmail.com with SMTP id a41so97259rng for ; Fri, 05 Nov 2004 10:09:27 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding; b=gUELQeBjk24efWyMLNana4c8JBG28oed0gPVNRYMt7gvWiAoB2DMjHWPoS7+vBd7lRjI8lkAT2rYWlBFPI6Sojq8E+v2kP+h1GxnC9ckBUvqeoVMOJiZ3GzsiHW8Pbbxkfzlj4+q0VbECPHcuet54qFILSfN5b2UZiv/XG+HOL8= Received: by 10.38.207.79 with SMTP id e79mr474043rng; Fri, 05 Nov 2004 10:09:26 -0800 (PST) Received: by 10.38.165.32 with HTTP; Fri, 5 Nov 2004 10:09:26 -0800 (PST) Message-ID: <27c475ec0411051009460dd9d0@mail.gmail.com> Date: Fri, 5 Nov 2004 13:09:26 -0500 From: Matt Nuzum Reply-To: matt@followers.net To: Postgresql Performance Subject: What is the difference between these? Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/103 X-Sequence-Number: 9057 To me, these three queries seem identical... why doesn't the first one (simplest to understand and write) go the same speed as the third one? I'll I'm trying to do is get statistics for one day (in this case, today) summarized. Table has ~25M rows. I'm using postgres 7.3.? on rh linux 7.3 (note that i think the difference between the first two might just be related to the data being in memory for the second query). EXPLAIN ANALYZE select count(distinct sessionid) from usage_access where atime > date_trunc('day', now()); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=933439.69..933439.69 rows=1 width=4) (actual time=580350.43..580350.43 rows=1 loops=1) -> Seq Scan on usage_access (cost=0.00..912400.11 rows=8415831 width=4) (actual time=580164.48..580342.21 rows=2964 loops=1) Filter: (atime > date_trunc('day'::text, now())) Total runtime: 580350.65 msec (4 rows) EXPLAIN ANALYZE select count(distinct sessionid) from (select * from usage_access where atime > date_trunc('day', now())) as temp; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=933439.69..933439.69 rows=1 width=4) (actual time=348012.85..348012.85 rows=1 loops=1) -> Seq Scan on usage_access (cost=0.00..912400.11 rows=8415831 width=4) (actual time=347960.53..348004.68 rows=2964 loops=1) Filter: (atime > date_trunc('day'::text, now())) Total runtime: 348013.10 msec (4 rows) EXPLAIN ANALYZE select count(distinct sessionid) from usage_access where atime between date_trunc('day', now()) and date_trunc('day', now()) + '1 day'::interval; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=89324.98..89324.98 rows=1 width=4) (actual time=27.84..27.84 rows=1 loops=1) -> Index Scan using usage_access_atime on usage_access (cost=0.00..89009.39 rows=126237 width=4) (actual time=0.51..20.37 rows=2964 loops=1) Index Cond: ((atime >= date_trunc('day'::text, now())) AND (atime <= (date_trunc('day'::text, now()) + '1 day'::interval))) Total runtime: 28.11 msec (4 rows) -- Matthew Nuzum | Makers of "Elite Content Management System" www.followers.net | View samples of Elite CMS in action matt@followers.net | http://www.followers.net/portfolio/