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)
[...]