Re: Extremely slow when query uses GIST exclusion index

2018-08-29 Thread Andreas Kretschmer
Am 29.08.2018 um 20:10 schrieb David: On Wed, Aug 29, 2018 at 7:25 AM, Andreas Kretschmer mailto:andr...@a-kretschmer.de>> wrote: Okay, other solution. The problem is the nested loop, we can disable that: test=*# set enable_nestloop to false; Is it OK to keep this of

Re: Extremely slow when query uses GIST exclusion index

2018-08-29 Thread David
Thanks for your help investigating this! Follow-up below: On Wed, Aug 29, 2018 at 7:25 AM, Andreas Kretschmer wrote: > > Okay, other solution. The problem is the nested loop, we can disable that: >> > test=*# set enable_nestloop to false; Is it OK to keep this off permanently in production? I t

Re: Extremely slow when query uses GIST exclusion index

2018-08-29 Thread Andreas Kretschmer
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.v

Re: Extremely slow when query uses GIST exclusion index

2018-08-29 Thread Andreas Kretschmer
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_in

Extremely slow when query uses GIST exclusion index

2018-08-28 Thread David
Hi. My databases make heavy use of timestamp ranges, and they rely on GIST exclusion constraints to ensure that the ranges are disjoint. I've noticed that queries that hit the GIST indexes are EXTREMELY slow, and the queries run much faster if I make trivial changes to avoid the GIST indexes. Here