Message-ID: <1033735339.3d9d8cab8b2f8@webmail.oli.tudelft.nl>
Date: Fri,  4 Oct 2002 14:42:19 +0200
From: Jochem van Dieten <jochemd@oli.tudelft.nl>
To: Siva Kumar <tech@leatherlink.net>
Cc: pgsql-general@postgresql.org
Subject: Re: Query optimization
References: <200210041347.55055.tech@leatherlink.net>
	<200210041114.17241.dev@archonet.com>
	<200210041756.35489.tech@leatherlink.net>
In-Reply-To: <200210041756.35489.tech@leatherlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
User-Agent: Internet Messaging Program (IMP) 3.0

Quoting Siva Kumar <tech@leatherlink.net>:
>
> 
> Giving below the output of EXPLAIN ANALYSE. I could not make much
> sense out of 
> it, please help!
> 
> NOTICE:  QUERY PLAN:
> 
> Sort  (cost=1382.45..1382.45 rows=1000 width=442) (actual
> time=3.47..3.47 
> rows=3 loops=1)
>   ->  Merge Join  (cost=1263.12..1332.62 rows=1000 width=442) (actual
> 
> time=3.21..3.27 rows=3 loops=1)
>         ->  Index Scan using master_activity_pkey on master_activity
> ma  
> (cost=0.00..52.00 rows=1000 width=50) (actual time=0.06..0.09 rows=4
> loops=1)

EXPLAIN returns the way that PostgreSQL will try to get data from the 
different tables and indexes and combine that together to produce the 
query result. To choose the best way, PostgreSQL is dependent on 
statistical information on the content of the tables. Those statistics 
are not collected automatically, you need to tell PostgreSQL to collect 
these statistics.

Your EXPLAIN plan always returns an estimate of 1000 rows for each 
operation. This is usually an indication that you didn't gather 
statistics yet. You need to run ANALYZE first and then try again.
Also, take a look at the manual entry for VACUUM, which is another 
maintenance operation that can be critical for performance.

If your database is not in production, I would recommend you do a 
VACUUM FULL ANALYZE and then try the EXPLAIN again. If your database is 
in production, do VACUUM ANALYZE and read up on the FULL part so you 
know when to use that.
After that, see if performance got better and post the new EXPLAIN 
output.

Jochem