Hi, The short version: I have a database where I converted an integer primary key column to a custom base type that pretty much amounts to a wrapper around an integer, and now some queries are resulting in much slower query plans. Does Postgres have special optimizations for integers that are not available for custom types, or did I perhaps overlook something? The longer version: I have implemented a custom base type that encodes a "type" (which has a fixed number of values) and a smaller integer into a single 4-byte value. I've been using this type with success in some parts of the database - it's much faster than a record type and more convenient than using multiple columns - so I'm trying to extend its use to more tables. The full implementation is available online: SQL: https://g.blicky.net/vndb.git/tree/sql/vndbid.sql?id=30070e326f18789f8b82252090b269166d5ade22 C: https://g.blicky.net/vndb.git/tree/sql/c/vndbfuncs.c?id=30070e326f18789f8b82252090b269166d5ade22 But now I'm running into cases where queries that used to perform really well suddenly end up getting a much worse query plan. As an example, observe the following query, run after doing a VACUUM FULL ANALYZE. The fast version with integer columns: => 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=5635.45..5635.46 rows=1 width=8) (actual time=1.273..1.274 rows=1 loops=1) Buffers: shared hit=904 -> Nested Loop (cost=4145.94..5631.93 rows=1410 width=0) (actual time=0.525..1.249 rows=301 loops=1) Buffers: shared hit=904 -> HashAggregate (cost=4145.65..4159.59 rows=1394 width=4) (actual time=0.515..0.579 rows=301 loops=1) Group Key: traits_chars.cid Batches: 1 Memory Usage: 81kB Buffers: shared hit=301 -> Bitmap Heap Scan on traits_chars (cost=19.79..4142.12 rows=1410 width=4) (actual time=0.078..0.426 rows=301 loops=1) Recheck Cond: (tid = ANY ('{1957,75}'::integer[])) Heap Blocks: exact=295 Buffers: shared hit=301 -> Bitmap Index Scan on traits_chars_tid (cost=0.00..19.43 rows=1410 width=0) (actual time=0.039..0.039 rows=301 loops=1) Index Cond: (tid = ANY ('{1957,75}'::integer[])) Buffers: shared hit=6 -> Index Only Scan using chars_pkey1 on chars c (cost=0.29..1.07 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=301) Index Cond: (id = traits_chars.cid) Heap Fetches: 0 Buffers: shared hit=603 Planning: Buffers: shared hit=190 Planning Time: 0.650 ms Execution Time: 1.372 ms (23 rows) Same query, but now the chars.id and traits_chars.cid are of the custom 'vndbid' type: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=2019373.51..2019373.52 rows=1 width=8) (actual time=2273.986..2273.987 rows=1 loops=1) Buffers: shared hit=2917 -> Nested Loop Semi Join (cost=19.71..2019370.01 rows=1400 width=0) (actual time=0.227..2273.965 rows=301 loops=1) Join Filter: (c.id = traits_chars.cid) Rows Removed by Join Filter: 28788543 Buffers: shared hit=2917 -> Seq Scan on chars c (cost=0.00..3573.94 rows=95794 width=4) (actual time=0.007..7.717 rows=95794 loops=1) Buffers: shared hit=2616 -> Materialize (cost=19.71..4125.57 rows=1400 width=4) (actual time=0.000..0.010 rows=301 loops=95794) Buffers: shared hit=301 -> Bitmap Heap Scan on traits_chars (cost=19.71..4118.57 rows=1400 width=4) (actual time=0.080..0.438 rows=301 loops=1) Recheck Cond: (tid = ANY ('{1957,75}'::integer[])) Heap Blocks: exact=295 Buffers: shared hit=301 -> Bitmap Index Scan on traits_chars_tid (cost=0.00..19.36 rows=1400 width=0) (actual time=0.042..0.042 rows=301 loops=1) Index Cond: (tid = ANY ('{1957,75}'::integer[])) Buffers: shared hit=6 Planning: Buffers: shared hit=178 Planning Time: 0.565 ms Execution Time: 2274.181 ms (21 rows) The row estimates for the traits_chars subquery are nearly identical in both plans and the row estimates for the chars table in the second plan is accurate, which leads me to suspect that this is not a statistics issue. I suspected that my custom type may be missing some operators or functions needed for the execution of the faster query plan, so I experimented with implementing the equalimage btree and 64bit salted hash support functions, but neither affected the query plan in any way. Am I perhaps missing something else? What other avenues can I try to investigate these slower queries? Both databases are running in a single PostgreSQL 13.2 instance on Gentoo. For further reference, in case it matters, the full schema (the integer version of it, the custom type version is identical except for some columns having 'vndbid' instead of 'integer') is defined in https://g.blicky.net/vndb.git/tree/sql/tableattrs.sql?id=30070e326f18789f8b82252090b269166d5ade22 and https://g.blicky.net/vndb.git/tree/sql/tableattrs.sql?id=30070e326f18789f8b82252090b269166d5ade22
