Re: Slow sub-selects, max and count(*)

Richard,

> The issue is with the speed ( or lackof it) that the procedure proceeds.
Apparently it is the subselects that are the worst issue and I have tried to
replace these. Also max() and count() refuse to use indexes.

Max() and Count() cannot use indexes for technical reasons. Browse through
the archives of SQL, PERFORM, and HACKERS for about 12,000 discussions on the
subject.

> Please, I am sure that there is a better way to do this. And 5 to 7 minutes
to insert a day is really too slow. Each days import table contains about
3200 records. Total table size for fsechist is about 2.5 million records.

Standard advice:
1) Where possible, use COPY and not INSERT for bulk imports.
2) Where COPY is not possible, group inserts into 1000-statement blocks and
wrap them in a transaction.
3) Where safe, suspend all triggers, foriegn keys, and constraints on the
table while inserting and re-apply them afterward.

--
-Josh Berkus
Aglio Database Solutions
San Francisco