Re: [BUGS] 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:
If you believe the optimizer is incorrect in choosing a
sequential scan, use SET enable_seqscan TO 'off' and
run tests to see if an index scan is indeed faster.
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=1.00..10001.17 rows=7 width=64)
>>Filter: ('192.168.1.0/24'::inet >> i)
>>(2 rows)
[...]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [BUGS] inet/cidr indexes almost not used
Gleb Kouzmenko <[EMAIL PROTECTED]> writes: > I quoted two EXPLAINs below again: WHERE i<<'IP' and WHERE 'IP'>>i is > logically equivalent, but are planned differently If you'd like to fix that, see match_special_index_operator() and expand_indexqual_conditions() in src/backend/optimizer/path/indxpath.c. I can't get very excited about it myself. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[BUGS] Bug #877: numbers are no longer recognized as smallints
Denis N. Stepanov ([EMAIL PROTECTED]) reports a bug with a severity of 2 The lower the number the more severe it is. Short Description numbers are no longer recognized as smallints Long Description I have bunch of functions (doesn't matter of which language) which take parameters as 'smallint's. I used to pass parameters into them as is, i.e. plain numbers without explicit typecasting. But while trying to upgrade from PostgreSQL 7.2.3 to 7.3.1 I discovered that this no longer works. All function calls now fail convincing me to add explicit typecasts. I believe this is incorrect since, say, 1 is a correct notation for smallint type and it should NOT require explicit typecase in each and every place. I tried to find the grounds of such change in 7.3.1 docs but did not succeed. Please let me know whether this is a bug or I just miss something important here. Sample Code testdasdb=# create function test(smallint) returns integer as 'select 0' language 'sql'; CREATE FUNCTION testdasdb=# select test(1); ERROR: Function test(integer) does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts No file was uploaded with this report ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
