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 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly