edib

-- show running queries (pre 9.2)
SELECT procpid, age(query_start, clock_timestamp()), usename, current_query 
FROM pg_stat_activity 
WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

-- show running queries (9.2)
SELECT pid, age(query_start, clock_timestamp()), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 

edib

-- Order by one indexed column (FAST)
newsdesk_production=# explain analyze select * from pressreleases order by published_at DESC limit 100;
                                                                                   QUERY PLAN                                                                                    
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..249.91 rows=100 width=1207) (actual time=26.070..716.453 rows=100 loops=1)
   ->  Index Scan Backward using pressreleases_published_at_index on pressreleases  (cost=0.00..964766.62 rows=386042 width=1207) (actual time=26.067..716.343 rows=100 loops=1)
 Total runtime: 716.709 ms
(3 rows)

- Order by two separately indexed columns (SLOW)

edib

To use the most modern version of Postgres software we need to add postgresql repository. Edit /etc/apt/sources.list or create /etc/apt/sources.list.d/pgdg.list and add there a line: deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main Then import the repository signing key, and update the package lists:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update

Install a new version of PostgreSQL server.

Once the Debian upgrade finished, I used dpkg-query -l postgresql* to check which versions of postgres I have installed.