Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-hackers-owner+M257525=pgsql-hackers-arkari@postgresql.org>)
 id 1XTEF9-0002Tz-5Q
 for pgsql-hackers@arkaria.postgresql.org; Sun, 14 Sep 2014 18:09:47 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-hackers-owner+M257525=pgsql-hackers-arkari@postgresql.org>)
 id 1XTEF7-0006E9-Oi
 for pgsql-hackers@arkaria.postgresql.org; Sun, 14 Sep 2014 18:09:45 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <emre@hasegeli.com>) id 1XTEF4-0006Dv-RF
 for pgsql-hackers@postgresql.org; Sun, 14 Sep 2014 18:09:43 +0000
Received: from mail-lb0-x22c.google.com ([2a00:1450:4010:c04::22c])
 by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <emre@hasegeli.com>) id 1XTEEv-0002Lg-Ld
 for pgsql-hackers@postgresql.org; Sun, 14 Sep 2014 18:09:40 +0000
Received: by mail-lb0-f172.google.com with SMTP id w7so3345021lbi.3
 for <pgsql-hackers@postgresql.org>; Sun, 14 Sep 2014 11:09:30 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=hasegeli.com;
 s=google;
 h=date:from:to:cc:subject:message-id:references:mime-version
 :content-type:content-disposition:in-reply-to:user-agent;
 bh=2nEtqAJvRxJrRXlnyPr8J1LfdxaZJ7I2yt+hzimUbTI=;
 b=DOtfh4nbqHlLps0wGFJw9J0rZuTzFZFh1H5KYDMnUpODCcMKnCQtRbzsi9SC/rbqGr
 m4TtJGRxi4xn5YSo/9TZZCJdY1VkvLK6D0H6huTUUU8315hZHCiSN2vCMg8sWIjZMR8B
 6FvXZR0zvtDwYD+0GgQcoSMACNR11bjs36zZg=
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
 d=1e100.net; s=20130820;
 h=x-gm-message-state:date:from:to:cc:subject:message-id:references
 :mime-version:content-type:content-disposition:in-reply-to
 :user-agent;
 bh=2nEtqAJvRxJrRXlnyPr8J1LfdxaZJ7I2yt+hzimUbTI=;
 b=TiE30uKZu3433gLxwuoYWVNwaQLfo1A5IBuDGS/mmnGO3PIiLq+GsRiN8VHLDMIyim
 RTJGptmusqakQxYR1q12hFzIZ0c8MElIECZ7rePI05n9mLOGhBkmL9pHTY6T4I5dgLHk
 cCShcXev9r/dzFb8Ipo9qWZDeI4Li2eCiApkprVuR9rtkaYZ5SobC5YmfMLNb0kmkBkL
 uUdam8fXBXBORWw/hWlSjzQfrr8SGPF07IdejTnYN62PN0+1tdBLtYNTOC4xsvW6e5uA
 H86XINEmrYSxz3tWbDK9wFNIaYbWvYa5W7vjEjdcTXkLhORm5eBOv+vP3hOTiZxsePhu
 0nWw==
X-Gm-Message-State: 
 ALoCoQkFqplPOi8WkypaH9oe3Bp9bq7TUBWDpcTppXwrZ1mP4ydp8T7Id+4LNbR+Tw4OqFGtLvPA
X-Received: by 10.112.130.68 with SMTP id oc4mr8917624lbb.41.1410718170230;
 Sun, 14 Sep 2014 11:09:30 -0700 (PDT)
Received: from hasegeli.com ([24.133.146.176])
 by mx.google.com with ESMTPSA id p8sm1332320lap.24.2014.09.14.11.09.27
 for <multiple recipients>
 (version=TLSv1.2 cipher=RC4-SHA bits=128/128);
 Sun, 14 Sep 2014 11:09:28 -0700 (PDT)
Date: Sun, 14 Sep 2014 21:09:36 +0300
From: Emre Hasegeli <emre@hasegeli.com>
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>,
 Heikki Linnakangas <hlinnakangas@vmware.com>
Subject: Re: KNN-GiST with recheck
Message-ID: <20140914180936.GA5084@hasegeli.com>
References: 
 <CAPpHfdvn5jxgh-rVMz7c2vXoYW_W7dGrJjQkVAtaCr=-ZMCADA@mail.gmail.com>
 <20140803134809.GA58181@hasegeli-2.local>
 <CAPpHfdu3JmBrsgsrtaAQJMpXyMgrW+T2P7Gmf9oP8L1V7zLvOg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="opJtzjQTFsWo+cga"
Content-Disposition: inline
In-Reply-To: 
 <CAPpHfdu3JmBrsgsrtaAQJMpXyMgrW+T2P7Gmf9oP8L1V7zLvOg@mail.gmail.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
X-Pg-Spam-Score: -2.0 (--)
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


--opJtzjQTFsWo+cga
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline

I added the point to polygon distance operator patch to the open
CommitFest as ready for committer and added myself as reviewer to
both of the patches.

> I think that for most use cases just some operators require further sorting
> and some of them not. But it could appear one day that some index gives
> part of its knn answers exact and part of them inexact. Same happen to
> recheck of regular operators. Initially recheck flag was defined in
> opclass. But later recheck became runtime flag.

I cannot think of an use case, but it makes sense to add the flag to
the distance function just like the consistent function if we will go
with this implementation.
 
> Cost estimation of GiST is a big problem anyway. It doesn't care (and
> can't) about amount of recheck for regular operators. In this patch, same
> would be for knn recheck. The problem is that touching heap from access
> method breaks incapsulation. One idea about this is to do sorting in
> another nodes. However, I wonder if it would be an overengineering and
> overhead. In attached patch I propose a different approach: put code
> touching heap into separate index_get_heap_values function. Also new
> version of patch includes regression tests and some cleanup.

While looking it at I found a bug.  It returns the second column
in wrong order when both of the distance functions return recheck = true.
Test script attached to run on the regression database.  I tried to
fix but could not.  searchTreeItemDistanceRecheck function is not
very easy to follow.  I think it deserves more comments.

--opJtzjQTFsWo+cga
Content-Type: application/x-sql
Content-Disposition: attachment;
 filename="knn-gist-recheck-test-multicolumn.sql"
Content-Transfer-Encoding: quoted-printable

drop table if exists gpolygon_circle_tbl;=0A=
drop table if exists seqscan_result;=0A=
drop table if exists indexscan_result;=0A=
=0A=
create table gpolygon_circle_tbl as=0A=
select polygon(a.home_base) as poly,=0A=
	circle(b.home_base) as circ=0A=
from slow_emp4000 as a,=0A=
	lateral (select * from slow_emp4000 as b=0A=
			order by a.home_base <-> b.home_base limit 2) as b;=0A=
=0A=
create table seqscan_result (id serial, poly polygon, circ circle);=0A=
create table indexscan_result (id serial, poly polygon, circ circle);=0A=
=0A=
insert into seqscan_result=0A=
select row_number(*) over (), *=0A=
from (select * from gpolygon_circle_tbl=0A=
	order by poly <-> '(0,0)'::=0A=
	point, circ <-> '(200,300)'::point) as a;=0A=
=0A=
create index on gpolygon_circle_tbl=0A=
using gist (poly, circ);=0A=
=0A=
set enable_seqscan =3D false;=0A=
=0A=
insert into indexscan_result=0A=
select row_number(*) over (), *=0A=
from (select * from gpolygon_circle_tbl=0A=
	order by poly <-> '(0,0)'::point, circ <-> '(200,300)'::point) as a;=0A=
=0A=
-- Should not return a row.=0A=
select *=0A=
from seqscan_result join indexscan_result using (id)=0A=
where seqscan_result.poly::text !=3D indexscan_result.poly::text or=0A=
		seqscan_result.circ::text !=3D indexscan_result.circ::text;=0A=

--opJtzjQTFsWo+cga
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--opJtzjQTFsWo+cga--