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

From Andreas Kretschmer
Subject Re: Extremely slow when query uses GIST exclusion index
Date
Msg-id b65a76ae-b742-8de1-f13d-707b8a3cb94d@a-kretschmer.de
Whole thread Raw
In response to Re: Extremely slow when query uses GIST exclusion index  (David <dchau+postgresql@hioscar.com>)
List pgsql-performance

Am 29.08.2018 um 20:10 schrieb David:
>
> On Wed, Aug 29, 2018 at 7:25 AM, Andreas Kretschmer 
> <andreas@a-kretschmer.de <mailto:andreas@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 off permanently in production?

no, but you can switch off/on per session, for instance. and you can it 
set to on after that query.


>
>      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)
>
>
> Hm, also, it looks like one of the oddities of this query is that 
> PostgreSQL is severely underestimating the cardinality of the join.

ack, that's the main problem here, i think. It leads to the expensive 
nested loop. Tbh, i don't have a better suggestion now besides the 
workaround with setting nestloop to off.


Regards, Andreas

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



pgsql-performance by date:

Previous
From: David
Date:
Subject: Re: Extremely slow when query uses GIST exclusion index
Next
From: Mariel Cherkassky
Date:
Subject: trying to delete most of the table by range of date col