Thread: Indexing an array?

Hi,

I have a problem with arrays in Postgres. I want to create a really
large array, lets say 3 billion characters long.

As far I could read from the documentation - this should be possible.
But my question is, is there a kind of index on the array.

Lets say, I want to get element 2,675,345,328. Does Postgres have to
load the entire array into memory and then run through the 2.6 billion
characters to return the one I want or does Postgres have an index - as
where to find this element on disk?

Any advice is welcome and thanks in advance
Silke Trißl

Silke,

> I have a problem with arrays in Postgres. I want to create a really
> large array, lets say 3 billion characters long.

Change your application design.  

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Really seems like that array should be a separate table, then Postgres would definitely know how to index it.

Dmitri

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Silke Trissl
> Sent: Thursday, September 08, 2005 12:14 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Indexing an array?
>
>
> Hi,
>
> I have a problem with arrays in Postgres. I want to create a
> really large array, lets say 3 billion characters long.
>
> As far I could read from the documentation - this should be
> possible. But my question is, is there a kind of index on the array.
>
> Lets say, I want to get element 2,675,345,328. Does Postgres
> have to load the entire array into memory and then run
> through the 2.6 billion characters to return the one I want
> or does Postgres have an index - as where to find this
> element on disk?
>
> Any advice is welcome and thanks in advance
>
>     Silke Trißl
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
The information transmitted is intended only for the person or entity to which it is addressed and may contain
confidentialand/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any
actionin reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you
receivedthis in error, please contact the sender and delete the material from any computer 

Silke Trissl <trissl@informatik.hu-berlin.de> writes:
> I have a problem with arrays in Postgres. I want to create a really
> large array, lets say 3 billion characters long.

Forget it --- quite aside from indexing inefficiencies, the max size of
an array (or any other single field) is just 1Gb.  Don't try to use
arrays to replace tables.
        regards, tom lane

Silke Trissl wrote:
> As far I could read from the documentation - this should be possible.
> But my question is, is there a kind of index on the array.

If your needs are a bit more modest (say, a few thousands instead of 
billions) the stuff in contrib/intarray works well; and if you
needed types other than integers you can you can look at intarray
to see how to build indexes on them and what those indexes
are useful for.

> Lets say, I want to get element 2,675,345,328. Does Postgres have to
> load the entire array into memory and then run through the 2.6 billion
> characters to return the one I want or does Postgres have an index - as
> where to find this element on disk?

Wouldn't you rather expect an index to be useful for finding which
rows match your query rather than for finding content within a row?