On Wed, Jun 22, 2016 at 2:23 PM, Jonathan Vanasco <postg...@2xlp.com> wrote:
> > 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.
