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

Am 29.08.2018 um 05:31 schrieb David:
> For now, I can bypass the GIST index by avoiding range operators in my 
> queries. But why is the GIST index so slow?

your GiST-Index contains (member_id,group_id,valid_period), but your 
query is only on the latter 2 fields.


test=*# create index test_index on member_span using gist 
(group_id,valid_period);
CREATE INDEX
test=*# commit;
COMMIT
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=319.27..776.18 rows=1 width=196) (actual 
time=3.156..334.963 rows=10000 loops=1)
    Join Filter: (app.group_id = member_span.group_id)
    ->  Hash Join  (cost=319.00..771.00 rows=12 width=104) (actual 
time=3.100..14.040 rows=10000 loops=1)
          Hash Cond: (group_span.group_id = app.group_id)
          Join Filter: (app.app_time <@ group_span.valid_period)
          Rows Removed by Join Filter: 2000
          ->  Seq Scan on group_span  (cost=0.00..257.00 rows=12000 
width=59) (actual time=0.013..1.865 rows=12000 loops=1)
          ->  Hash  (cost=194.00..194.00 rows=10000 width=45) (actual 
time=3.037..3.037 rows=10000 loops=1)
                Buckets: 16384  Batches: 1  Memory Usage: 910kB
                ->  Seq Scan on app  (cost=0.00..194.00 rows=10000 
width=45) (actual time=0.010..1.201 rows=10000 loops=1)
    ->  Index Scan using test_index on member_span (cost=0.28..0.42 
rows=1 width=92) (actual time=0.027..0.031 rows=1 loops=10000)
          Index Cond: ((group_id = group_span.group_id) AND 
(group_span.valid_period && valid_period))
  Planning time: 2.160 ms
  Execution time: 335.820 ms
(14 rows)

test=*#


better?

Okay, other solution. The problem is the nested loop, we can disable that:


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=771.15..1121.33 rows=1 width=196) (actual 
time=23.291..32.028 rows=10000 loops=1)
    Hash Cond: (member_span.group_id = app.group_id)
    Join Filter: (group_span.valid_period && member_span.valid_period)
    Rows Removed by Join Filter: 2000
    ->  Seq Scan on member_span  (cost=0.00..305.00 rows=12000 width=92) 
(actual time=0.019..1.577 rows=12000 loops=1)
    ->  Hash  (cost=771.00..771.00 rows=12 width=104) (actual 
time=23.254..23.254 rows=10000 loops=1)
          Buckets: 16384 (originally 1024)  Batches: 1 (originally 1)  
Memory Usage: 1486kB
          ->  Hash Join  (cost=319.00..771.00 rows=12 width=104) (actual 
time=7.968..18.951 rows=10000 loops=1)
                Hash Cond: (group_span.group_id = app.group_id)
                Join Filter: (app.app_time <@ group_span.valid_period)
                Rows Removed by Join Filter: 2000
                ->  Seq Scan on group_span  (cost=0.00..257.00 
rows=12000 width=59) (actual time=0.010..2.068 rows=12000 loops=1)
                ->  Hash  (cost=194.00..194.00 rows=10000 width=45) 
(actual time=7.900..7.900 rows=10000 loops=1)
                      Buckets: 16384  Batches: 1  Memory Usage: 910kB
                      ->  Seq Scan on app  (cost=0.00..194.00 rows=10000 
width=45) (actual time=0.011..3.165 rows=10000 loops=1)
  Planning time: 1.241 ms
  Execution time: 32.676 ms
(17 rows)

test=*#






Regards, Andreas

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