Re: Interesting speed anomaly

On Thu, 15 Dec 2005, Gavin Sherry wrote:

> On Wed, 14 Dec 2005, Zoltan Boszormenyi wrote:
>
> > Tom Lane írta:
> >
> > >Zoltan Boszormenyi <zboszor@dunaweb.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?

Sorry, I sent this email instead of cancelling it. I take it 'code' is a
string generated by the query, for example: "select 'CAR' as code,* from
cars ...."? If so, it seems strange that we do not use the expressional
index. Could you send the output of explain analyze?

Thanks,

Gavin