X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 6CEAB9DC999 for ; Fri, 10 Mar 2006 14:58:54 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 27100-09 for ; Fri, 10 Mar 2006 14:58:54 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 0F75B9DCA2C for ; Fri, 10 Mar 2006 14:58:51 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k2AIwoZX007571; Fri, 10 Mar 2006 13:58:51 -0500 (EST) To: Evgeny Gridasov cc: pgsql-performance@postgresql.org Subject: Re: one-field index vs. multi-field index planner In-reply-to: <20060310202823.88495774.eugrid@fpm.kubsu.ru> References: <6BCB9D8A16AC4241919521715F4D8BCEA35109@algol.sollentuna.se> <200603101113.35868.jdevisser@digitalfairway.com> <20060310194545.be002083.eugrid@fpm.kubsu.ru> <6628.1142010559@sss.pgh.pa.us> <20060310202823.88495774.eugrid@fpm.kubsu.ru> Comments: In-reply-to Evgeny Gridasov message dated "Fri, 10 Mar 2006 20:28:23 +0300" Date: Fri, 10 Mar 2006 13:58:50 -0500 Message-ID: <7570.1142017130@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.112 required=5 tests=[AWL=0.112] X-Spam-Score: 0.112 X-Spam-Level: X-Archive-Number: 200603/141 X-Sequence-Number: 17578 Evgeny Gridasov writes: > ofcourse I've analyzed it. > visible is true for about 0.3% of all rows. Well, I get an indexscan on i3 ... there isn't going to be any strong reason for the planner to prefer i2 over i1, given that the phone column is probably near-unique and the i2 index will be bigger than i1. I don't see why it wouldn't like i3 though. Could we see the EXPLAIN ANALYZE results with and without i3? regression=# CREATE TABLE test (phone TEXT, visible BOOLEAN); CREATE TABLE regression=# insert into test select (z/2)::text,(z%1000)<=3 from generate_series(1,300000) z; INSERT 0 300000 regression=# CREATE INDEX i1 ON test(phone); CREATE INDEX regression=# CREATE INDEX i2 ON test(phone, visible); CREATE INDEX regression=# CREATE INDEX i3 ON test(phone, visible) WHERE visible; CREATE INDEX regression=# analyze test; ANALYZE regression=# explain SELECT * FROM test WHERE phone='12345' AND visible; QUERY PLAN ---------------------------------------------------------------- Index Scan using i3 on test (cost=0.00..5.82 rows=1 width=10) Index Cond: ((phone = '12345'::text) AND (visible = true)) (2 rows) regression=# drop index i3; DROP INDEX regression=# explain SELECT * FROM test WHERE phone='12345' AND visible; QUERY PLAN ---------------------------------------------------------------- Index Scan using i2 on test (cost=0.00..5.82 rows=1 width=10) Index Cond: ((phone = '12345'::text) AND (visible = true)) Filter: visible (3 rows) regression=# regards, tom lane