rsyslog=> EXPLAIN ANALYZE SELECT * FROM systemevents WHERE fromhost = 'XXXX' ORDER BY id desc LIMIT 20 ;
                                                                              QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..26274.76 rows=20 width=957) (actual time=182283.090..238288.456 rows=20 loops=1)
   ->  Index Scan Backward using systemevents_pkey on systemevents  (cost=0.00..85295739.77 rows=64926 width=957) (actual time=182283.086..238288.407 rows=20 loops=1)
         Filter: ((fromhost)::text = 'XXXX'::text)
 Total runtime: 238294.565 ms
(4 rows)

rsyslog=> SELECT count(*) FROM  systemevents WHERE fromhost = 'XXXX';
 count
-------
  2750
(1 row)

rsyslog=>

# cat /evnt_log/postgresDB/postgresql.conf | grep ^[a-zA-Z0-9]
listen_addresses = '*'
max_connections = 10                    # (change requires restart)
shared_buffers = 1536MB                 # min 128kB
work_mem = 256MB                        # min 64kB
maintenance_work_mem = 512MB            # min 1MB
wal_buffers = 12MB                      # min 32kB
checkpoint_segments = 16                # in logfile segments, min 1, 16MB each
checkpoint_completion_target = 0.8      # checkpoint target duration, 0.0 - 1.0
effective_cache_size = 4GB
logging_collector = on                  # Enable capturing of stderr and csvlog
log_directory = 'pg_log'                # directory where log files are written,
log_filename = 'postgresql-%a.log'      # log file name pattern,
log_truncate_on_rotation = on           # If on, an existing log file of the
log_rotation_age = 1d                   # Automatic rotation of logfiles will
log_rotation_size = 0                   # Automatic rotation of logfiles will
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'                     # locale for system error message
lc_monetary = 'en_US.UTF-8'                     # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'                      # locale for number formatting
lc_time = 'en_US.UTF-8'                         # locale for time formatting
default_text_search_config = 'pg_catalog.english'

rsyslog=> EXPLAIN ANALYZE SELECT * FROM systemevents WHERE fromhost = 'XXXX' ORDER BY id desc LIMIT 20 ;
                                                                              QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------
---------
 Limit  (cost=0.00..26158.94 rows=20 width=957) (actual time=129134.836..131515.164 rows=20 loops=1)
   ->  Index Scan Backward using systemevents_pkey on systemevents  (cost=0.00..94773822.13 rows=72460 width=957) (actual time=129134.833..131515.142 rows=20
loops=1)
         Filter: ((fromhost)::text = 'XXXX'::text)
 Total runtime: 131515.293 ms
(4 rows)

rsyslog=>

rsyslog=> EXPLAIN SELECT * FROM systemevents WHERE fromhost = 'XXXX' ORDER BY id desc LIMIT 10;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Limit  (cost=255199.89..255199.92 rows=10 width=957)
   ->  Sort  (cost=255199.89..255376.89 rows=70797 width=957)
         Sort Key: id
         ->  Bitmap Heap Scan on systemevents  (cost=1680.96..253670.00 rows=70797 width=957)
               Recheck Cond: ((fromhost)::text = 'XXXX'::text)
               ->  Bitmap Index Scan on nomequipement  (cost=0.00..1663.26 rows=70797 width=0)
                     Index Cond: ((fromhost)::text = 'XXXX'::text)

rsyslog=> ALTER TABLE systemevents ADD PRIMARY KEY (id);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "systemevents_pkey" for table "systemevents"
ALTER TABLE

rsyslog=> EXPLAIN SELECT * FROM systemevents WHERE fromhost = 'XXXX' ORDER BY id desc LIMIT 10;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..13071.17 rows=10 width=957)
   ->  Index Scan Backward using systemevents_pkey on systemevents  (cost=0.00..93162177.33 rows=71273 width=957)
         Filter: ((fromhost)::text = 'XXXX'::text)
(3 rows)

rsyslog=>

rsyslog=> analyze systemevents;
ANALYZE
rsyslog=> EXPLAIN SELECT * FROM systemevents WHERE fromhost = 'XXXX' ORDER BY id desc LIMIT 10;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..13199.83 rows=10 width=957)
   ->  Index Scan Backward using systemevents_pkey on systemevents  (cost=0.00..93450812.85 rows=70797 width=957)
         Filter: ((fromhost)::text = 'XXXX'::text)
(3 rows)

rsyslog=>

rsyslog=> select
rsyslog->     t.relname as table_name,
rsyslog->     i.relname as index_name,
rsyslog->     a.attname as column_name
rsyslog-> from
rsyslog->     pg_class t,
rsyslog->     pg_class i,
rsyslog->     pg_index ix,
rsyslog->     pg_attribute a
rsyslog-> where
rsyslog->     t.oid = ix.indrelid
rsyslog->     and i.oid = ix.indexrelid
rsyslog->     and a.attrelid = t.oid
rsyslog->     and a.attnum = ANY(ix.indkey)
rsyslog->     and t.relkind = 'r'
rsyslog->     and t.relname like 'systemevents'
rsyslog-> order by
rsyslog->     i.relname;
  table_name  |    index_name     |    column_name
--------------+-------------------+--------------------
 systemevents | nomequipement     | fromhost
 systemevents | systemevents_pkey | id
 systemevents | timereporting     | devicereportedtime

SELECT * FROM systemevents WHERE ORDER BY id desc LIMIT 25;

SELECT * FROM systemevents WHERE fromhost like 'XXXX%' ORDER BY id desc LIMIT 25;

SELECT * FROM systemevents WHERE fromhost = 'XXXX' AND devicereportedtime <'XXXX-XX-XX'  AND devicereportedtime >'XXXX-XX-XX' ORDER BY id desc LIMIT 25;

SELECT * FROM systemevents WHERE fromhost = 'XXXX' AND facility <> 10 ORDER BY id desc LIMIT 25;

CREATE INDEX CONCURRENTLY NomEquipement ON systemevents(fromhost varchar_pattern_ops);

SELECT * FROM systemevents WHERE id = 50000000;

SELECT * FROM systemevents WHERE receivedat < '2014-03-14' AND receivedat > '2014-02-14' LIMIT 10;