Thread: Indexing varchar[]'s

Indexing varchar[]'s

From

"Eric Ridge"

Date:

14 June 2001, 22:59:46

If this has been asked and answered before, I am sorry.  I've been
digging through the mail archives and cannot find this particular
question (although some releated to int64[]'s).  Is it possible, with
7.1.2, to create an index on a varchar[]?

I've tried:
# create index foo on mytable (myfield);
ERROR:  DefineIndex: type _varchar has no default operator class
     and
# create index foo on mytable (myfield varchar_ops);
ERROR:  DefineIndex: opclass "varchar_ops" does not accept datatype
"_varchar"
     and
# create index foo on docket (case_name[1]);
ERROR:  parser: parse error at or near "["


Am I missing something obvious, or can it just not be done right now?

If it's not possible right now, is it in the works?  If not, could
someone point me in the right direction for creating operator classes,
or briefly outline what would be necessary to do this?

I'm new to postgres and have just starting looking seriously at the
sources.

thanks in advance.

eric

Re: Indexing varchar[]'s

From

"Tim Barnard"

Date:

15 June 2001, 13:24:08

I'm running 7.1.2 and have no problem doing so.

ca=#create table test(fld1 varchar(20));
CREATE
ca=#create index test_pkey on test (fld1);
CREATE

Tim

----- Original Message -----
From: "Eric Ridge" <ebr@tcdi.com>
To: <pgsql-general@postgresql.org>
Sent: Thursday, June 14, 2001 4:59 PM
Subject: [GENERAL] Indexing varchar[]'s


> If this has been asked and answered before, I am sorry.  I've been
> digging through the mail archives and cannot find this particular
> question (although some releated to int64[]'s).  Is it possible, with
> 7.1.2, to create an index on a varchar[]?
>
> I've tried:
> # create index foo on mytable (myfield);
> ERROR:  DefineIndex: type _varchar has no default operator class
>      and
> # create index foo on mytable (myfield varchar_ops);
> ERROR:  DefineIndex: opclass "varchar_ops" does not accept datatype
> "_varchar"
>      and
> # create index foo on docket (case_name[1]);
> ERROR:  parser: parse error at or near "["
>
>
> Am I missing something obvious, or can it just not be done right now?
>
> If it's not possible right now, is it in the works?  If not, could
> someone point me in the right direction for creating operator classes,
> or briefly outline what would be necessary to do this?
>
> I'm new to postgres and have just starting looking seriously at the
> sources.
>
> thanks in advance.
>
> eric
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Re: Indexing varchar[]'s

From

Tom Lane

Date:

15 June 2001, 14:07:14

"Eric Ridge" <ebr@tcdi.com> writes:
> If this has been asked and answered before, I am sorry.  I've been
> digging through the mail archives and cannot find this particular
> question (although some releated to int64[]'s).  Is it possible, with
> 7.1.2, to create an index on a varchar[]?

No, there's no support for indexes on varchar arrays (or any other kind
of array).  What sorts of things do you think such an index should be
able to do?

            regards, tom lane

RE: Indexing varchar[]'s

From

"Eric Ridge"

Date:

15 June 2001, 17:11:55

I meant:

create table test(fld1 varchar(20)[])

can't index that.  :(

eric


> -----Original Message-----
> From: Tim Barnard [mailto:tbarnard@povn.com]
> Sent: Friday, June 15, 2001 10:04 AM
> To: Eric Ridge
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Indexing varchar[]'s
>
>
> I'm running 7.1.2 and have no problem doing so.
>
> ca=#create table test(fld1 varchar(20));
> CREATE
> ca=#create index test_pkey on test (fld1);
> CREATE
>
> Tim
>
> ----- Original Message -----
> From: "Eric Ridge" <ebr@tcdi.com>
> To: <pgsql-general@postgresql.org>
> Sent: Thursday, June 14, 2001 4:59 PM
> Subject: [GENERAL] Indexing varchar[]'s
>
>
> > If this has been asked and answered before, I am sorry.  I've been
> > digging through the mail archives and cannot find this particular
> > question (although some releated to int64[]'s).  Is it
> possible, with
> > 7.1.2, to create an index on a varchar[]?
> >
> > I've tried:
> > # create index foo on mytable (myfield);
> > ERROR:  DefineIndex: type _varchar has no default operator class
> >      and
> > # create index foo on mytable (myfield varchar_ops);
> > ERROR:  DefineIndex: opclass "varchar_ops" does not accept datatype
> > "_varchar"
> >      and
> > # create index foo on docket (case_name[1]);
> > ERROR:  parser: parse error at or near "["
> >
> >
> > Am I missing something obvious, or can it just not be done
> right now?
> >
> > If it's not possible right now, is it in the works?  If not, could
> > someone point me in the right direction for creating
> operator classes,
> > or briefly outline what would be necessary to do this?
> >
> > I'm new to postgres and have just starting looking seriously at the
> > sources.
> >
> > thanks in advance.
> >
> > eric
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
> >
>
>

Re: Indexing varchar[]'s

From

Tom Lane

Date:

15 June 2001, 17:16:44

"Eric Ridge" <ebr@tcdi.com> writes:
> If you've thought about this, maybe you could
> point me in the right direction?

I have not thought about it.

In current sources there is a contrib/intarray/ module, which may or may
not be of any use to you ...

            regards, tom lane

RE: Indexing varchar[]'s

From

"Eric Ridge"

Date:

15 June 2001, 17:18:37

> No, there's no support for indexes on varchar arrays (or any
> other kind
> of array).  What sorts of things do you think such an index should be
> able to do?

Thanks for responding so quickly.

On a really high level, an index just associates the value to a
particular row, no?  I would think an indexed array column would do the
same, except it would associate _each_ value from the array to that
particular row.

You might also want to only index a particular element, or a range of
elements from the array... instead of the entire thing.

Then you'd be able to do:

SELECT * FROM TABLE WHERE VARARR = 'Foo';

to get the all the rows that have 'Foo' somewhere in their VARARR column
w/o the need for the contrib/arrays package (which by the way is a very
useful package!).

I'm probably over simplifying.

Doing sequential scans (combined with the array iterator) against an
array column in a table with 8+ million records takes a little while...

What's the feasability of making this happen?  I've seen your name
associated with a lot of the code.  How difficult would it be for you to
implement?  That would give me an indiciation of how difficult it would
be for me to implement.  If you've thought about this, maybe you could
point me in the right direction?

thanks.

eric

Re: Indexing varchar[]'s

From

will trillich

Date:

16 June 2001, 03:14:04

On Fri, Jun 15, 2001 at 10:46:00AM -0400, Tom Lane wrote:
> "Eric Ridge" <ebr@tcdi.com> writes:
> > If this has been asked and answered before, I am sorry.  I've been
> > digging through the mail archives and cannot find this particular
> > question (although some releated to int64[]'s).  Is it possible, with
> > 7.1.2, to create an index on a varchar[]?
>
> No, there's no support for indexes on varchar arrays (or any other kind
> of array).  What sorts of things do you think such an index should be
> able to do?

i'd bet that it's likely that something could be done with a
subsidiary relation -- instead of

    create table burgeoning (
        id serial,
        stuff text,
        amt float8,
        xref int8,
        xyz varchar[],  -- hmm?
        primary key (id)
    );

maybe this would be a good alternative:

    create table streamline (
        id serial,
        stuff text,
        amt float8,
        xref int8,
        primary key (id)
    );
    create table subsidiary (
        id references streamline(id),
        xyz varchar   -- aha!
    );
    create view shebang as
        select
            streamline.*,
            subsidiary.xyz
        from
            streamline,
            subsidiary
        where
            streamline.id = subsidiary.id
    ;

may not work in some cases, but it's certainly easier on the
indexing, yes?

--
I figure: if a man's gonna gamble, may as well do it
without plowing.   -- Bama Dillert, "Some Came Running"

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

RE: Indexing varchar[]'s

From

"Eric Ridge"

Date:

16 June 2001, 03:33:28

> i'd bet that it's likely that something could be done with a
> subsidiary relation -- instead of

you're absolutely right, that would make the indexing work (with
postgres).  but using arrays is a design decision (good or bad).  and
it's a decision we make often (with another db product) when we have
tables with millions of records.  performance starts to suffer when you
join one 8.3 million record table with another 1.5 million record table.
Esp. when you have many fields that need to be "arrays".

plus, as an aside, since postgres supports arrays it just kinda makes
sense that you can be able to index 'em too.  on the surface it doesn't
sound too difficult to implement, but I don't understand the sources
enough to do it... yet.

what I really need is full text indexing.  the contrib/fti package seems
good, but the queries you have to generate are complicated.

eric