General Hints
- do not use syslog ! It slows down query a lot
Statistics target
edit file /etc/postgresql/8.1/main/postgresql.conf Sets the default statistics target for table columns that have not had a column-specific target set via ALTER TABLE SET STATISTICS. Larger values increase the time needed to do ANALYZE, but may improve the quality of the planner's estimates. The default is 10, it can be increased to 100, for example (maximum is 1000).
Logging commands
To trace the commands sent to a PostgreSQL server, edit file /etc/postgresql/8.1/main/postgresql.conf (replace 8.1 by your version number):
log_statement = 'all' # none, mod, ddl, all
Now, all commands sent to the server will be logged. Example:
2007-05-02 15:45:29 CEST LOG: statement: select * from _format ;
Analyzing queries
The most useful tool in tuning your database is the SQL command EXPLAIN ANALYZE. This allows you to profile each SQL query your application performs and see exactly how the PostgreSQL planner will process the query.
prelude=> explain analyze SELECT t0.time, t0.gmtoff, t0.usec FROM Prelude_Alert AS top_table LEFT JOIN Prelude_CreateTime AS t0 ON (t0._parent_type='A' AND
t0._message_ident=top_table._ident) LEFT JOIN Prelude_Address AS t1 ON (t1._parent_type='S' AND t1._message_ident=top_table._ident
AND t1._parent0_index = 0 AND t1._index = 0) LEFT JOIN Prelude_Address AS t2 ON (t2._parent_type='T' AND t2._message_ident=top_table._ident
AND t2._parent0_index = 0 AND t2._index = 0) WHERE t0.time >= '2007-05-02 12:52:22' AND t0.time < '2007-05-02 13:52:22'
AND t1.address = '::ffff:192.168.33.183' AND t2.address = '::ffff:66.102.9.99' ORDER BY 1 DESC LIMIT 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..580.85 rows=1 width=20) (actual time=770.185..770.187 rows=1 loops=1)
-> Nested Loop (cost=0.00..580.85 rows=1 width=20) (actual time=770.182..770.182 rows=1 loops=1)
-> Nested Loop (cost=0.00..575.25 rows=1 width=50) (actual time=770.136..770.136 rows=1 loops=1)
-> Nested Loop (cost=0.00..569.64 rows=1 width=39) (actual time=48.901..769.622 rows=16 loops=1)
Join Filter: ("outer"._message_ident = ("inner"._ident)::numeric)
-> Index Scan Backward using prelude_createtime_index on prelude_createtime t0 (cost=0.00..5.79 rows=1 width=31) (actual time=0.057..0.113 rows=16 loops=1)
Index Cond: (((_parent_type)::text = 'A'::text) AND ("time" >= '2007-05-02 12:52:22'::timestamp without time zone) AND ("time" < '2007-05-02 13:52:22'::timestamp without time zone))
-> Seq Scan on prelude_alert top_table (cost=0.00..303.54 rows=17354 width=8) (actual time=0.012..23.403 rows=17393 loops=16)
-> Index Scan using prelude_address_pkey on prelude_address t2 (cost=0.00..5.59 rows=1 width=11) (actual time=0.027..0.027 rows=0 loops=16)
Index Cond: (((t2._parent_type)::text = 'T'::text) AND (t2._message_ident = "outer"._message_ident) AND (t2._parent0_index = 0) AND (t2._index = 0))
Filter: ((address)::text = '::ffff:66.102.9.99'::text)
-> Index Scan using prelude_address_pkey on prelude_address t1 (cost=0.00..5.59 rows=1 width=11) (actual time=0.042..0.042 rows=1 loops=1)
Index Cond: (((t1._parent_type)::text = 'S'::text) AND ("outer"._message_ident = t1._message_ident) AND (t1._parent0_index = 0) AND (t1._index = 0))
Filter: ((address)::text = '::ffff:192.168.33.183'::text)
Total runtime: 770.327 ms
(15 rows)
This query shows a sequential scan on the db:
-> Seq Scan on prelude_alert top_table (cost=0.00..303.54 rows=17354 width=8) (actual time=0.012..23.403 rows=17393 loops=16)
