Re: 600 million rows of data. Bad hardware or need partitioning?

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

 



On Mon, May 4, 2020 at 12:44 AM David Rowley <dgrowleyml@xxxxxxxxx> wrote:
> How long does it take if you first do:
>
> SET enable_nestloop TO off;

I tried this, but it takes much longer

Time: 318620.319 ms (05:18.620)

Below is the EXPLAIN ANALYZE


      QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on test_table  (cost=639.96..4581378.80 rows=10000 width=160)
(actual time=290593.159..290593.159 rows=0 loops=1)
   ->  Hash Join  (cost=639.96..4581378.80 rows=10000 width=160)
(actual time=422.313..194430.318 rows=10000 loops=1)
         Hash Cond: (test_table.id = subquery.id)
         ->  Seq Scan on test_table  (cost=0.00..4200967.98
rows=101238898 width=92) (actual time=296.970..177731.611
rows=101189271 loops=1)
         ->  Hash  (cost=514.96..514.96 rows=10000 width=56) (actual
time=125.312..125.312 rows=10000 loops=1)
               Buckets: 16384  Batches: 1  Memory Usage: 959kB
               ->  Subquery Scan on subquery  (cost=0.00..514.96
rows=10000 width=56) (actual time=0.030..123.031 rows=10000 loops=1)
                     ->  Limit  (cost=0.00..414.96 rows=10000
width=16) (actual time=0.024..121.014 rows=10000 loops=1)
                           ->  Seq Scan on test_table test_table_1
(cost=0.00..4200967.98 rows=101238898 width=16) (actual
time=0.021..120.106 rows=10000 loops=1)
 Planning Time: 0.304 ms
 JIT:
   Functions: 12
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 2.178 ms, Inlining 155.980 ms, Optimization
100.611 ms, Emission 39.481 ms, Total 298.250 ms
 Execution Time: 290595.448 ms
(15 rows)


> If you find it's faster then you most likely have random_page_cost set
> unrealistically low. In fact, I'd say it's very unlikely that a nested
> loop join will be a win in this case when random pages must be read
> from a mechanical disk, but by all means, try disabling it with the
> above command and see for yourself.

It's much slower with SET enable_nestloop TO off. Any other suggestions?





[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux