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 BB649536C0 for ; Thu, 21 Apr 2005 12:54:45 -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 16056-05 for ; Thu, 21 Apr 2005 15:54:40 +0000 (GMT) Received: from localhost.net (cust.89.121.adsl.cistron.nl [195.64.89.121]) by svr1.postgresql.org (Postfix) with ESMTP id EE68A534FB for ; Thu, 21 Apr 2005 12:54:39 -0300 (ADT) Received: from [127.0.0.1] (localhost [127.0.0.1]) by localhost.net (Postfix) with ESMTP id 4872883D6E for ; Thu, 21 Apr 2005 17:54:37 +0200 (CEST) Message-ID: <4267CCB9.40807@trust-factory.com> Date: Thu, 21 Apr 2005 17:54:33 +0200 From: Richard van den Berg Organization: Trust Factory User-Agent: Debian Thunderbird 1.0.2 (X11/20050331) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: When are index scans used over seq scans? References: <426665A0.1080102@trust-factory.com> <42666890.1000401@arbash-meinel.com> <42667219.7050907@trust-factory.com> <4266773E.405@arbash-meinel.com> <42679922.3040300@trust-factory.com> <4267A1C1.9060706@arbash-meinel.com> In-Reply-To: <4267A1C1.9060706@arbash-meinel.com> X-Enigmail-Version: 0.90.2.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200504/570 X-Sequence-Number: 11967 John A Meinel wrote: > You might try doing: > ALTER TABLE us ALTER COLUMN starttimetrunc SET STATISTICS 200; > ALTER TABLE us ALTER COLUMN finishtimetrunc SET STATISTICS 200; > VACUUM ANALYZE us; I've been looking into that. While increasing the statistics makes the planner use the index for simple selects, it still does not for joins. Another thing that threw me off is that after a "vacuum analyze" a "select * from us where 'x' between start and finish" uses seq scans, while after just an "analyze" is uses the index! I thought both statements were supposed to update the statistics in the same way? (This is with 7.4.7.) > You have 2 tables, a duration, and a from->to table, right? How many > rows in each? Duration: 10k Sessions: 1M > Anyway, you can play around with it by using stuff like: > SET enable_seqscan TO off; This doesn't help much. Instead of turning seqscans off this setting increases its cost with 100M. Since my query already has a cost of about 400M-800M this doesn't matter much. For now, the only reliable way of forcing the use of the index is to set cpu_tuple_cost = 1. -- Richard van den Berg, CISSP ------------------------------------------- Trust Factory B.V. | www.dna-portal.net Bazarstraat 44a | www.trust-factory.com 2518AK The Hague | Phone: +31 70 3620684 The Netherlands | Fax : +31 70 3603009 -------------------------------------------