Re: Selectivity estimation for inet operators

From: Emre Hasegeli <emre(at)hasegeli(dot)com>
To: Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>
Cc: Dilip kumar <dilip(dot)kumar(at)huawei(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, Andreas Karlsson <andreas(at)proxel(dot)se>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: Selectivity estimation for inet operators
Date: 2014-08-31 17:57:10
Message-ID: 20140831175710.GA8990@hasegeli-2.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

> * Isn't "X >> Y" equivalent to "network_scan_first(X) < Y AND
> network_scan_last(X) > Y"? Or at least close enough for selectivity
> estimation purposes? Pardon my ignorance - I'm not too familiar with the
> inet datatype - but how about just calling scalarineqsel for both bounds?

Actually, "X >> Y" is equivalent to

network_scan_first(X) <= network_host(Y) AND
network_scan_last(X) >= network_host(Y) AND
network_masklen(X) < network_masklen(X)

but we do not have statistics for neither network_scan_last(X)
nor network_masklen(X). I tried to find a solution based on
the implementation of the operators.

> * inet_mcv_join_selec() is O(n^2) where n is the number of entries in the
> MCV lists. With the max statistics target of 10000, a worst case query on
> my laptop took about 15 seconds to plan. Maybe that's acceptable, but you
> went through some trouble to make planning of MCV vs histogram faster, by
> the log2 method to compare only some values, so I wonder why you didn't do
> the same for the MCV vs MCV case?

It was like that in the previous versions. It was causing worse
estimation, but I was trying to reduce both sides of the lists. It
works slightly better when only the left hand side of the list is
reduced. Attached version works like that.

> * A few typos: lenght -> length.


Thank you for looking at it.

Attachment Content-Type Size
inet-selfuncs-v9.patch text/plain 27.6 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Emre Hasegeli 2014-08-31 17:59:18 Re: Selectivity estimation for inet operators
Previous Message David G Johnston 2014-08-31 17:46:04 Re: [BUGS] Re: BUG #9555: pg_dump for tables with inheritance recreates the table with the wrong order of columns