Обсуждение: optimizing a query

I have a handful of queries in the following general form that I can't seem to optimize any further (same results on
9.3,9.4, 9.5) 

I'm wondering if anyone might have a suggestion, or if they're done.

The relevant table structure:

    t_a2b
        a_id INT references t_a(id)
        b_id INT references t_b(id)
        col_a

    t_a
        id INT
        col_1 INT
        col_2 BOOL

The selects query the association table (t_a2b) and join in a related table (t_a) for some filtering.

In effort of simplifying the work, I've created indexes on t_a that have all the related columns.

    CREATE INDEX test_idx ON t_a(col_1, id) WHERE col_2 IS NOT FALSE;
        CREATE INDEX test_idx__a ON t_a(col_1, id) WHERE col_2 IS NOT FALSE;

postgres will query test_idx__a first (yay!) but then does a bitmap heap scan on t_a, and uses the raw t_a for the hash
join.  

I don't actually need any information from t_a - it's just there for the filtering, and ideally postgres would just use
theindex. 

I thought this might have been from using a partial index, but the same results happen with a full index.  I just can't
seemto avoid this hash join against the full table. 

anyone have a suggestion?


example query

    SELECT t_a2b.b_id AS b_id,
           count(t_a2b.b_id) AS counted
    FROM t_a2b
    WHERE
          t_a2b.col_a = 1
          AND
          t_a.col_1 = 730
          AND
          t_a.col_2 IS NOT False
    GROUP BY t_a2b.b_id
    ORDER BY     counted DESC,
                 t_a2b.b_id ASC

                                   

On 06/21/2016 03:33 PM, Jonathan Vanasco wrote:
> I have a handful of queries in the following general form that I can't seem to optimize any further (same results on
9.3,9.4, 9.5) 
>
> I'm wondering if anyone might have a suggestion, or if they're done.
>
> The relevant table structure:
>
>     t_a2b
>         a_id INT references t_a(id)
>         b_id INT references t_b(id)
>         col_a
>
>     t_a
>         id INT
>         col_1 INT
>         col_2 BOOL
>
> The selects query the association table (t_a2b) and join in a related table (t_a) for some filtering.
>
> In effort of simplifying the work, I've created indexes on t_a that have all the related columns.
>
>     CREATE INDEX test_idx ON t_a(col_1, id) WHERE col_2 IS NOT FALSE;
>         CREATE INDEX test_idx__a ON t_a(col_1, id) WHERE col_2 IS NOT FALSE;
>
> postgres will query test_idx__a first (yay!) but then does a bitmap heap scan on t_a, and uses the raw t_a for the
hashjoin. 
>
> I don't actually need any information from t_a - it's just there for the filtering, and ideally postgres would just
usethe index. 
>
> I thought this might have been from using a partial index, but the same results happen with a full index.  I just
can'tseem to avoid this hash join against the full table. 
>
> anyone have a suggestion?
>

The below works without including t_a in the FROM?

>
> example query
>
>     SELECT t_a2b.b_id AS b_id,
>            count(t_a2b.b_id) AS counted
>     FROM t_a2b
>     WHERE
>           t_a2b.col_a = 1
>           AND
>           t_a.col_1 = 730
>           AND
>           t_a.col_2 IS NOT False
>     GROUP BY t_a2b.b_id
>     ORDER BY     counted DESC,
>                  t_a2b.b_id ASC
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On Jun 22, 2016, at 4:25 AM, Erik Gustafson wrote:

> don't you want an index on t_a2b.col_a, maybe partial where col_a=1 ?

that table has indexes on all columns.  they're never referenced because the rows are so short.  this was just an
examplequery too, col_a has 200k variations  

After a lot of testing, I think I found a not-bug but possible area-for-improvement in the planner when joining against
atable for filtering (using my production 9.5.2 box) 

I checked a query against multiple possible indexes using the related columns.  only one of indexes was on the table
foreach series of tests, and I analyzed the table after the drop/create of indexes. 


Note 1: The only time an index-only scan is used, is on this form:

        CREATE INDEX idx_partial_fkey_id_partial ON table_a(fkey_1, id, col_partial) WHERE col_partial IS NOT FALSE;

    Omitting the col_partial from being indexed will trigger a Bitmap Heap Scan on the full table with a recheck
condition:

        CREATE INDEX idx_partial_fkey_id ON table_a(fkey_1, id) WHERE col_partial IS NOT FALSE;

    This shouldn't be necessary.  the planner knew that `col_partial` fulfilled the WHERE clause when it used the
index,but scanned the table to check it anyways. 

    On most tables the heap scan was negligible, but on a few larger tables it accounted a 20% increase in execution.

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
tablewith 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;

On Jun 22, 2016, at 2:38 PM, David G. Johnston wrote:
> What query?  ​A self-contained email would be nice.​

This was the same query as in the previous email in the thread.  I didn't think to repeat it.  I did include it below.

> ​https://www.postgresql.org/docs/9.6/static/indexes-index-only-scans.html
>
> Note especially:
>
> ​"Visibility information is not stored in index entries, only in heap entries; ..."
>
> The check against the heap isn't for the truthiness of the predicate but the visibility of the row.

Thanks for this link.

The table I worked on hasn't had any writes since a server restart, and according to those docs the queries should have
beenoff the visibility map not the heap.   
However the amount of time to search is not in line with expectations for the visibility map.

After reading the last paragraph about some index optimizations in 9.6 that looked related, I installed the RC on an
anothermachine and dumped 2 tables from production to see if I would qualify for any improvements.   

>>> But there's a problem: the WHERE clause refers to success which is not available as a result column of the index.
Nonetheless,an index-only scan is possible because the plan does not need to recheck that part of the WHERE clause at
runtime:all entries found in the index necessarily have success = true so this need not be explicitly checked in the
plan.PostgreSQL versions 9.6 and later will recognize such cases and allow index-only scans to be generated, but older
versionswill not. 

The 9.6 branch planner optimizes for my query and realizes that it doesn't need to check the table:

So while this index is necessary on 9.5:
    CREATE INDEX idx__9_5 ON table_a(column_1, id, column_2) WHERE column_2 IS NOT FALSE;

This index works on 9.6
    CREATE INDEX idx__9_6 ON table_a(column_1, id) WHERE column_2 IS NOT FALSE;

Considering I have several million rows, this has a noticeable effect .

Combined with the various improvements on 9.6, there is a huge difference in query speed:

    9.6 runs the query with the smaller index in an average of 1200ms
    9.5 runs the query with the larger index in an average of 2700ms


> ​This one requires knowledge of the query; but I am not surprised that reversing the order of columns in a b-tree
indexhas an impact. 

I expected this to impact the decision on which index to use when multiple ones are available, or to offer poor
performance-- but not to discount using the index entirely. 


> ​All at once?

No.  I dropped all indexes to test, then for each column combination did:

    CREATE INDEX foo_idx;
        ANALYZE foo ;
    EXPLAIN ANALYZE;
    DROP INDEX foo_idx;

I call Explain Analyze manually once for the plan, then via script 25x to average out execution times and account for
cold-startvs having loaded all the indexes.  I shut down all other user processes on the machine as well.