Re: inet/cidr indexes almost not used

Bruce,

I did SET enable_seqscan to off before EXPLAINs, of course.
Optimizer ignored this SET, and I cannot compare seq scan with index one.

I quoted two EXPLAINs below again: WHERE i<<'IP' and WHERE 'IP'>>i is
logically equivalent, but are planned differently


(BTW I thought that inet/cidr ops >>,>>=,<<,<<= cannot be used with indexes at all
until I had read thread 'inet regression test' in c.d.p.hackers a couple days ago)

Thank you for your support.

Bruce Momjian wrote:
> OK, see the FAQ on index usage and run some tests.
>
> I have just added the following to our FAQ section on index usage:
>
>     <P>If you believe the optimizer is incorrect in choosing a
>     sequential scan, use <CODE>SET enable_seqscan TO 'off'</CODE> and
>     run tests to see if an index scan is indeed faster.</P>
>
> Gleb Kouzmenko wrote:
[...]
>>test=# set enable_seqscan to off;
>>SET
 >>test=# explain select * from inet_tbl where i<<'192.168.1.0/24'::cidr;
 >>                                   QUERY PLAN
 >>-------------------------------------------------------------------------------
 >>  Index Scan using inet_idx1 on inet_tbl  (cost=0.00..4.68 rows=7 width=64)
 >>    Index Cond: ((i > '192.168.1.0/24'::inet) AND (i <= '192.168.1.255'::inet))
 >>    Filter: (i << '192.168.1.0/24'::inet)
 >>(2 rows)
 >>
 >>test=# explain select * from inet_tbl where '192.168.1.0/24'::cidr>>i;
 >>                                QUERY PLAN
 >>-------------------------------------------------------------------------
 >>  Seq Scan on inet_tbl  (cost=100000000.00..100000001.17 rows=7 width=64)
 >>    Filter: ('192.168.1.0/24'::inet >> i)
 >>(2 rows)
[...]