Re: Custom base type and suboptimal query plans

On 2021-02-23, Tom Lane wrote:
> Have you tried "enable_seqscan = off" (and maybe also disable merge
> and hash joins) to see if you can force choice of that plan?

No luck. It uses the index now, but seemingly only to loop over it. The
integer version uses a HashAggregate, I must have missed something in my
implementation to make the planner avoid that node. Does it have any special
type requirements, other than the hash operator class?

set enable_seqscan = off;
set enable_hashjoin = off;
set enable_mergejoin = off;
EXPLAIN (ANALYZE,BUFFERS) SELECT count(*) FROM chars c WHERE c.id IN(SELECT cid FROM traits_chars WHERE tid IN(1957, 75));
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=2036624.55..2036624.56 rows=1 width=8) (actual time=2275.163..2275.164 rows=1 loops=1)
Buffers: shared hit=567
-> Nested Loop Semi Join (cost=20.10..2036621.02 rows=1412 width=0) (actual time=1.402..2275.143 rows=301 loops=1)
Join Filter: (c.id = traits_chars.cid)
Rows Removed by Join Filter: 28803593
Buffers: shared hit=567
-> Index Only Scan using chars_pkey1 on chars c (cost=0.29..2493.95 rows=95844 width=4) (actual time=0.016..5.955 rows=95844 loops=1)
Heap Fetches: 0
Buffers: shared hit=264
-> Materialize (cost=19.80..4154.68 rows=1412 width=4) (actual time=0.000..0.010 rows=301 loops=95844)
Buffers: shared hit=303
-> Bitmap Heap Scan on traits_chars (cost=19.80..4147.62 rows=1412 width=4) (actual time=0.085..0.636 rows=301 loops=1)
Recheck Cond: (tid = ANY ('{1957,75}'::integer[]))
Heap Blocks: exact=297
Buffers: shared hit=303
-> Bitmap Index Scan on traits_chars_tid (cost=0.00..19.45 rows=1412 width=0) (actual time=0.046..0.046 rows=301 loops=1)
Index Cond: (tid = ANY ('{1957,75}'::integer[]))
Buffers: shared hit=6
Planning:
Buffers: shared hit=179
Planning Time: 0.578 ms
Execution Time: 2275.328 ms
(22 rows)