Note 2:
This is odd, but this index is used by the planner:
CREATE INDEX idx_partial_fkey_id ON table_a(fkey_1, id) WHERE col_partial IS NOT FALSE;
but this index is never used:
CREATE INDEX idx_partial_id_fkey ON table_a(id, fkey_1) WHERE col_partial IS NOT FALSE;
I honestly don't know why the second index would not be used. The query time doubled without it when run on a table with 6million rows and about 20 columns.
-------
The indexes I tested on:
CREATE INDEX idx_fkey_1 ON table_a(fkey_1);
CREATE INDEX idx_partial_fkey ON table_a(fkey_1) WHERE col_partial IS NOT FALSE;
CREATE INDEX idx_partial_fkey_id ON table_a(fkey_1, id) WHERE col_partial IS NOT FALSE;
CREATE INDEX idx_partial_id_fkey ON table_a(id, fkey_1) WHERE col_partial IS NOT FALSE;
CREATE INDEX idx_partial_fkey_partial ON table_a(fkey_1, col_partial) WHERE col_partial IS NOT FALSE;
CREATE INDEX idx_partial_fkey_id_partial ON table_a(fkey_1, id, col_partial) WHERE col_partial IS NOT FALSE;
There is no relation named "table_a" anywhere in your past emails.
Your "t_a" relation is (id : int, col1 : int, col_2 : bool) not (id : int?, fkey_1 : int?, col_partial : bool), and given that t_a is an end relation it doesn't typically have a foreign key.
1) t_a is going to use the indexes appropriate to its where clauses to select records. For the (id, fkey_1) index unless there is dependency [*] (id => fkey_1) you have to scan the entire index so know that you've located all records containing a given fkey_1 value when id is unspecified (as it is in your query).
* if even there was a dependency I don't not believe PostgreSQL would be able to use that knowledge during planning.
2) to join t_a2b an index on t_a2b having a_id as the first column would likely be used to quickly locate matching records from [1]. No additional indexes on t_a are going to help here.
You have a good head for this, and maybe I'm missing something obvious here - this is not my strong suit. The random, though similar, naming of objects in your posts makes it a bit hard to follow. As you found the partial indexes might or might not be influencing the results. And note that the missing column influences the use of an index-only scan - if you are going to get one of those anyway its quite possible a given index will be worse than some non-index alternative.
David J.
