I think you missed an important "feature" of multicolumn indexes, that you better not use 'OR' in your expressions. You seem to want only to use '>=' so this should be OK.
Suppose you have 3 columns a,z,e containing values linearly distributed between ...
select min(a),max(a),min(z),max(z),min(e),max(e) from test; min | max | min | max | min | max -----+-----+-----+-----+-----+----- 0 | 13 | 0 | 99 | 0 | 99
For instance the following query is indexed :
explain analyze select * from test where a>=0 and z>=90 and e>=0;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Index Scan using testa on test (cost=0.00..1637.56 rows=11345 width=16) (actual time=0.085..51.441 rows=13000 loops=1)
Index Cond: ((a >= 0) AND (z >= 90) AND (e >= 0))
Total runtime: 56.307 ms
The following is only partially indexed :
explain analyze select * from test where (a=1 or a=2) and (z=1 or z=8) and e>=0;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using testa, testa on test (cost=0.00..3269.06 rows=346 width=16) (actual time=0.328..52.961 rows=400 loops=1)
Index Cond: ((a = 1) OR (a = 2))
Filter: (((z = 1) OR (z = 8)) AND (e >= 0))
Total runtime: 53.297 ms
You see the 'index cond' field which is what determines the fetched rows, which are then fetched and filtered with the 'filter' expression. Having the most selective index cond is important because it will diminish the number of rows to be fetched. However, in your case the filter expression is also beneficial because any row eliminated by the filter will not need to go through your expensive matching function.
In this case :
SELECT count(*) FROM test; => 131072
SELECT count(*) FROM test WHERE ((a = 1) OR (a = 2)); => 20000
SELECT count(*) FROM test WHERE (a=1 or a=2) and (z=1 or z=8) and e>=0; => 400
In this case the index fetches 20k rows out of 131072 but only 400 are used...
If you don't use OR, index use is more likely :
explain analyze select * from test where (a,z,e) >= (0,50,80);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Index Scan using testa on test (cost=0.00..1669.78 rows=12627 width=16) (actual time=0.087..58.316 rows=13000 loops=1)
Index Cond: ((a >= 0) AND (z >= 50) AND (e >= 80))
Total runtime: 63.049 ms
Here you have a full index scan.
To determine the efficiency of your indexes, you can thus use this method, and look at the 'index cond' and 'filter' expressions, and counting the rows matched by each.
particular number of columns
for indexing. I don't want to use too many, nor too few columns. I also
want to optimize the nature(which atom types, bond types, etc.)
of the count columns. While I could do this
and use the speedup as the measure of success, I think
that if my B-tree were "covering" the data well, I would get the best results.
Covering means finding that optimal situation where there is not one index for all rows
and also not a unique index for every row - something inbetween would be ideal,
or is that basically a wrong idea?
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
