Thread: Composite Index Structure
Hi all,<br /><br />I want to construct an "Composite Index Structure" i.e. a combination of gist and btree. <br />What iam thinking is that first creating a Rtree structure that is pointing to another Btree structure.<br /> For example, Supposei want to find vehicles between 2 to 4 pm on 14/2/2011 on X road.<br /><br />I am thinking of creating rtree structurefor road network and then btree for time. For reaching X road i use Rtree, and from there btree begin i.e. leafnode of rtree contains the pointer to root node of btree ( in this way i have all time belonging to X road) <br /><br/>My question is that how to implement this composite index structure in postgres?<br /><br />Let us suppose, if i createmygist index, then i have to write my own operator class?<br />or <br />can i use gist index as it is and btree treeas it is. I mean their operator class and their gist methods but how to establish linkage between them?<br /><br />Anyidea ??<br /><br />Thanks<br /><font color="#888888">Raj</font>
On 07.03.2011 08:07, Nick Raj wrote: > I want to construct an "Composite Index Structure" i.e. a combination of > gist and btree. > What i am thinking is that first creating a Rtree structure that is pointing > to another Btree structure. > For example, Suppose i want to find vehicles between 2 to 4 pm on 14/2/2011 > on X road. > > I am thinking of creating rtree structure for road network and then btree > for time. For reaching X road i use Rtree, and from there btree begin i.e. > leaf node of rtree contains the pointer to root node of btree ( in this way > i have all time belonging to X road) > > My question is that how to implement this composite index structure in > postgres? > > Let us suppose, if i create mygist index, then i have to write my own > operator class? > or > can i use gist index as it is and btree tree as it is. I mean their operator > class and their gist methods but how to establish linkage between them? It sounds like a use case for a multi-column gist index. See btree_gist contrib module. You'll want something like: CREATE INDEX ... USING gist (coordinates, time) -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Hi,
Thanks for your suggestion, Heikki. Btree_gist module provides Gist implementation for various different data types, equivalent to btree. Correct me if i wrong?
create index ..... using gist (coordinates,time). It will create index on both but i think there would be no linkage between them.
But i want linkage between location and time tree. I want for each location i have Btree like
Rtree root
/ \
non-leaf .......
/ \ \
leaf ...... leaf
| |
Btree root Btree root
/ \ / \
non-leaf
/
leaf
How to create this kind of structure?
What are the modification is required for this?
Thanks
Raj
Thanks for your suggestion, Heikki. Btree_gist module provides Gist implementation for various different data types, equivalent to btree. Correct me if i wrong?
create index ..... using gist (coordinates,time). It will create index on both but i think there would be no linkage between them.
But i want linkage between location and time tree. I want for each location i have Btree like
Rtree root
/ \
non-leaf .......
/ \ \
leaf ...... leaf
| |
Btree root Btree root
/ \ / \
non-leaf
/
leaf
How to create this kind of structure?
What are the modification is required for this?
Thanks
Raj
On Mon, Mar 7, 2011 at 12:33 PM, Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote:
It sounds like a use case for a multi-column gist index. See btree_gist contrib module. You'll want something like:On 07.03.2011 08:07, Nick Raj wrote:I want to construct an "Composite Index Structure" i.e. a combination of
gist and btree.
What i am thinking is that first creating a Rtree structure that is pointing
to another Btree structure.
For example, Suppose i want to find vehicles between 2 to 4 pm on 14/2/2011
on X road.
I am thinking of creating rtree structure for road network and then btree
for time. For reaching X road i use Rtree, and from there btree begin i.e.
leaf node of rtree contains the pointer to root node of btree ( in this way
i have all time belonging to X road)
My question is that how to implement this composite index structure in
postgres?
Let us suppose, if i create mygist index, then i have to write my own
operator class?
or
can i use gist index as it is and btree tree as it is. I mean their operator
class and their gist methods but how to establish linkage between them?
CREATE INDEX ... USING gist (coordinates, time)
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On 07.03.2011 11:11, Nick Raj wrote: > create index ..... using gist (coordinates,time). It will create index on > both but i think there would be no linkage between them. > But i want linkage between location and time tree. I want for each location > i have Btree like > > Rtree root > / \ > non-leaf ....... > / \ \ > leaf ...... leaf > | | > Btree root Btree root > / \ / \ > non-leaf > / > leaf > > How to create this kind of structure? A multi-column index is quite similar to that, you should get the same performance characteristics. The leading column will determine the primary order of the tree, and for rows that have the same value in the leading column, the second column will determine the order among them. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com