Re: Query optimization

Quoting Siva Kumar <tech(at)leatherlink(dot)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