The plot Function

The plot() function is your gateway to plotting result sets with plotpg.

plot(text, text DEFAULT '')

The first parameter is an sql query -- its result set will be plotted.

The second parameter is an optional string of arbitrary gnuplot commands.

### Sample Query

=# select plot('select rating, raises from attitude order by rating asc', 
               'set title "rating vs raises"; set style data lines;')


Please refer to the gnuplot documentation or an introductory monograph on gnuplot for more information on gnuplot commands that can be supplied as the second parameter.

Plotting Conventions

The plot function is intended to be used as a general exploratory tool without too much thought. To this end, it will apply a plot style to the result set based on some characteristics of the result set.

plot() uses simple conventions to determine the type of plot to display. It considers the number of records in the result set and the types of the first two columns.

# of records	first column type	second column type	Plot Style
1	ordinal	ordinal	Stacked Bar
> 1	timestamp(tz)	ordinal	Timeseries
> 1	text	ordinal	Histogram / Frequency
> 1	ordinal	ordinal	Scatter Plot

To create plots that do not fit these constraints, please see Arbitrary Plots.

Let's consider some example result sets and their corresponding plots taken from the pgantenna project.

Bar

pgantenna=# SELECT (total_memory - 
pgantenna-#            (free_memory + disk_cache + shared_buffers)) 
pgantenna-#              / total_memory::numeric as application_memory,
pgantenna-#         free_memory / total_memory::numeric as free_memory,
pgantenna-#         disk_cache / total_memory::numeric as disk_cache,
pgantenna-#         shared_buffers / total_memory::numeric as shared_buffers
pgantenna-#     FROM (SELECT pages * page_size as total_memory, 
pgantenna(#                    pages_available * page_size as free_memory, 
pgantenna(#                    page_cache * 1024 as disk_cache,
pgantenna(#                    gucs.bufs as shared_buffers
pgantenna(#               FROM stat_systems, 
pgantenna(#                    ( SELECT shared_buffers * blk_size as bufs
pgantenna(#                        FROM restart_gucs 
pgantenna(#                          ORDER BY created_at DESC LIMIT 1
pgantenna(#                    ) as gucs
pgantenna(#               ORDER BY measured_at DESC
pgantenna(#               LIMIT 1
pgantenna(#         ) as foo
pgantenna-# ;


 application_memory | free_memory | disk_cache  | shared_buffers     
--------------------+-------------+-------------+----------------
 0.0931620762741420 | 0.841066954 | 0.050084325 | 0.015688984723
(1 row)

Note the single row in the result set. When this query is passed to plot():

It turns out RAM is pretty cheap at Fry's electronics.

Scatter Plot

pgantenna_development=# select distinct on (table_id) relpages, reltuples 
pgantenna_development-#   from stat_class
pgantenna_development-#     where reltuples > 0
pgantenna_development-#   limit 5;
 relpages | reltuples 
----------+-----------
        4 |       303
        4 |       584
        2 |       124
        1 |        54
        1 |        51
(5 rows)

Note that the first and second columns are ordinal types (int, numeric, float...). A line could be plotted by passing 'set style data lines;' as a second parameter to plot().

Time Series

pgantenna=# select measured_at as time, 
             (current_tx_id - 
               coalesce(lag(current_tx_id, 1) over w, current_tx_id))
                 / extract( epoch from  
                   (measured_at - lag(measured_at, 1) over w))
                     as "transactions/sec"
             from heartbeats as h 
               where measured_at > now() - interval '4 hours'
                 window w as (order by measured_at) 
             order by measured_at asc
             limit 10;

             time              |    transactions/sec    
-------------------------------+------------------------
 2014-10-13 23:18:44.647795-07 |     4.5812443854858210
 2014-10-13 23:19:16.652792-07 | 0.12498048351637089671
 2014-10-13 23:19:48.660816-07 | 0.28117949424181886392
 2014-10-13 23:20:20.668504-07 | 0.12496997596327482322
 2014-10-13 23:20:52.676853-07 | 0.21869294164469401405
 2014-10-13 23:21:24.684783-07 | 0.12496903111197756306

Note that the first column is a timestamptz and the second column is numeric. This results in the x-axis formatted as time. When this query is passed to plot():

Histogram

select unnest(com.vals) as pg_clients, 
       unnest(com.freqs)::numeric as connections
  from (select 
          string_to_array(regexp_replace(most_common_vals, 
          ''[^a-z\/,]'', '''', ''g''), '','') as vals,       
          string_to_array(regexp_replace(most_common_freqs, 
          ''[^0-9\.\/,]'', '''', ''g''), '','') as freqs
        from stat_column limit 1) as com;');

    pg_clients       | connections 
---------------------+-------------
 bin/rails           |     0.62991
 /usr/local/bin/rake |    0.337435
 psql                |   0.0321817
(3 rows)