X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 28712D1B498 for ; Mon, 29 Dec 2003 19:26:39 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 62922-09 for ; Mon, 29 Dec 2003 15:25:51 -0400 (AST) Received: from bittern.mail.pas.earthlink.net (bittern.mail.pas.earthlink.net [207.217.120.119]) by svr1.postgresql.org (Postfix) with ESMTP id CD654D1B450 for ; Mon, 29 Dec 2003 15:25:49 -0400 (AST) Received: from dpc6682160001.direcpc.com ([66.82.160.1] helo=earthlink.net) by bittern.mail.pas.earthlink.net with esmtp (Exim 3.33 #1) id 1Ab31b-0000in-00 for pgsql-performance@postgresql.org; Mon, 29 Dec 2003 11:26:02 -0800 Message-ID: <3FF07FB2.5060608@earthlink.net> Date: Mon, 29 Dec 2003 14:25:38 -0500 From: Sean Shanny User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.5) Gecko/20031007 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Question about difference in performance of 2 queries References: <3FF057DF.7000605@earthlink.net> <15476.1072716487@sss.pgh.pa.us> In-Reply-To: <15476.1072716487@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/309 X-Sequence-Number: 5169 Here is one of the explain analyzes. This is the from the faster query. Ignore the total runtime as we are currently doing other queries on this machine so it is slightly loaded. Thanks. --sean explain analyze select count (distinct (persistent_cookie_key) ) from f_pageviews where date_key between 305 and 334 and content_key <> -1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=1384925.95..1384925.95 rows=1 width=4) (actual time=4541462.030..4541462.034 rows=1 loops=1) -> Index Scan using idx_pageviews_date_nov_2003 on f_pageviews (cost=0.00..1343566.52 rows=16543772 width=4) (actual time=83.267..4286664.678 rows=15710722 loops=1) Index Cond: ((date_key >= 305) AND (date_key <= 334)) Filter: (content_key <> -1) Total runtime: 4541550.832 ms (5 rows) Tom Lane wrote: >Please show EXPLAIN ANALYZE output for your queries, not just EXPLAIN. >Also it would be useful to see the pg_stats rows for the date_key and >content_key columns. > > regards, tom lane > > >