Thanks for your help investigating this! Follow-up below:
On Wed, Aug 29, 2018 at 7:25 AM, Andreas Kretschmer <andreas@xxxxxxxxxxxxxxx> 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 thought these settings were just for debugging, and once we've identified the culprit, we're supposed to take other steps (?) to avoid the suboptimal execution plan.
your GiST-Index contains (member_id,group_id,valid_period), but your query is only on the latter 2 fields.
Yeah, I didn't really want GIST index in the first place -- PostgreSQL created it automatically as a side effect of the exclusion constraint that I need.
Your suggestion to create *another* GIST index is an interesting workaround. But we've seen that the query runs even faster if we didn't have the GIST index(es) at all. So is there any way to tell the planner to avoid the GIST index altogether?
(Alternatively, could there be a bug that's causing PostgreSQL to underestimate the cost of using the GIST index?)
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. It seems to think that the join will result in only 1 row, when the join actually produces 10,000 rows. Maybe that's why the planner thinks that using the GIST index is cheap? (I.e., the planner thought that it would only need to do 1 GIST index lookup, which is cheaper than a sequential scan; but in reality it has to do 10,000 GIST index lookups, which is much more expensive than a scan.) Is there any way to help the planner better estimate how big the join output going to be?
