> 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? -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
