pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIndexing an array?
5+ messages / 5 participants
[nested] [flat]
* Indexing an array?
@ 2005-09-08 16:13 Silke Trissl <trissl@informatik.hu-berlin.de>
2005-09-08 16:26 ` Re: Indexing an array? Josh Berkus <josh@agliodbs.com>
2005-09-08 16:33 ` Re: Indexing an array? Tom Lane <tgl@sss.pgh.pa.us>
2005-09-09 23:35 ` Re: Indexing an array? Ron Mayer <rm_pg@cheapcomplexdevices.com>
0 siblings, 3 replies; 5+ messages in thread
From: Silke Trissl @ 2005-09-08 16:13 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Indexing an array?
2005-09-08 16:13 Indexing an array? Silke Trissl <trissl@informatik.hu-berlin.de>
@ 2005-09-08 16:26 ` Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2005-09-08 16:26 UTC (permalink / raw)
To: pgsql-sql; +Cc: Silke Trissl <trissl@informatik.hu-berlin.de>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Indexing an array?
2005-09-08 16:13 Indexing an array? Silke Trissl <trissl@informatik.hu-berlin.de>
@ 2005-09-08 16:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2005-09-08 16:33 UTC (permalink / raw)
To: Silke Trissl <trissl@informatik.hu-berlin.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Indexing an array?
2005-09-08 16:13 Indexing an array? Silke Trissl <trissl@informatik.hu-berlin.de>
@ 2005-09-09 23:35 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2 siblings, 0 replies; 5+ messages in thread
From: Ron Mayer @ 2005-09-09 23:35 UTC (permalink / raw)
To: Silke Trissl <trissl@informatik.hu-berlin.de>
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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Indexing an array?
@ 2005-09-08 16:34 Dmitri Bichko <dbichko@aveopharma.com>
0 siblings, 0 replies; 5+ messages in thread
From: Dmitri Bichko @ 2005-09-08 16:34 UTC (permalink / raw)
To: Silke Trissl <trissl@informatik.hu-berlin.de>; pgsql-sql
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 confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-09-09 23:35 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-08 16:13 Indexing an array? Silke Trissl <trissl@informatik.hu-berlin.de>
2005-09-08 16:26 ` Josh Berkus <josh@agliodbs.com>
2005-09-08 16:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-09-09 23:35 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2005-09-08 16:34 Re: Indexing an array? Dmitri Bichko <dbichko@aveopharma.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox