Re: Query execution for intervals

From: Mikito Harakiri <mikharakiri_nospaum_at_yahoo.com>
Date: 9 Feb 2006 12:02:30 -0800
Message-ID: <1139515350.612291.89930_at_g47g2000cwa.googlegroups.com>

> -- both indexes
> select --+ index(i xi) index(i yi)
> count(1)
> from intervals i
> where 500 between x and y;
> 18 sec
>
> -- one index
> select --+ index(i xi)
> count(1)
> from intervals i
> where 500 between x and y;
> 16 sec

These queries actually have identical plans. The correct test is select --+ index(i xi) index(i yi)

       count(1)
from intervals i
where 50000 between x and y;
16 sec

select --+ index(i xi) no_index(i yi)
-----------------------------^^^^^^^^^^^^^^^^
       count(1)

from intervals i
where 50000 between x and y;
21 sec

select --+full(i)

       count(1)
from intervals i
where 50000 between x and y;
2.5 sec

These performance data are volatile, of course. Increasing number of interval bondary collisions would favor bitmapped plans. Increasing the number of colums in the table would slow down full table scan. Therefore, it is possible that bitmapped plans might outperform FTS. Still, querying intervals covering a point is nowhere as fast as finding a (small) set of points which belongs to an interval via B-Tree index scan. I assume this remains to be true even for fancier access methods developed in spatial area (e.g. R-tree). Received on Thu Feb 09 2006 - 21:02:30 CET