Re: Interesting speed anomaly

On Wed, 14 Dec 2005, Zoltan Boszormenyi wrote:

> Tom Lane írta:
>
> >Zoltan Boszormenyi <zboszor(at)dunaweb(dot)hu> writes:
> >
> >
> >>$ time echo "select * from v_invoice_browse where code||inv_no = 'CARO200000020'" | dbaccess db
> >>
> >>
> >
> >
> >
> >>Is there a way to speed this operation up?
> >>
> >>
> >
> >Make an expression index on "code||inv_no", if you think this case is
> >important enough to be worth maintaining an extra index for.
> >
> >(This is not on-topic for -hackers, IMHO. Try pgsql-perform.)
> >
> > regards, tom lane
> >
> >
> >
>
> Thanks for both the hint and the pointer to the mailing list.
> My problem is, I can't see how could I create any index on a view.
> PostgreSQL refuses it:
>
> create index iinvbrowse1 on v_invoice_browse ((code||inv_no));
> ERROR: "v_invoice_browse" is not a table
>
> Creating indexes on the 12 invoice tables, like this:
>
> create index iinvoice1 on invoice1 (('PREFIX'||id));

Are you creating the index on (core || id) on on the string 'PREFIX' or
some other literal?

Gavin