On Sun, 15 Oct 2006, Tom Lane wrote: > Jeremy Drake <[EMAIL PROTECTED]> writes: > > CREATE TABLE test_domain ( > > fkey integer not null, > > k integer not null, > > x1 integer not null, > > x2 integer, > > mp m_or_p not null > > ); > > > CREATE INDEX test_domain_k_x1_x2_m ON test_domain (k, x1, x2) WHERE mp = > > 'm'; > > CREATE INDEX test_domain_k_x1_x2_p ON test_domain (k, x1, x2) WHERE mp = > > 'p'; > > Perhaps you need a non-partial index.
I just tried that, CREATE INDEX test_domain_k_x1_x2_mp ON test_domain (k, x1, x2, mp); and dropped the others. That actually works properly. jeremyd=# explain analyze select * from test_domain where k = 1255 and mp; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on test_domain (cost=5.37..237.21 rows=66 width=17) (actual time=0.115..0.707 rows=132 loops=1) Recheck Cond: (k = 1255) Filter: mp -> Bitmap Index Scan on test_domain_k_x1_x2_mp (cost=0.00..5.37 rows=66 width=0) (actual time=0.081..0.081 rows=132 loops=1) Index Cond: ((k = 1255) AND (mp = true)) Total runtime: 1.137 ms (6 rows) I thought I had to refer to all of the columns in order for this to work, that I could not skip some in the middle, but it seems to work. -- If you can survive death, you can probably survive anything. ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org