Re: Custom base type and suboptimal query plans

(forgot to cc the list, sorry for the duplicate, Tom!)

On 2021-02-23, Tom Lane wrote:
> Ayo <ayo(at)blicky(dot)net> writes:
> > 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 slow query isn't using the chars_pkey1 index, which makes one
> wonder if you have a corresponding index in the custom-type case,

The index exists and is usable in the custom-type case:

=> explain (analyze,buffers) SELECT count(*) FROM chars WHERE id BETWEEN 'c1000' AND 'c2000';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33.87..33.88 rows=1 width=8) (actual time=0.252..0.253 rows=1 loops=1)
Buffers: shared hit=9
-> Index Only Scan using chars_pkey1 on chars (cost=0.29..31.47 rows=959 width=0) (actual time=0.042..0.179 rows=1001 loops=1)
Index Cond: ((id >= 'c1000'::vndbid) AND (id <= 'c2000'::vndbid))
Heap Fetches: 0
Buffers: shared hit=9
Planning:
Buffers: shared hit=97
Planning Time: 0.383 ms
Execution Time: 0.308 ms
(10 rows)

> or if you fat-fingered something about the index operator class
> for the custom type. As of v13 I don't think there's anything in
> that area that custom types can't replicate ... but there certainly
> is plenty of infrastructure for the standard types that you'll need
> to build out if you want equivalent functionality.

Good to know that this ought to be possible, at least. Is there
documentation about what infrastructure exists and how it interacts with
the planner? I've built upon
https://www.postgresql.org/docs/13/xindex.html and implemented
everything that seemed relevant for the type. No doubt I've missed
something, but I can't really tell what that may be.