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 0D0625294C for ; Thu, 12 May 2005 06:07:52 -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 99223-07 for ; Thu, 12 May 2005 09:07:43 +0000 (GMT) Received: from ns2.rox.net (ns2.rox.net [212.63.65.2]) by svr1.postgresql.org (Postfix) with ESMTP id EC193565C1 for ; Thu, 12 May 2005 06:07:41 -0300 (ADT) Received: from localhost ([127.0.0.1]) by emma.rox.net with esmtp (Exim 4.44) id 1DW9fU-00068Z-FL for pgsql-performance@postgresql.org; Thu, 12 May 2005 11:07:44 +0200 Received: from e176011089.adsl.alicedsl.de ([85.176.11.89] helo=[10.0.1.6]) by emma.rox.net with esmtpa (Exim 4.44) id 1DW9fT-00067n-1e for pgsql-performance@postgresql.org; Thu, 12 May 2005 11:07:43 +0200 Mime-Version: 1.0 (Apple Message framework v622) Content-Transfer-Encoding: 7bit Message-Id: <2aeb891e99fa6313e9e3a20d48c099aa@cluster9.com> Content-Type: text/plain; charset=US-ASCII; format=flowed To: PgSQL Performance ML From: David Teran Subject: AND OR combination: index not being used Date: Thu, 12 May 2005 11:07:41 +0200 X-Mailer: Apple Mail (2.622) X-Scanned-By: rockenstein AG X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200505/231 X-Sequence-Number: 12395 Hi, postgres 8.0.1, mac os x 10.3.9 i have a select with multiple OR's combined with one AND: explain analyze SELECT t0.ATTRIBUTE_TYPE FROM ATTRIBUTE_VALUE t0 WHERE (((t0.ATTRIBUTE_TYPE = 'pb'::varchar(10) OR t0.ATTRIBUTE_TYPE = 'po'::varchar(10) OR t0.ATTRIBUTE_TYPE = 'pn'::varchar(10) OR t0.ATTRIBUTE_TYPE = 'ps'::varchar(10))) AND t0.ID_ATTRIBUTE = 17::int8); The result is the following. It shows that postgres does not use an index which makes the select pretty slow. Seq Scan on attribute_value t0 (cost=0.00..529.13 rows=208 width=5) (actual time=66.591..66.591 rows=0 loops=1) Filter: ((((attribute_type)::text = 'pb'::text) OR ((attribute_type)::text = 'po'::text) OR ((attribute_type)::text = 'pn'::text) OR ((attribute_type)::text = 'ps'::text)) AND (id_attribute = 17::bigint)) Total runtime: 66.664 ms (3 rows) When i remove one OR qualifier one can see that now an index is used. explain analyze SELECT t0.ATTRIBUTE_TYPE FROM ATTRIBUTE_VALUE t0 WHERE (((t0.ATTRIBUTE_TYPE = 'pb'::varchar(10) OR t0.ATTRIBUTE_TYPE = 'po'::varchar(10) OR t0.ATTRIBUTE_TYPE = 'pn'::varchar(10))) AND t0.ID_ATTRIBUTE = 17::int8); Index Scan using attribute_value__attribute_type__id_attribute, attribute_value__attribute_type__id_attribute, attribute_value__attribute_type__id_attribute on attribute_value t0 (cost=0.00..451.82 rows=137 width=5) (actual time=0.301..0.301 rows=0 loops=1) Index Cond: ((((attribute_type)::text = 'pb'::text) AND (id_attribute = 17::bigint)) OR (((attribute_type)::text = 'po'::text) AND (id_attribute = 17::bigint)) OR (((attribute_type)::text = 'pn'::text) AND (id_attribute = 17::bigint))) Filter: ((((attribute_type)::text = 'pb'::text) OR ((attribute_type)::text = 'po'::text) OR ((attribute_type)::text = 'pn'::text)) AND (id_attribute = 17::bigint)) Total runtime: 0.414 ms (4 rows) When i do 'set enable_seqscan=no' the index is used of course. Unfortunately the sql is generated on the fly and its not easy, more or less impossible to selectively enable / disable seqscan. Any hint how to force postgres to use the index even with more OR parts? regards, David