Re: Memory usage - indexes - Mailing list pgsql-performance

On 24 September 2010 00:12, Mark Kirkwood <mark.kirkwood@catalyst.net.nz> wrote:
> All good questions! Before (or maybe as well as) looking at index sizes vs
> memory I'd check to see if any of your commonly run queries have suddenly
> started to use different plans due to data growth, e.g:
>
> - index scan to seq scan (perhaps because effective_cache_size is too small
> now)
> - hash agg to sort (work_mem too small now)

Would be trivial if we had a handful of different queries and knew the
plans by heart ... but our setup is slightly more complex than that.
I would have to log the plans, wouldn't I?  How would you go about it?
 I was having some thoughts to make up some script to scan through the
postgres log, extract some stats on the queries run, and even do some
explains and store query plans.

We've started to chase down on seq scans (causing us to create even
more indexes and eating up more memory...).  I have set up a simple
system for archiving stats from pg_stat_user_tables now, like this:

insert into tmp_pg_stat_user_tables select *,now() as snapshot from
pg_stat_user_tables ;

NBET=> \d tmp_delta_pg_stat_user_tables
       View "public.tmp_delta_pg_stat_user_tables"
      Column      |           Type           | Modifiers
------------------+--------------------------+-----------
 duration         | interval                 |
 relname          | name                     |
 seq_scan         | bigint                   |
 seq_tup_read     | bigint                   |
 idx_scan         | bigint                   |
 idx_tup_fetch    | bigint                   |
 n_tup_ins        | bigint                   |
 n_tup_upd        | bigint                   |
 n_tup_del        | bigint                   |
 n_tup_hot_upd    | bigint                   |
 n_live_tup       | bigint                   |
 n_dead_tup       | bigint                   |
 last_vacuum      | timestamp with time zone |
 last_autovacuum  | timestamp with time zone |
 last_analyze     | timestamp with time zone |
 last_autoanalyze | timestamp with time zone |
View definition:
 SELECT now() - b.snapshot AS duration, a.relname, a.seq_scan -
b.seq_scan AS seq_scan, a.seq_tup_read - b.seq_tup_read AS
seq_tup_read, a.idx_scan - b.idx_scan AS idx_scan, a.idx_tup_fetch -
b.idx_tup_fetch AS idx_tup_fetch, a.n_tup_ins - b.n_tup_ins AS
n_tup_ins, a.n_tup_upd - b.n_tup_upd AS n_tup_upd, a.n_tup_del -
b.n_tup_del AS n_tup_del, a.n_tup_hot_upd - b.n_tup_hot_upd AS
n_tup_hot_upd, a.n_live_tup, a.n_dead_tup, a.last_vacuum,
a.last_autovacuum, a.last_analyze, a.last_autoanalyze
   FROM pg_stat_user_tables a, tmp_pg_stat_user_tables b
  WHERE b.snapshot = (( SELECT max(tmp_pg_stat_user_tables.snapshot) AS max
           FROM tmp_pg_stat_user_tables)) AND b.relname = a.relname;