Here are some tips for working with postgresql databases
1. How to see the statistics of the active queries at the real time?
You should query table pg_stat_activities. There are some other tables usefull as well : pg_stat_database & pg_stats. Some logging options should be switched on as well, or you will not see full information. This is quite usefull, as it is easy to incorporate into php backend.
2. How to make vacuuming work faster?
I have got real problems with starting vacuuming database. It took hours and did not completed. This little tip helped me a lot: When vacuum fails, you have to reindex your tables, as some indexes in postgresql can get corrupted. Just reindex tables one by one, and then run vacuuming on them.
3. How to make selects with date operands work faster?
Typically, selects that use date operations use sequential lookup, and does not use index’es. You have to wrap it into another selects for indexes to work, for example:
SELECT ID FROM NEWS WHERE DATE_AR < (now()-’30 days’::interval) => SELECT ID FROM NEWS WHERE DATE_AR < SELECT (now()-’30 days’::interval)
4. Why count is so slow ?
Count in most of operations takes at least the same as regular scan. Postgresql often uses sequential scan for counting records. That is quite different from mysql. Thus one should simplify the count expressions or cache them where possible. Additionally, you are better of using
SELECT id from tab ORDER by ID DESC LIMIT 1
Than :
SELECT MAX(id) from tab;
For the same reason.
