INET operators and NOT

Tomas Cerha <t.cerha@sh.cvut.cz> writes:

Aplying the NOT operator with << INET operator results always in false.

accounting=> SELECT * FROM a;
ip
--------
10.1.1.1
10.1.1.2
10.2.1.2
10.2.1.1
(4 rows)

accounting=> SELECT * FROM a WHERE ip<<'10.1/16';
ip
--------
10.1.1.1
10.1.1.2
(2 rows)

accounting=> SELECT * FROM a WHERE NOT ip<<'10.1/16';
ip
--
(0 rows)

What's going on here is that the optimizer is simplifying "NOT x<<y"
(network_sub) into "x>>=y" (network_supeq), because the pg_operator
entry for << claims that >>= is its negator. This example demonstrates
that that ain't so.

Can anyone comment on whether any of the inet operators are actually the
correct negator of << ? For that matter, are inet's other commutator
and negator declarations just as broken?

regards, tom lane

From bouncefilter Tue Dec 28 20:55:59 1999
Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34])
by hub.org (8.9.3/8.9.3) with ESMTP id UAA32009
for <pgsql-hackers@postgreSQL.org>;
Tue, 28 Dec 1999 20:54:59 -0500 (EST) (envelope-from Inoue@tpf.co.jp)
Received: from cadzone ([126.0.1.40] (may be forged))
by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP
id KAA04651; Wed, 29 Dec 1999 10:54:53 +0900
From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "pgsql-hackers" <pgsql-hackers@postgreSQL.org>
Subject: RE: [HACKERS] can't link libpq.so(inet_aton() not found)
Date: Wed, 29 Dec 1999 11:00:08 +0900
Message-ID: <000201bf51a0$6eee6e40$2801007e@cadzone.tpf.co.jp>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-2022-jp"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4
In-Reply-To: <28369.946394358@sss.pgh.pa.us>
Importance: Normal

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

It seems fe-connect.c was changed to call inet_aton() recently.
I can't make executables linking libpq.so because my environ-
ment(i386-pc-solaris2.5.1, compiled by gcc 2.7.2.3) doesn't have
inet_aton().

Hmm. We could make libpq dependent on the substitute inet_aton
that's in backend/ports. But since this is only needed for a very
optional feature (and one I don't much care for ;-)), my inclination
is to just #ifdef it out, and not support pghostaddr on machines
without inet_aton.

I agree to $ifdef it out.
It seems bad to lower the independecy of libpq by such a optional
feature.

I don't maintain current source tree in i386-pc-solaris.
After a long time I tried to install current tree to my i386-pc-solaris
and found 3 errors.

1) this one
2) psql compile error due to old verison readline as Jan already
reported.
3) initdb error due to "id -u" error as Keith already reported.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

From bouncefilter Tue Dec 28 21:56:00 1999
Received: from munn.com (kmunn.munn.com [209.123.248.252])
by hub.org (8.9.3/8.9.3) with ESMTP id VAA43580
for <pgsql-hackers@postgresql.org>;
Tue, 28 Dec 1999 21:55:06 -0500 (EST) (envelope-from kmunn@munn.com)
Received: from localhost (kmunn@localhost)
by munn.com (8.9.1/8.9.1) with ESMTP id VAA08081
for <pgsql-hackers@postgresql.org>; Tue, 28 Dec 1999 21:54:59 -0500
Date: Tue, 28 Dec 1999 21:54:50 -0500 (EST)
From: Kristofer Munn <kmunn@munn.com>
To: pgsql-hackers@postgresql.org
Subject: Index Puzzle for you
Message-ID: <Pine.LNX.4.04.9912282140110.2149-100000@munn.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

Hi all. Once again I come to you with a puzzle...

I have the following structures (related to this question) in my
[PostgreSQL 6.5.2 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66]
database according to a pg_dump of the schema (reformatted for
readability):

---------------------------------------------------------------------------

CREATE TABLE "tblissuearticle" (
"ixissue" int4 NOT NULL,
"ixarticle" int4 NOT NULL,
"ixprocessor" int4,
"ixmember" int4,
"iorder" int4,
"sstatus" character);

REVOKE ALL on "tblissuearticle" from PUBLIC;

CREATE INDEX "tblissuearticle_oid" on "tblissuearticle"
using btree ("oid" "oid_ops" );

CREATE INDEX "tblissuearticle_idx1" on "tblissuearticle"
using btree (
"ixissue" "int4_ops",
"ixarticle" "int4_ops",
"iorder" "int4_ops"
);

CREATE INDEX "tblissuearticle_idx2" on "tblissuearticle"
using btree ("ixissue" "int4_ops" );

---------------------------------------------------------------------------

Now I enter trusty psql to run some SQL statements. Notice the SECOND
EXPLAIN which I run. I aded the _idx2 index above after this statement
didn't catch _idx1 (partial index). Neither parts matched. I tried
dropping _idx1 and it still didn't use _idx2.

---------------------------------------------------------------------------

mail=> vacuum tblissuearticle ;
VACUUM
mail=> vacuum analyze tblissuearticle ;
VACUUM
mail=> explain select 1 from tblissuearticle where ixissue = 7
and ixarticle = 9;
NOTICE: QUERY PLAN:

Index Scan using tblissuearticle_idx1 on tblissuearticle
(cost=228.04 rows=1 width=0)

EXPLAIN
mail=> explain select 1 from tblissuearticle where ixissue = 7;
NOTICE: QUERY PLAN:

Seq Scan on tblissuearticle (cost=4076.63 rows=76338 width=0)

EXPLAIN
mail=> explain verbose select 1 from tblissuearticle where ixissue = 7;
NOTICE: QUERY DUMP:

{ SEQSCAN :cost 4076.63 :size 76338 :width 0 :state <> :qptargetlist ({
TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname
"?column?" :reskey 0 :reskeyop 0 :resgroupref 0 :resjunk false } :expr {
CONST :consttype 23 :constlen 4 :constisnull false :constvalue 4 [ 1 0 0 0
] :constbyval true }}) :qpqual ({ EXPR :typeOid 0 :opType op :oper { OPER
:opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1
:vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST
:consttype 23 :constlen 4 :constisnullfalse :constvalue 4 [ 7 0 0 0 ]
:constbyval true })}) :lefttree <> :righttree <> :extprm () :locprm ()
:initplan <> :nprm 0 :scanrelid 1 }

NOTICE: QUERY PLAN:

Seq Scan on tblissuearticle (cost=4076.63 rows=76338 width=0)

EXPLAIN

---------------------------------------------------------------------------

Hoping someone can shed some light on this for me. Happy Holidays...

- K

Kristofer Munn * KMI * 973-509-9414 * AIM KrMunn * ICQ 352499 * www.munn.com

From bouncefilter Wed Dec 29 00:51:02 1999
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id AAA80059
for <pgsql-hackers@postgreSQL.org>;
Wed, 29 Dec 1999 00:50:27 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id AAA03229;
Wed, 29 Dec 1999 00:49:12 -0500 (EST)
To: Kristofer Munn <kmunn@munn.com>
cc: pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Index Puzzle for you
In-reply-to: <Pine.LNX.4.04.9912282140110.2149-100000@munn.com>
References: <Pine.LNX.4.04.9912282140110.2149-100000@munn.com>
Comments: In-reply-to Kristofer Munn <kmunn@munn.com>
message dated "Tue, 28 Dec 1999 21:54:50 -0500"
Date: Wed, 29 Dec 1999 00:49:12 -0500
Message-ID: <3226.946446552@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Kristofer Munn <kmunn@munn.com> writes:

[ why does the second example not use an index? ]

mail=> explain select 1 from tblissuearticle where ixissue = 7
and ixarticle = 9;

Index Scan using tblissuearticle_idx1 on tblissuearticle
(cost=228.04 rows=1 width=0)

mail=> explain select 1 from tblissuearticle where ixissue = 7;

Seq Scan on tblissuearticle (cost=4076.63 rows=76338 width=0)

The thing that jumps out at me from this example is the much larger
estimate of returned rows in the second case. The planner is clearly
estimating that "ixissue = 7" alone is not very selective. That might
or might not be reasonable (how many rows are in the table, and what's
the actual distribution of ixissue values?), but if it is reasonable
then a sequential scan might indeed be the right choice. Index scans
are not always better than sequential scans --- the planner's job would
be far simpler if they were ;-)

regards, tom lane

From bouncefilter Wed Dec 29 02:14:03 1999
Received: from munn.com (kmunn.munn.com [209.123.248.252])
by hub.org (8.9.3/8.9.3) with ESMTP id CAA95874
for <pgsql-hackers@postgreSQL.org>;
Wed, 29 Dec 1999 02:13:56 -0500 (EST) (envelope-from kmunn@munn.com)
Received: from localhost (kmunn@localhost)
by munn.com (8.9.1/8.9.1) with ESMTP id CAA09939;
Wed, 29 Dec 1999 02:10:44 -0500
Date: Wed, 29 Dec 1999 02:10:36 -0500 (EST)
From: Kristofer Munn <kmunn@munn.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Index Puzzle for you
In-Reply-To: <3226.946446552@sss.pgh.pa.us>
Message-ID: <Pine.LNX.4.04.9912290204480.9369-100000@munn.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

Tom Lane wrote:

The thing that jumps out at me from this example is the much larger
estimate of returned rows in the second case. The planner is clearly

Good catch! There were 296 possible issues the table. One had 86,544
articles associated with it. The next highest was 5,949. Then the
numbers drop to 630, 506, 412, 184 and then the rest are all under 62.
Out of curiosity, how does vacuum decide on the large estimate?

The maximum is 86,544.
The average row return for ixissue = x is 3412.
The median is 25.
The mode is 25.

ixissue is the result of a sequence.

Thanks for the heads up on this...

- K

Kristofer Munn * KMI * 973-509-9414 * AIM KrMunn * ICQ 352499 * www.munn.com

I still see this problem in 7.0.

[ Charset ISO-8859-2 unsupported, converting... ]

Your name : Tomas Cerha
Your email address : t.cerha@sh.cvut.cz
----------------------------------------------------------------------
System Configuration
----------------------------------------------------------------------
Architecture (example: Intel Pentium) : Intel Pentium MMX
Operating System (example: Linux 2.0.26 ELF) : Linux 2.2.5-15 ELF
PostgreSQL version (example: PostgreSQL-6.5.3): PostgreSQL-6.5.3
Compiler used (example: gcc 2.8.0) : installed from RPMs
installed packages:
postgresql-6.5.3-1.i386.rpm
postgresql-perl-6.5.3-1.i386.rpm
postgresql-server-6.5.3-1.i386.rpm
postgresql-tcl-6.5.3-1.i386.rpm
postgresql-test-6.5.3-1.rpm
----------------------------------------------------------------------
I've already posted this bug, but nobody replied yet ... I was not
subscribed, but now I am. If it is not significant, I'm sorry to
overload this
mailing list... Now to the problem:

----------------------------------------------------------------------
Please enter a FULL description of your problem:
----------------------------------------------------------------------

Aplying the NOT operator with << INET operator results always in false.
See the example below:

This is the contents of table a:

accounting=> SELECT * FROM a;
ip
--------
10.1.1.1
10.1.1.2
10.2.1.2
10.2.1.1
(4 rows)

Now, let's select only those hosts from subnet '10.1/16': (works fine)

accounting=> SELECT * FROM a WHERE ip<<'10.1/16';
ip
--------
10.1.1.1
10.1.1.2
(2 rows)

And now, I only apply NOT to prewious statement ....

accounting=> SELECT * FROM a WHERE NOT ip<<'10.1/16';
ip
--
(0 rows)

But that is not true! I tryed this also with other versions of postgress
on other machines and the result was always the same. But this makes all
about INET operators quite unusable, when I am not able to exclude some
address space (I can only include them). Or is there another way to do
It?

----------------------------------------------------------------------
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

CREATE TABLE a (ip inet);

INSERT INTO a VALUES ('10.1.1.1');
INSERT INTO a VALUES ('10.1.1.2');
INSERT INTO a VALUES ('10.2.1.2');
INSERT INTO a VALUES ('10.2.1.1');

SELECT * FROM a;
SELECT * FROM a WHERE ip<<'10.1/16';
SELECT * FROM a WHERE NOT ip<<'10.1/16';

DROP TABLE a;

----------------------------------------------------------------------
Thank you for any idea which could help to solve this problem ...

Tom Cerha, student, FEE CTU Prague Czech Republic

************

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Here is Tom Lane's comment.

Tomas Cerha <t.cerha@sh.cvut.cz> writes:

Aplying the NOT operator with << INET operator results always in false.

accounting=> SELECT * FROM a;
ip
--------
10.1.1.1
10.1.1.2
10.2.1.2
10.2.1.1
(4 rows)

accounting=> SELECT * FROM a WHERE ip<<'10.1/16';
ip
--------
10.1.1.1
10.1.1.2
(2 rows)

accounting=> SELECT * FROM a WHERE NOT ip<<'10.1/16';
ip
--
(0 rows)

What's going on here is that the optimizer is simplifying "NOT x<<y"
(network_sub) into "x>>=y" (network_supeq), because the pg_operator
entry for << claims that >>= is its negator. This example demonstrates
that that ain't so.

Can anyone comment on whether any of the inet operators are actually the
correct negator of << ? For that matter, are inet's other commutator
and negator declarations just as broken?

regards, tom lane

************

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

What's going on here is that the optimizer is simplifying "NOT x<<y"
(network_sub) into "x>>=y" (network_supeq), because the pg_operator
entry for << claims that >>= is its negator. This example demonstrates
that that ain't so.

Can anyone comment on whether any of the inet operators are actually the
correct negator of << ? For that matter, are inet's other commutator
and negator declarations just as broken?

I did take out the demonstrably incorrect negator links for 7.0.
We still have those other issues about CIDR/INET types though...

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I still see this problem in 7.0.

You do? I don't:

regression=# SELECT * FROM a;
ip
----------
10.1.1.1
10.1.1.2
10.2.1.2
10.2.1.1
(4 rows)

regression=# SELECT * FROM a WHERE ip<<'10.1/16';
ip
----------
10.1.1.1
10.1.1.2
(2 rows)

regression=# SELECT * FROM a WHERE NOT ip<<'10.1/16';
ip
----------
10.2.1.2
10.2.1.1
(2 rows)

regression=#

regards, tom lane

Sorry, I got the 2's and 1's mixed up. Yes, fixed.

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I still see this problem in 7.0.

You do? I don't:

regression=# SELECT * FROM a;
ip
----------
10.1.1.1
10.1.1.2
10.2.1.2
10.2.1.1
(4 rows)

regression=# SELECT * FROM a WHERE ip<<'10.1/16';
ip
----------
10.1.1.1
10.1.1.2
(2 rows)

regression=# SELECT * FROM a WHERE NOT ip<<'10.1/16';
ip
----------
10.2.1.2
10.2.1.1
(2 rows)

regression=#

regards, tom lane

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026