Search Postgresql Archives

Query plan prefers hash join when nested loop is much faster

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,

I am trying to understand why the query planner insists on using a hash join, and how to make it choose the better option, which in this case would be a nested loop. I have two tables:

// about 200 million rows
CREATE TABLE module_result(
    id bigserial PRIMARY KEY,
    name_id bigint NOT NULL references result_name(id),
    run_id integer NOT NULL references run (id),
    logs text NOT NULL,
    status result_status NOT NULL
);
CREATE INDEX ON module_result (run_id);

// 500 million rows
CREATE TABLE test_result(
    id bigserial PRIMARY KEY,
    name_id bigint NOT NULL references result_name(id),
    module_result_id bigint NOT NULL references module_result (id),
    seconds float NOT NULL,
    failure_msg text, -- Either a <failure>...</failure> or an <error message="... />
    status result_status NOT NULL
);
CREATE INDEX ON test_result (module_result_id);

I'm trying to select all test cases that belong to a given run_id, which logically involves finding all IDs in module_result that belong to a given run, and then selecting the test results for those IDs (run_id has several module_result_id, which in turn have several test_results each). 

EXPLAIN ANALYZE SELECT test_result.status, count(test_result.status) as "Count" FROM test_result INNER JOIN module_result ON module_result.id = test_result.module_result_id WHERE module_resul
 t.run_id=158523 GROUP BY test_result.status                                                                                                                                                                  
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                                                           |
|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Finalize GroupAggregate  (cost=7771702.73..7771804.08 rows=3 width=12) (actual time=32341.993..32341.994 rows=2 loops=1)                                                             |
|   Group Key: test_result.status                                                                                                                                                      |
|   ->  Gather Merge  (cost=7771702.73..7771804.02 rows=6 width=12) (actual time=32341.970..32343.222 rows=6 loops=1)                                                                  |
|         Workers Planned: 2                                                                                                                                                           |
|         Workers Launched: 2                                                                                                                                                          |
|         ->  Partial GroupAggregate  (cost=7770702.71..7770803.30 rows=3 width=12) (actual time=32340.278..32340.286 rows=2 loops=3)                                                  |
|               Group Key: test_result.status                                                                                                                                          |
|               ->  Sort  (cost=7770702.71..7770736.23 rows=13408 width=4) (actual time=32339.698..32339.916 rows=4941 loops=3)                                                        |
|                     Sort Key: test_result.status                                                                                                                                     |
|                     Sort Method: quicksort  Memory: 431kB                                                                                                                            |
|                     Worker 0:  Sort Method: quicksort  Memory: 433kB                                                                                                                 |
|                     Worker 1:  Sort Method: quicksort  Memory: 409kB                                                                                                                 |
|                     ->  Hash Join  (cost=586.15..7769783.54 rows=13408 width=4) (actual time=18112.078..32339.011 rows=4941 loops=3)                                                 |
|                           Hash Cond: (test_result.module_result_id = module_result.id)                                                                                               |
|                           ->  Parallel Seq Scan on test_result  (cost=0.00..7145224.72 rows=237703872 width=12) (actual time=0.034..15957.894 rows=190207740 loops=3)                |
|                           ->  Hash  (cost=438.41..438.41 rows=11819 width=8) (actual time=3.905..3.905 rows=14824 loops=3)                                                           |
|                                 Buckets: 16384  Batches: 1  Memory Usage: 708kB                                                                                                      |
|                                 ->  Index Scan using module_result_run_id_idx on module_result  (cost=0.57..438.41 rows=11819 width=8) (actual time=0.017..2.197 rows=14824 loops=3) |
|                                       Index Cond: (run_id = 158523)                                                                                                                  |
| Planning Time: 0.178 ms                                                                                                                                                              |
| Execution Time: 32343.330 ms                                                                                                                                                         |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN
Time: 32.572s (32 seconds), executed in: 32.551s (32 seconds)


This plan takes about 30s to execute. If I turn off seqscan, I get a nested loop join that takes about 0.02s to execute:

set enable_seqscan = off                                                                                                                                                                        
SET
Time: 0.305s
> explain analyze select test_result.status, count(test_result.status) as "Count"  from test_result inner join module_result ON module_result.id = test_result.module_result_id where module_resul
 t.run_id=158523   group by test_result.status                                                                                                                                                                  
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                                                            |
|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Finalize GroupAggregate  (cost=34297042.16..34297143.50 rows=3 width=12) (actual time=15.014..15.015 rows=2 loops=1)                                                                  |
|   Group Key: test_result.status                                                                                                                                                       |
|   ->  Gather Merge  (cost=34297042.16..34297143.44 rows=6 width=12) (actual time=15.005..15.850 rows=6 loops=1)                                                                       |
|         Workers Planned: 2                                                                                                                                                            |
|         Workers Launched: 2                                                                                                                                                           |
|         ->  Partial GroupAggregate  (cost=34296042.13..34296142.72 rows=3 width=12) (actual time=12.937..12.940 rows=2 loops=3)                                                       |
|               Group Key: test_result.status                                                                                                                                           |
|               ->  Sort  (cost=34296042.13..34296075.65 rows=13408 width=4) (actual time=12.339..12.559 rows=4941 loops=3)                                                             |
|                     Sort Key: test_result.status                                                                                                                                      |
|                     Sort Method: quicksort  Memory: 461kB                                                                                                                             |
|                     Worker 0:  Sort Method: quicksort  Memory: 403kB                                                                                                                  |
|                     Worker 1:  Sort Method: quicksort  Memory: 408kB                                                                                                                  |
|                     ->  Nested Loop  (cost=232.74..34295122.96 rows=13408 width=4) (actual time=0.232..11.671 rows=4941 loops=3)                                                      |
|                           ->  Parallel Bitmap Heap Scan on module_result  (cost=232.17..44321.35 rows=4925 width=8) (actual time=0.218..0.671 rows=4941 loops=3)                      |
|                                 Recheck Cond: (run_id = 158523)                                                                                                                       |
|                                 Heap Blocks: exact=50                                                                                                                                 |
|                                 ->  Bitmap Index Scan on module_result_run_id_idx  (cost=0.00..229.21 rows=11819 width=0) (actual time=0.592..0.592 rows=14824 loops=1)               |
|                                       Index Cond: (run_id = 158523)                                                                                                                   |
|                           ->  Index Scan using test_result_module_result_id_idx on test_result  (cost=0.57..6911.17 rows=4331 width=12) (actual time=0.002..0.002 rows=1 loops=14824) |
|                                 Index Cond: (module_result_id = module_result.id)                                                                                                     |
| Planning Time: 0.214 ms                                                                                                                                                               |
| Execution Time: 15.932 ms                                                                                                                                                             |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN
Time: 0.235s


I don't think it's recommended to turn off seqscan in production, so I'm looking for a way to make the query planner choose the significantly faster plan. How can I do that? It's probably related to some statistics, but they are up to date (I run ANALYZE several times).

Any pointers would be very helpful,

thank you,
iulian
ReplyForward

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux