Re: optimizing a query

On Wed, Jun 22, 2016 at 2:23 PM, Jonathan Vanasco <postgres(at)2xlp(dot)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.