I tried making the "ident" column a text instead of text[] in the beginning,
but searches were approximately of the same speed; so I voted for the array,
because this way there isn't even a possibility for the separator ("##") to
cause problems.
Anyway, the "ident BETWEEN ARRAY['foo', 'bar'] AND A
On Mon, Dec 7, 2009 at 7:01 AM, Sam Mason wrote:
> On Sat, Dec 05, 2009 at 09:54:58AM -0500, Merlin Moncure wrote:
>> GIN is a pretty heavy price to pay for something that should be btree
>> indexable. Also note he is using a multi column index with array as
>> second column...that would be prett
On Sat, Dec 05, 2009 at 09:54:58AM -0500, Merlin Moncure wrote:
> GIN is a pretty heavy price to pay for something that should be btree
> indexable. Also note he is using a multi column index with array as
> second column...that would be pretty awkward with GIN.
Yup, sounds as though it's not goi
without digging too much into the details - just a suggestion:
look at the ltree contrib. it actually provides an indexable array
prefix search.
2009/12/4 Denes Daniel :
> Hi,
>
> I have a table like this:
>
> CREATE TABLE test (
> type text NOT NULL,
> ident text[] NOT NULL,
> ...
On Sat, Dec 5, 2009 at 10:31 AM, Denes Daniel wrote:
> According to the documentation,
> http://www.postgresql.org/docs/8.4/static/functions-comparisons.html#ROW-WISE-COMPARISON
> "Note: Prior to PostgreSQL 8.2, the <, <=, > and >= cases were not handled
> per SQL specification."
> I think the way
2009/12/5 Merlin Moncure
> AFAIK, your approach is the only solution given your requirements. It
> works well...I've used it often, but usually for integers. Maybe
> there is a missing operator for arrays kinda similar to the contains
> operator that would be btree indexable.
Yes, I also thi
2009/12/5 Sam Mason
> Would a GIN index help? You'd be able to ask if a 'foo' appears
> anywhere in the array (or some subset if you want). You can then have a
> subsequent filter that actually expresses the clause you want. Not sure
> what selectivity you're dealing with and if this would be
On Sat, Dec 5, 2009 at 4:54 AM, Sam Mason wrote:
> On Sat, Dec 05, 2009 at 02:23:13AM +0100, Denes Daniel wrote:
>> 2009/12/4 Sam Mason
>> > CREATE INDEX test_my_idx ON test (type,(ident[1]));
>>
>> Sorry, but this approach is no good, since I may search like:
>> SELECT * FROM test WHERE type =
On Sat, Dec 05, 2009 at 02:23:13AM +0100, Denes Daniel wrote:
> 2009/12/4 Sam Mason
> > CREATE INDEX test_my_idx ON test (type,(ident[1]));
>
> Sorry, but this approach is no good, since I may search like:
> SELECT * FROM test WHERE type = 'three' AND (ident[1] = 'foo' AND ident[2] =
> 'bar');
>
On Fri, Dec 4, 2009 at 12:58 PM, Denes Daniel wrote:
> Hi,
>
> I have a table like this:
>
> CREATE TABLE test (
> type text NOT NULL,
> ident text[] NOT NULL,
> ...
> );
> ALTER TABLE ONLY test ADD CONSTRAINT test_pkey PRIMARY KEY (type, ident);
>
> and I would like to query rows that
2009/12/4 Sam Mason
> I think you want to create a functional index on ident[1], something
> like:
>
> CREATE INDEX test_my_idx ON test (type,(ident[1]));
Sorry, but this approach is no good, since I may search like:
SELECT * FROM test WHERE type = 'three' AND (ident[1] = 'foo' AND ident[2] =
On Fri, Dec 04, 2009 at 06:58:21PM +0100, Denes Daniel wrote:
> SELECT * FROM test WHERE type = 'two' AND ident[1] = 'test';
>
> this query uses the primary key index only for the "type" field, and
> then filters for ident[1]. Is there a way to make it use the index for the
> array prefix search t
12 matches
Mail list logo
