Hi Nix,
The problem is, that while doing the vacuum full ANALYZE the table was
empty. It therefore gathered statistics of a situation which isn't there
anymore when you fill up the table. In an empty or small table, it is
normal to do sequential scans. Which you most of the time don't want in
a large table.
My suggestion is to VACUUM (FULL) the table after you've deleted the
data. Then fill up the table and do a ANALYZE when you're done filling it.
That way, the analysis of the data will be much more accurate. My guess
is, it'll use indexes much sooner and be much faster. Have a look at the
output of EXPLAIN ANALYZE yourstatement; before doing ANALYZE and after.
Best regards,
Arjen
On 26-11-2004 7:35, Nikola Milutinovic wrote:
> Hi all.
>
> I have the following:
>
> - Mandrake Linux 9.1
> - PostgreSQL 7.3.2 MDK5
>
> There is one DB and one DB user. The DB is cleared and loaded with the
> data of same volume each month (monthly report). The volume is not small
> and it usually takes 3 hours to load. Loading is done with SQL files
> which use transactions, 10,000 SQL statements per transaction.
>
> A couple of days ago, disk became full, since we were not doing VACUUM
> on the DB at all. So, I deleted all records from the 3 tables the DB has
> and performed "VACUUM FULL ANALYZE". This reclaimed the space.
>
> My problem is that the load is now taking (to my estimate) 20 times more!
>
> Anything I could do to find out what's going on? There is nothing in the
> logs that I can see.
>
> Nix.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
