Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-hackers-owner+M257685=pgsql-hackers-arkari@postgresql.org>)
 id 1XUAMA-0003co-6f
 for pgsql-hackers@arkaria.postgresql.org; Wed, 17 Sep 2014 08:12:54 +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+M257685=pgsql-hackers-arkari@postgresql.org>)
 id 1XUAM8-0006Pz-Ib
 for pgsql-hackers@arkaria.postgresql.org; Wed, 17 Sep 2014 08:12:52 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <emre@hasegeli.com>) id 1XUAM6-0006Po-P4
 for pgsql-hackers@postgresql.org; Wed, 17 Sep 2014 08:12:51 +0000
Received: from mail-la0-x230.google.com ([2a00:1450:4010:c03::230])
 by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <emre@hasegeli.com>) id 1XUAM2-0000pk-EB
 for pgsql-hackers@postgresql.org; Wed, 17 Sep 2014 08:12:49 +0000
Received: by mail-la0-f48.google.com with SMTP id ty20so1330751lab.21
 for <pgsql-hackers@postgresql.org>; Wed, 17 Sep 2014 01:12:44 -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=emG/NwJD/vKoKyofp44k9D4GfBSJwp0oJeyQx99gL4Q=;
 b=O2sRxPKxiJo+wSumTFU6n7o2kgBzCdoThWnd+sgmddx/FSBcWil7GSvlRXNei0dRnN
 QyZQf7JN95+3ZltbKaytZaGsNpqCQTfu6YCpHD/deKQRrTMycjcdsF8KIvDTDjaDsv0N
 kheSplsP2YOGMnwMx5su2hkjebHPMyV+U/lvs=
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=emG/NwJD/vKoKyofp44k9D4GfBSJwp0oJeyQx99gL4Q=;
 b=lJf6pUd7Adn+FJrorZzDgSb+ACbww+FzEriyJM465jkt3RZFoaZS32YgB0rxfN+E7A
 OERbqHSBROEsgisBW1nRlg8rqrKyPUsbj4mdJkF4fucfivfCDbRO7QGv1ZRnby/CCi/V
 gvhnwlpoQq90traG8UW/YvHPsY7Z/r7c6Wooy1D2x5Lyc/+ypDq6vbJxFDQNOOJXp/H7
 0MOxMP73QA58NGA2n3DLu3De5ZvpsZ/6XK0BJXhQPgXGytlxZ45cTuLos/g37oEf5xET
 g7Y0/GKcqSNqsrlTj464nfesH88CWK/p3oLoHiSXG8F9VAX4tMu0eSKd79reFW+MSWkW
 Jacg==
X-Gm-Message-State: 
 ALoCoQkzqMFye+N5d+HHbiTtfW70LHJz3CGkhMrLigAfUrBtqfPSXpw7wvQOb/4l4ctujYP4Vims
X-Received: by 10.152.9.132 with SMTP id z4mr35056407laa.8.1410941564054;
 Wed, 17 Sep 2014 01:12:44 -0700 (PDT)
Received: from hasegeli.com ([24.133.146.176])
 by mx.google.com with ESMTPSA id ki7sm1422621lac.38.2014.09.17.01.12.38
 for <multiple recipients>
 (version=TLSv1.2 cipher=RC4-SHA bits=128/128);
 Wed, 17 Sep 2014 01:12:39 -0700 (PDT)
Date: Wed, 17 Sep 2014 11:12:50 +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: <20140917081250.GA28651@hasegeli.com>
References: 
 <CAPpHfdvn5jxgh-rVMz7c2vXoYW_W7dGrJjQkVAtaCr=-ZMCADA@mail.gmail.com>
 <20140803134809.GA58181@hasegeli-2.local>
 <CAPpHfdu3JmBrsgsrtaAQJMpXyMgrW+T2P7Gmf9oP8L1V7zLvOg@mail.gmail.com>
 <20140914180936.GA5084@hasegeli.com>
 <CAPpHfdvEpZAJrMBkNrCguG4fCK5QtZjARPti7LnL5chsSuD=Sg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="82I3+IH0IqGh5yIs"
Content-Disposition: inline
In-Reply-To: 
 <CAPpHfdvEpZAJrMBkNrCguG4fCK5QtZjARPti7LnL5chsSuD=Sg@mail.gmail.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
X-Pg-Spam-Score: -2.7 (--)
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


--82I3+IH0IqGh5yIs
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline

> > 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.
> 
> Fixed, thanks. It was logical error in comparison function implementation.

I managed to break it again by ordering rows only by the second column
of the index.  Test script attached.

--82I3+IH0IqGh5yIs
Content-Type: application/x-sql
Content-Disposition: attachment;
 filename="knn-gist-recheck-test-secondcolumn.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 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 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=

--82I3+IH0IqGh5yIs
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

--82I3+IH0IqGh5yIs--