From: Jochem van Dieten <jochemd@oli.tudelft.nl>
To: Siva Kumar <tech@leatherlink.net>
Cc: pgsql-general@postgresql.org
Subject: Re: Query optimization
Date: Fri, 4 Oct 2002 14:42:19 +0200
Message-ID: <1033735339.3d9d8cab8b2f8@webmail.oli.tudelft.nl> (raw)
In-Reply-To: <200210041756.35489.tech@leatherlink.net>
References: <200210041347.55055.tech@leatherlink.net>
<200210041114.17241.dev@archonet.com>
<200210041756.35489.tech@leatherlink.net>
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
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-general@postgresql.org
Cc: jochemd@oli.tudelft.nl, tech@leatherlink.net
Subject: Re: Query optimization
In-Reply-To: <1033735339.3d9d8cab8b2f8@webmail.oli.tudelft.nl>
* 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