Re: [PERFORM] BASH script for collecting analyze-related info

2013-09-30 Thread Ken Tanzer
Thanks for the suggestions, help and feedback. New version attached. 3. When extracting table names you are missing: >a. Index Scan Backward >b. Bitmap Heap Scan > 4. When extracting index names, you're missing Index Only Scans and Index > Scan Backwards. If someone can send me analyze

Re: [PERFORM] pg_statio_all_tables columns

2013-09-30 Thread Xenofon Papadopoulos
I do have a performance problem, and it is due to I/O bottleneck. We don't have pg_stat_statements installed, we will check it out. Thanks On Mon, Sep 30, 2013 at 8:44 PM, Jeff Janes wrote: > On Mon, Sep 30, 2013 at 5:45 AM, Xenofon Papadopoulos wrote: > >> I am trying to understand the heap_b

Re: [PERFORM] pg_statio_all_tables columns

2013-09-30 Thread Jeff Janes
On Mon, Sep 30, 2013 at 5:45 AM, Xenofon Papadopoulos wrote: > I am trying to understand the heap_blks_read and heap_blks_hit of > pg_statio_all_tables in 9.2 > Do the numbers refer only to SELECT, or they take INSERT into account? > They take insert (and update, and delete) into account. > Wou

Re: [PERFORM] pg_statio_all_tables columns

2013-09-30 Thread Giuseppe Broccolo
Il 30/09/2013 16:41, Xenofon Papadopoulos ha scritto: Should this ratio stay low even in the case of a write-heavy table? Yes, in my opinion. Before data manipolation, database pages are moved on the shared buffer. heap_blks_read and heap_blks_hit are involved in those operations, not directl

Re: [PERFORM] pg_statio_all_tables columns

2013-09-30 Thread Xenofon Papadopoulos
Hello Giuseppe, do you actually mean I have a *poorly-cached *database? Should this ratio stay low even in the case of a write-heavy table? On Mon, Sep 30, 2013 at 5:40 PM, Giuseppe Broccolo < giuseppe.brocc...@2ndquadrant.it> wrote: > Hi Xenofon, > > Il 30/09/2013 14:45, Xenofon Papadopoulos h

Re: [PERFORM] pg_statio_all_tables columns

2013-09-30 Thread Giuseppe Broccolo
Hi Xenofon, Il 30/09/2013 14:45, Xenofon Papadopoulos ha scritto: I am trying to understand the heap_blks_read and heap_blks_hit of pg_statio_all_tables in 9.2 Do the numbers refer only to SELECT, or they take INSERT into account? heap_blks_read and heap_blks_hit refer to number of blocks rea

[PERFORM] pg_statio_all_tables columns

2013-09-30 Thread Xenofon Papadopoulos
I am trying to understand the heap_blks_read and heap_blks_hit of pg_statio_all_tables in 9.2 Do the numbers refer only to SELECT, or they take INSERT into account? Would a heap_blks_read / ( heap_blks_read + heap_blks_hit ) ration of over 55% combined with a heap_blks_read value of over 50M indica

Re: [PERFORM] BASH script for collecting analyze-related info

2013-09-30 Thread hubert depesz lubaczewski
On nie, wrz 29, 2013 at 02:09:07 -0700, Ken Tanzer wrote: > p.s., This script runs fine on my computer (Ubuntu 13.04), but on a Fedora > 11 machine it dies with > > pg_analyze_info.sh: line 18: unexpected EOF while looking for matching `)' > pg_analyze_info.sh: line 57: syntax error: unexpected e