Re: Interesting speed anomaly

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));

can be done but it doesn't seem to help, at least the query run time
doesn't decrease.
Remember, the view is an union on the 12 tables, the 'code' (invoice
prefix) field is
a fake constant field to distinguish between the different invoice types.
And we have the 'inv_no' field in the view but the serial fields in the
separate invoice tables
are called 'szam'. So there is no direct linkage between the view and
table field names,
except the view definition. That still leaves me wondering. Both
Informix and PostgreSQL
seems to do the query using sequential scan but the above WHERE
condition is computed
about two times faster in Informix, every other usual queries are faster
in PostgreSQL
about (and I really meant at least) five times than Informix.
That's why I sent it to pgsql-hackers, maybe the hackers are interested
in further improving
PostgreSQL. ;-)

I will ask on pqsql-performance, thanks.

Best regards,
Zoltán Böszörményi