Improve PostgreSQL query performance for 100 millions of data

Optimization theory for counting all records:

1. remove the unnecessary field in SELECT query
2. remove ORDER BY ASC/DES portion(saves 7% - 10%)
3. remove aggregate functions(avg, sum, count etc)
4. Use standard VACUUM to reclaim storage occupied by dead tuples.
5. Research the "EXPLAIN ANALYZE [your_query_here]" result from http://explain.depesz.com/

Explanation No. 1: remove unnecessary field in SELECT query

select count(*) from ( SELECT
    HD.URINO
    /*HD.URIBRUI,
    HD.TCODE,
    HD.SQCODE*/
FROM
    TV_HD HD)

Explanation No. 2: remove ORDER BY ASC/DES portion(saves 7% - 10%)

select count(*) from ( SELECT
    HD.URINO
FROM
    TV_HD HD
    /*ORDER BY HD.URINO DESC*/)

Explanation No. 3: remove aggregate functions(avg, sum, count etc)

select count(*) from ( SELECT
    name
    /*MAX(salary),
    AVG(salary)*/
FROM Emp)

Explanation No. 4: Use standard VACUUM to reclaim storage occupied by dead tuples.

VACUUM (VERBOSE, ANALYZE) your_table;

In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to do VACUUM,periodically especially on frequently-updated tables.

There are two variants of VACUUM: standard VACUUM and.VACUUM FULL

VACUUM FULL can reclaim more disk space but runs much more slowly. Also, the standard form of VACUUM can run in parallel with production database operations. (Commands such as SELECT, INSERT, UPDATE, and DELETE will continue to function normally, though you will not be able to modify the definition of a table with commands such as ALTER TABLE while it is being vacuumed.) VACUUM FULL requires an exclusive lock on the table it is working on, and therefore cannot be done in parallel with other use of the table.

Generally, therefore, administrators should strive to use andstandard VACUUM avoid VACUUM FULL.

For details:

1. http://www.postgresql.org/docs/9.1/static/sql-vacuum.html
2. http://www.postgresql.org/docs/9.1/static/routine-vacuuming.html

Thanks for your time.

Original Link: Improve PostgreSQL query performance having left join for 100 millions of data