Hi,
I am trying to prove whether PostgreSQL is faster than Informix
so I can feed the management with numbers.
In our system, there is an invoice browser view, an UNION of 12
different tables. (Yes, there are 12 different invoices, like new or
second-hand cars, warranty, service, etc, with in/out directions,
all have to be counted from 1 starting each year, e.g 200500000001.
The view contains a constant field that is the so called invoice prefix,
e.g. CARO is CAR-OUT, invoice of sold new cars and so on.
SELECT * or SELECT COUNT(*) from this view for listing all invoices
is overall faster.
When I search for only one invoice, knowing the prefix and the invoice number
is more interesting, however.
Informix results:
************************************************
$ time echo "select * from v_invoice_browse where code = 'CARO' and inv_no = 200000020" | dbaccess db
Database selected.
...
1 row(s) retrieved.
Database closed.
real 0m1.263s
user 0m0.530s
sys 0m0.000s
$ time echo "select * from v_invoice_browse where code||inv_no = 'CARO200000020'" | dbaccess db
Database selected.
...
1 row(s) retrieved.
Database closed.
real 0m7.942s (varying between 7.5 and 14 seconds)
user 0m0.510s
sys 0m0.000s
************************************************
PostgreSQL results:
************************************************
$ time echo "select * from v_invoice_browse where code = 'CARO' and inv_no = 200000020" |psql db
...
(1 row)
real 0m0.061s
user 0m0.000s
sys 0m0.010s
$ time echo "select * from v_invoice_browse where code||inv_no = 'CARO200000020'" |psql db
...
(1 row)
real 0m18.158s (varying between about 18 and 24 seconds)
user 0m0.000s
sys 0m0.020s
************************************************
The timing of the first query varied very little between five runs.
The timing variations of the second query is indicated above,
it naturally depends on other system activities.
Is there a way to speed this operation up? Maybe it could be known whether
a field in a view is constant, or it can only have limited values, like in
this situation where we have an union of tables, and every member of the
union has a constant in that field. Or there may be other ways to speed up
comparing concatenated values.
Best regards,
Zoltán Böszörményi