From: Josh Berkus <josh@agliodbs.com>
To: Richard Sydney-Smith <richard@ibisaustralia.com>
To: pgsql-sql@postgresql.org
Subject: Re: Slow sub-selects, max and count(*)
Date: Wed, 4 Feb 2004 21:13:01 -0800
Message-ID: <200402042113.01369.josh@agliodbs.com> (raw)
In-Reply-To: <001001c3eb8a$255d6dc0$5801a8c0@athlon2000>
References: <001001c3eb8a$255d6dc0$5801a8c0@athlon2000>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, richard@ibisaustralia.com
Subject: Re: Slow sub-selects, max and count(*)
In-Reply-To: <200402042113.01369.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox