suggestions to improve postgresql suitability for data-mining

Dear PostgreSQL developers,

I have considered postgresql for data-mining, and I would like to
share some comments on the issues involved, that result in several
specific suggestions for improvements in various areas. I've notice
that some of the issues seems listed in the todo list, so it may be
an encouragement to implement them earlier;-)

I) Data-Mining
--------------

First, I call data-mining the bulk processing of a large amount of
data to compute some small summary for analysis.

Let us say you have a "client" table with some data about 10M clients
and an "invoice" table with 120M invoices for these clients. It may
look like this (I put INTEGER everywhere to simplify):

CREATE TABLE client (
id INTEGER PRIMARY KEY,
area INTEGER, -- from 0 to 99
type INTEGER -- from 0 to 3
);

CREATE TABLE invoice (
client INTEGER REFERENCES client,
amount INTEGER, -- how much money was asked for
month INTEGER -- the month the invoice was sent, from 0 to 11
);

You want to process all invoices to count them and to sum up the amounts
on a per month/area/type basis. The initial data size is in GB,
but the size of the expected result is in KB (namely 2 data for each
100 areas * 12 months * 4 types).

II) SQL
-------

The first idea is to ask SQL to do the job with a 'group by' clause:

SELECT area, type, month, SUM(amount), COUNT(*)
FROM client AS c, invoice AS i
WHERE c.id=i.client
GROUP BY area, type, month;

As I am just interested in reading the data, without any transaction, I
tuned a little bit the database parameters (fsync=false, more shared_mem
and sort_mem).

It works, but it is quite slow and it requires a lot of disk space.
Indeed, the result of the join is big, and the aggregation seems to
require an external sort step so as to sum up data one group after the
other.

As the resulting table is very small, I wish the optimizer would have
skipped the sort phase, so as to aggregate the data as they come after the
join. All may be done on the fly without much additionnal storage (well,
with some implementation efforts). Maybe it is the "hash evaluation of
group by aggregates" item listed in the todo list.

III) PL/pgSQL
-------------

Ok, if postgresql does not want to do it my way, let us make it do it.
Thus I wrote some PL/pgSQL function for my purpose, something like:

CREATE TEMPORARY TABLE tmp (
area INTEGER,
type INTEGER,
month INTEGER,
amount INTEGER,
count INTEGER,
PRIMARY KEY(area, type, month)
);
-- initialize tmp
FOR i IN 0..99 LOOP FOR j IN 0..3 LOOP FOR k IN 0..11 LOOP
INSERT INTO tmp VALUES(i,j,k,0,0);
END all LOOPs;
-- fill tmp
FOR tuple IN
SELECT area, type, month, amount FROM client, invoice WHERE id=client
LOOP
UPDATE tmp SET amount=amount+tuple.amount, count=count+1
WHERE area=tuple.area AND type=tuple.type AND month=tuple.month
END LOOP;
...

It is very SLOOOOOOOOOOOWWWWWWWWW... 10 to 100 times slower than the
previous one. Exit PL/pgSQL.

IV) Basic client side (JDBC, DBI, libpq)
----------------------------------------

Then I wrote the same stuff on the client side in java with JDBC, perl
with DBI and C with libpq, by browsing the above SELECT in a simple
loop and aggregating the data directly in the language. In all 3
cases, the process attempts to allocate the full result of the client
and invoice join in memory... a **very** bad idea indeed!

I checked that the postgres client-server protocol does not allow to
chunk the result of a select, as only one response is sent for one
query.

I suggest that this behavior should be changed, as the ODBC/DBI/JDBC
interfaces are designed to allow the client to process data as the
come out of the database, even if the query is not finished yet.

The library should do the chunking on its own automatically, either by
doing a CURSOR/FETCH's manually in the library implementation on
SELECT, or by changing the protocol so that results are sent by chunks
when required.

This is listed in the todo list of the JDBC interface, but there is
nothing about the perl interface nor the libpq interface.

V) Less basic client side (DBI, libpq)
--------------------------------------

I've redone the previous stuff, but with an explicit CURSOR and a
FETCH loop. It worked better, but it is still slow and still requires
a lot of disk space. Indeed, the database seems to first generate the
join in a temporary table on disk (I need twice as much disk space
available as the original base), which is then sent back to the client.
Thus I pay a read/write/read of the whole tables although
I had hoped that reading the data only once would have been enough.

I would suggest to make processing data on the fly be done really
on the fly, not with an intermediate storage and providing just
an on-the-fly interface without the real thing behind. I haven't seen
any item in the todo list about this issue. I'm not sure it is really
easy to implement.

Conclusion
----------

I have not succeeded in getting from postgresql the performances
I was expecting for data-mining.

I could get them if postgresql could be improved on some or all
of the following items:

(1) the execution engine may aggregate grouped data without a sort in
some cases.

(2) the PL/pgSQL interpreter would be a great deal faster.

(3) the client programming interfaces would provide a real on-the-fly
(without intermediate storage) fetching mecanism.

(4) Also, I noticed that temporary tables/indexes created by postgresql
when processing a request are stored in the same partition as the
database in use. What about "/tmp" or other partitions? Maybe
a set of other directories could be designated for this purpose?

Hope this help... at least to add new items to the postgresql todo list;-)

Have a nice day,

--
Fabien.