Re: visualizing B-tree index coverage

I realize that using OR will not result in an index scan.
I will never be interested in a OR condition for the kinds
of searches I use. In my Select statements, I always name
every column of the multi-column index in same order that
they were named when creating the index. I always use
the >= condition, and very rarely, the = condition.

However, I am concerned that I must place
the most selective column first in my index. I cannot tell,
a priori, which column will be most selective. That depends on the
nature of search, which can vary widely each time.
Are you saying that if my first column is not selective, even though the remaining
columns are, the planner may choose not to use the index after
seeing that the first column is not very selective?
That seems like an oversight, IMHO. Shouldn't the overall effect of
using all the columns be considered before choosing not to use an
index scan?

Since I'm using every column of my multi-column index for every search,
and I always use >=, Explain Analyze always shows that every column
is considered in the index scan. However, that is only when the
index scan is used. Sometimes, Explain Analyze shows it is not used.
That appears to happen when my search condition is very general.
This it to be expected, so I am not worried. Most of my searches will
be intermediate, namely not VERY selective, but also not VERY general.
So the idea of the multi-column index is to "characterize" each row
sufficiently, even when it is a perfectly ordinary row with no ONE
feature being distinctive, but rather several features together giving
it it's distinctive character. That is my interpretation of the
multi-column index.

TJ

PFC wrote:
>
> 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?