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 6C637D1B258 for ; Mon, 5 Jul 2004 10:42:20 -0300 (ADT) 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 09586-07 for ; Mon, 5 Jul 2004 13:42:19 +0000 (GMT) Received: from ludojad.itpp.pl (ludojad.itpp.pl [193.41.112.10]) by svr1.postgresql.org (Postfix) with ESMTP id 50BFBD1B1DA for ; Mon, 5 Jul 2004 10:42:14 -0300 (ADT) Received: by ludojad.itpp.pl (Postfix, from userid 1111) id 925F2B7691; Mon, 5 Jul 2004 15:46:15 +0200 (CEST) Date: Mon, 5 Jul 2004 15:46:15 +0200 From: eleven@ludojad.itpp.pl To: pgsql-performance@postgresql.org Subject: Re: Seq scan vs. Index scan with different query conditions Message-ID: <20040705134615.GA18520@ludojad.itpp.pl> References: <20040705101515.GA12879@ludojad.itpp.pl> <1089027854.6664.197.camel@lamb.mcmillan.net.nz> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-2 Content-Disposition: inline In-Reply-To: <1089027854.6664.197.camel@lamb.mcmillan.net.nz> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=NO_REAL_NAME X-Spam-Level: X-Archive-Number: 200407/12 X-Sequence-Number: 7403 On Mon, Jul 05, 2004 at 11:44:13PM +1200, Andrew McMillan wrote: > > DateTimeIndex was created on both columns (Date/Time): > > CREATE INDEX "DateTimeIndex" ON "tablex" USING btree ("Date", "Time"); > PostgreSQL is always going to switch at some point, where the number of > rows that have to be read from the table exceed some percentage of the > total rows in the table. > We can possibly be more helpful if you send EXPLAIN ANALYZE, rather than > just EXPLAIN. Unfortunately that seq scan vs. index scan heuristic was wrong - full scan kills the machine in no time due to large amount of INSERTs happening in the background (I/O bottleneck). > - Is this supposed to be a slice of midnight to 6pm, for each day > between 28 June and 4 July? If you want a continuous period from > Midnight 28 June -> 6pm 4 July you're better to have a single timestamp > field. > - It is unlikely that the , "Time" on your index is adding much to your > selectivity, and it may be that you would be better off without it. Yes, we've figured out that index on Date + Time is rather useless. Thanks for the tip, we've created index upon Date column instead and it should be enough. > - the DISTINCT can screw up your results, and it usually means that the > SQL is not really the best it could be. A _real_ need for DISTINCT is > quite rare in my experience, and from what I have seen it adds overhead > and tends to encourage bad query plans when used unnecessarily. What do you mean? The reason for which there's DISTINCT in that query is because I want to know how many unique rows is in the table. Do you suggest selecting all rows and doing "DISTINCT"/counting on the application level? -- 11.