From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "Richard Sydney-Smith" <richard@ibisaustralia.com>,
	<pgsql-sql@postgresql.org>
Subject: Re: Slow sub-selects, max and count(*)
Date: Wed, 4 Feb 2004 21:13:01 -0800
User-Agent: KMail/1.5.4
References: <001001c3eb8a$255d6dc0$5801a8c0@athlon2000>
In-Reply-To: <001001c3eb8a$255d6dc0$5801a8c0@athlon2000>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200402042113.01369.josh@agliodbs.com>

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