Re: Extremely slow when query uses GIST exclusion index - Mailing list pgsql-performance

Am 29.08.2018 um 12:50 schrieb Andreas Kretschmer:
> Okay, other solution. The problem is the nested loop, we can disable 
> that: 

oh, i used PG 10, this time 9.5:

test=# explain analyse SELECT *
FROM app
JOIN group_span ON
   app.group_id = group_span.group_id AND
   app.app_time <@ group_span.valid_period
JOIN member_span ON
   group_span.group_id = member_span.group_id AND
   group_span.valid_period && member_span.valid_period;
QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Nested Loop  (cost=0.55..4740.90 rows=180 width=212) (actual 
time=2.915..17624.676 rows=10000 loops=1)
    Join Filter: (app.group_id = member_span.group_id)
    ->  Nested Loop  (cost=0.28..4472.00 rows=600 width=112) (actual 
time=0.292..347.838 rows=10000 loops=1)
          ->  Seq Scan on app  (cost=0.00..194.00 rows=10000 width=44) 
(actual time=0.012..2.689 rows=10000 loops=1)
          ->  Index Scan using group_span_group_id_valid_period_excl on 
group_span (cost=0.28..0.42 rows=1 width=68) (actual time=0.029..0.033 
rows=1 loops=10000)
                Index Cond: ((group_id = app.group_id) AND (app.app_time 
<@ valid_period))
    ->  Index Scan using 
member_span_member_id_group_id_valid_period_excl on member_span 
(cost=0.28..0.44 rows=1 width=100) (actual time=0.912..1.726 rows=1 
loops=10000)
          Index Cond: ((group_id = group_span.group_id) AND 
(group_span.valid_period && valid_period))
  Planning time: 1.554 ms
  Execution time: 17627.266 ms
(10 rows)

test=*# set enable_nestloop to false;
SET
test=*# explain analyse SELECT *
FROM app
JOIN group_span ON
   app.group_id = group_span.group_id AND
   app.app_time <@ group_span.valid_period
JOIN member_span ON
   group_span.group_id = member_span.group_id AND
   group_span.valid_period && member_span.valid_period;
                                                            QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------
  Hash Join  (cost=2383.43..14284.93 rows=180 width=212) (actual 
time=42.440..63.834 rows=10000 loops=1)
    Hash Cond: (app.group_id = member_span.group_id)
    Join Filter: (group_span.valid_period && member_span.valid_period)
    Rows Removed by Join Filter: 2000
    ->  Merge Join  (cost=1928.43..12478.43 rows=600 width=112) (actual 
time=34.068..47.954 rows=10000 loops=1)
          Merge Cond: (app.group_id = group_span.group_id)
          Join Filter: (app.app_time <@ group_span.valid_period)
          Rows Removed by Join Filter: 2000
          ->  Sort  (cost=858.39..883.39 rows=10000 width=44) (actual 
time=15.331..17.104 rows=10000 loops=1)
                Sort Key: app.group_id
                Sort Method: quicksort  Memory: 1166kB
                ->  Seq Scan on app  (cost=0.00..194.00 rows=10000 
width=44) (actual time=0.004..1.070 rows=10000 loops=1)
          ->  Sort  (cost=1070.04..1100.04 rows=12000 width=68) (actual 
time=18.720..20.712 rows=12000 loops=1)
                Sort Key: group_span.group_id
                Sort Method: quicksort  Memory: 2072kB
                ->  Seq Scan on group_span  (cost=0.00..257.00 
rows=12000 width=68) (actual time=0.007..1.396 rows=12000 loops=1)
    ->  Hash  (cost=305.00..305.00 rows=12000 width=100) (actual 
time=8.198..8.198 rows=12000 loops=1)
          Buckets: 16384  Batches: 1  Memory Usage: 1582kB
          ->  Seq Scan on member_span  (cost=0.00..305.00 rows=12000 
width=100) (actual time=0.011..2.783 rows=12000 loops=1)
  Planning time: 0.468 ms
  Execution time: 64.694 ms
(21 rows)

test=*#


Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com