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 403853A3C13 for ; Thu, 4 Nov 2004 08:32:27 +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 57626-09 for ; Thu, 4 Nov 2004 08:32:20 +0000 (GMT) Received: from anchor-post-32.mail.demon.net (anchor-post-32.mail.demon.net [194.217.242.90]) by svr1.postgresql.org (Postfix) with ESMTP id 302973A3ADA for ; Thu, 4 Nov 2004 08:32:21 +0000 (GMT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-32.mail.demon.net with esmtp (Exim 4.42) id 1CPd2Z-000GqU-7r; Thu, 04 Nov 2004 08:32:21 +0000 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 5052017BE6; Thu, 4 Nov 2004 08:32:10 +0000 (GMT) Message-ID: <4189E908.2000104@archonet.com> Date: Thu, 04 Nov 2004 08:32:08 +0000 From: Richard Huxton User-Agent: Mozilla Thunderbird 0.7 (X11/20040615) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Mario Ivankovits Cc: pgsql-performance@postgresql.org Subject: Re: index not used if using IN or OR References: <4189E086.5060400@ops.co.at> In-Reply-To: <4189E086.5060400@ops.co.at> Content-Type: text/plain; charset=us-ascii; format=flowed 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/37 X-Sequence-Number: 8991 Mario Ivankovits wrote: > Hello ! > > Sorry if this has been discussed before, it is just hard to find in the > archives using the words "or" or "in" :-o > > I use postgres-8.0 beta4 for windows. > I broke down my problem to a very simple table - two columns > "primary_key" and "secondary_key". Creates and Insert you will find below. > > If I query the _empty_ freshly created table I get the following explain > result: > > select * from tt where seckey = 1; > Index Scan using seckey_key on tt (cost=0.00..17.07 rows=5 width=12) > Index Cond: (seckey = 1) > > If I use "OR" (or IN) things get worse: > > select * from tt where seckey = 1 or seckey = 2 > Seq Scan on tt (cost=0.00..0.00 rows=1 width=12) > Filter: ((seckey = 1) OR (seckey = 2)) > > Note the "Seq Scan" instead of using the index. But as you said, your table is *empty* - why would an index be faster? Try running EXPLAIN ANALYSE on these queries and look at the actual times. > After populating the table with 8920 records and "analyze" the scenario > gets even worser: > > select * from tt where seckey = 1; > Seq Scan on tt (cost=0.00..168.50 rows=1669 width=12) (actual > time=0.000..15.000 rows=1784 loops=1) > Filter: (seckey = 1) > Total runtime: 31.000 ms > > Now also this simple query uses a "Seq Scan". Well, it thinks it's going to be returning 1669 rows. If that's roughly right, then scanning the table probably is faster. Run the queries again with EXPLAIN ANALYSE. Also try issuing set enable_seqscan=false; This will force the planner to use any indexes it finds. Compare the times with and without, and don't forget to account for the effects of caching. -- Richard Huxton Archonet Ltd