Thread: Index-only scans for multicolumn GIST

On 07/21/2014 10:47 PM, Anastasia Lubennikova wrote:
> Hi, hackers!
> There are new results of my work on GSoC project "Index-only scans for
> GIST".
> Previous post is here:
> http://postgresql.1045698.n5.nabble.com/Index-only-scans-for-GIST-td5804892.html
>
> Repository is
> https://github.com/lubennikovaav/postgres/tree/indexonlygist2
> Patch is in attachments.
> It includes indexonlyscan for multicolumn GiST. It works correctly -
> results are the same with another scan plans.
> Fetch() method is realized for box and circle opclasses
> Improvement for circle opclass is not such distinct as for box opclass,
> because of recheck.

For a circle, the GiST index stores a bounding box of the circle. The 
new fetch function reverses that, calculating the radius and center of 
the circle from the bounding box.

Those conversions lose some precision due to rounding. Are we okay with 
that? Floating point math is always subject to rounding errors, but 
there's a good argument to be made that it's not acceptable to get a 
different value back when the user didn't explicitly invoke any math 
functions.

As an example:

create table circle_tbl (c circle);
create index circle_tbl_idx on circle_tbl using gist (c);
insert into circle_tbl values ('1.23456789012345,1.23456789012345,1e300');

postgres=# set enable_seqscan=off; set enable_bitmapscan=off; set 
enable_indexonlyscan=on;
SET
SET
SET
postgres=# select * from circle_tbl ;       c
---------------- <(0,0),1e+300>
(1 row)

postgres=# set enable_indexonlyscan=off;
SET
postgres=# select * from circle_tbl ;                      c
---------------------------------------------- <(1.23456789012345,1.23456789012345),1e+300>
(1 row)

- Heikki

On Tue, Jul 22, 2014 at 2:55 AM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
> On 07/21/2014 10:47 PM, Anastasia Lubennikova wrote:
>>
>> Hi, hackers!
>> There are new results of my work on GSoC project "Index-only scans for
>> GIST".
>> Previous post is here:
>>
>> http://postgresql.1045698.n5.nabble.com/Index-only-scans-for-GIST-td5804892.html
>>
>> Repository is
>> https://github.com/lubennikovaav/postgres/tree/indexonlygist2
>> Patch is in attachments.
>> It includes indexonlyscan for multicolumn GiST. It works correctly -
>> results are the same with another scan plans.
>> Fetch() method is realized for box and circle opclasses
>> Improvement for circle opclass is not such distinct as for box opclass,
>> because of recheck.
>
>
> For a circle, the GiST index stores a bounding box of the circle. The new
> fetch function reverses that, calculating the radius and center of the
> circle from the bounding box.
>
> Those conversions lose some precision due to rounding. Are we okay with
> that? Floating point math is always subject to rounding errors, but there's
> a good argument to be made that it's not acceptable to get a different value
> back when the user didn't explicitly invoke any math functions.
>
> As an example:
>
> create table circle_tbl (c circle);
> create index circle_tbl_idx on circle_tbl using gist (c);
> insert into circle_tbl values ('1.23456789012345,1.23456789012345,1e300');
>
> postgres=# set enable_seqscan=off; set enable_bitmapscan=off; set
> enable_indexonlyscan=on;
> SET
> SET
> SET
> postgres=# select * from circle_tbl ;
>        c
> ----------------
>  <(0,0),1e+300>
> (1 row)
>
> postgres=# set enable_indexonlyscan=off;
> SET
> postgres=# select * from circle_tbl ;
>                       c
> ----------------------------------------------
>  <(1.23456789012345,1.23456789012345),1e+300>
> (1 row)

I really don't think it's ever OK for a query to produce different
answers depending on which plan is chosen.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Heikki Linnakangas <hlinnakangas@vmware.com> writes:
> For a circle, the GiST index stores a bounding box of the circle. The 
> new fetch function reverses that, calculating the radius and center of 
> the circle from the bounding box.

> Those conversions lose some precision due to rounding. Are we okay with 
> that?

That seems like a nonstarter :-(.  Index-only scans don't have a license
to return approximations.  If we drop the behavior for circles, how much
functionality do we have left?
        regards, tom lane

> That seems like a nonstarter :-(.  Index-only scans don't have a license
> to return approximations.  If we drop the behavior for circles, how much
> functionality do we have left?

It should work with exact operator classes, box_ops, point_ops,
range_ops, inet_ops.