Re: Gist indexing performance with cidr types

> Are the coverage operatons just that expensive?

They shouldn't be. A similar query like yours works in 0.5 second on my laptop:

># create table inner_side as select i, ((random() * 255.5)::int::text || '.' || (random() * 255.5)::int::text || '.' || (random() * 255.5)::int::text || '.' || (random() * 255.5)::int::text || '/' || (random() * 16 + 9)::int::text)::inet::cidr as network from generate_series(1, 2300000) as i;
> SELECT 2300000
>
># create table outer_side as select i, ((random() * 255.5)::int::text || '.' || (random() * 255.5)::int::text || '.' || (random() * 255.5)::int::text || '.' || (random() * 255.5)::int::text || '/' || (random() * 16 + 9)::int::text)::inet::cidr as network from generate_series(1, 732) as i;
> SELECT 732
>
># create index on inner_side using gist(network inet_ops);
> CREATE INDEX
>
># analyze;
> ANALYZE
>
># explain analyze select * from outer_side join inner_side on outer_side.network && inner_side.network;
> QUERY PLAN
> ----------
> Nested Loop (cost=0.41..563927.27 rows=137310 width=22) (actual time=0.115..474.103 rows=561272 loops=1)
> -> Seq Scan on outer_side (cost=0.00..11.32 rows=732 width=11) (actual time=0.011..0.096 rows=732 loops=1)
> -> Index Scan using inner_side_network_idx on inner_side (cost=0.41..540.38 rows=23000 width=11) (actual time=0.031..0.553 rows=767 loops=732)
> Index Cond: ((outer_side.network)::inet && (network)::inet)
> Planning time: 0.830 ms
> Execution time: 505.641 ms
> (6 rows)

Maybe, something we haven't expected about your dataset causes a
performance regression on the index. Did you see anything relevant on
the server logs on index creation time?