Re: Selectivity estimation for inet operators

From: Emre Hasegeli <emre(at)hasegeli(dot)com>
To: Dilip kumar <dilip(dot)kumar(at)huawei(dot)com>
Cc: 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-07-12 17:54:51
Message-ID: 20140712175451.GA25414@hasegeli-2.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

> I have one last comment, after clarifying this I can move it to "ready for committer".
> 1. In networkjoinsel, For avoiding the case of huge statistics, only some of the values from mcv and histograms are used (calculated using SQRT).
> -- But in my opinion, if histograms and mcv both are exist then its fine, but if only mcv's are there in that case, we can match complete MCV, it will give better accuracy.
> In other function like eqjoinsel also its matching complete MCV.

I was not sure of reducing statistics, at all. I could not find any
other selectivity estimation function which does this. After testing
it some more, I reached the conclusion that it would be better to
only reduce the values of the outer loop on histogram match. Now it
matches complete MCV lists to each other. I also switched back to
log2() from sqrt() to make the outer list smaller.

I rethink your previous advice to threat histogram bucket partially
matched when the constant matches the last boundary, and changed it
that way. It is better than using the selectivity for only one value.
Removing this part also make the function more simple. The new
version of the patch attached.

While looking at it I find some other small problems and fixed them.
I also realized that I forgot to support other join types than inner
join. Currently, the default estimation is used for anti joins.
I think the patch will need more than trivial amount of change to
support anti joins. I can work on it later. While doing it, outer
join selectivity estimation can also be improved. I think the patch
is better than nothing in its current state.

Attachment Content-Type Size
inet-selfuncs-v7.patch text/plain 22.3 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2014-07-12 22:38:23 Re: Extending MSVC scripts to support --with-extra-version
Previous Message Magnus Hagander 2014-07-12 17:39:28 Re: Extending MSVC scripts to support --with-extra-version