Re: select off of a view going slowly - Mailing list pgsql-general

Hello all,

I've finished upgrading to 7.1 and I'm still having trouble getting this view
to run respectably.

Does a view run its query every time I select from the view?  I had been
assuming that the view recreates itself when I modify the table to which the
view is attached, but Tom mentioned that adding an index on tstamp (which i
did) might help, which would imply that every time I select from the view it
is querying the original table (since tstamp lives there but not in the
view).  I did add the index (and one on status) with no effects.

It's still taking wayyyy long to run the select against this view (see stats
below).  I've included some select queries and their stats, a \d of the
error_log view, a \d of the log table from which it is derived, and an
EXPLAIN on the typical-case query.

I'm not real familiar with views so maybe I am not using them in the proper
way.Any pointers at all would be greatly appreciated, as this seems to be the
last hurdle I need to get over before I have a usable application.  Thank you
very much!  (Also, I promise that once I get this application working and can
get my boss off my back, I'll take some time and learn about views, and
relational databases in general, more deeply.   Then maybe I can help out
around here some more! =)

Thanks,
Fran

monitoring=# select * from error_log;
 count | site_id | host_id
-------+---------+---------
     8 |      34 |      88
     8 |      34 |     110
(2 rows)

The query statistics:
2001-05-07 16:31:57 DEBUG:  query: select * from error_log;
2001-05-07 16:31:57 DEBUG:  ProcessQuery
2001-05-07 16:32:02 DEBUG:  CommitTransactionCommand
QUERY STATISTICS
! system usage stats:
!       4.428527 elapsed 3.720000 user 0.710000 system sec

monitoring=# select * from error_log where site_id=34 and host_id=88;
 count | site_id | host_id
-------+---------+---------
     8 |      34 |      88
(1 row)

2001-05-07 16:32:46 DEBUG:  query: select * from error_log where site_id=34
and host_id=88;
2001-05-07 16:32:46 DEBUG:  ProcessQuery
2001-05-07 16:32:48 DEBUG:  CommitTransactionCommand
QUERY STATISTICS
! system usage stats:
!       2.152403 elapsed 1.530000 user 0.620000 system sec

monitoring=# \d error_log
        View "error_log"
 Attribute |  Type   | Modifier
-----------+---------+----------
 count     | integer |
 site_id   | bigint  |
 host_id   | bigint  |
View definition: SELECT count(*) AS count, log.site_id, log.host_id FROM log
WHE
RE (((log.status = 'CRIT'::"varchar") OR (log.status = 'EMERG'::"varchar"))
AND
(log.tstamp > (now() - '1 day'::"interval"))) GROUP BY log.site_id,
log.host_id;

monitoring=# \d log
                    Table "log"
  Attribute   |           Type           | Modifier
--------------+--------------------------+----------
 site_id      | bigint                   |
 host_id      | bigint                   |
 fqdn         | character varying        | not null
 site         | character varying        | not null
 region       | character varying        | not null
 hostname     | character varying        | not null
 product      | character varying        | not null
 class        | character varying        | not null
 subclass     | character varying        | not null
 status       | character varying        | not null
 msg          | character varying        | not null
 remote_stamp | timestamp with time zone | not null
 tstamp       | timestamp with time zone | not null
Indices: log_hostid_index,
         log_siteid_hostid_index,
         log_siteid_index,
         log_status_index,
         log_tstamp_index

monitoring=# explain select * from error_log where site_id=34 and host_id=88;

NOTICE:  QUERY PLAN:

Subquery Scan error_log  (cost=33145.20..33145.21 rows=1 width=16)
  ->  Aggregate  (cost=33145.20..33145.21 rows=1 width=16)
        ->  Group  (cost=33145.20..33145.21 rows=1 width=16)
              ->  Sort  (cost=33145.20..33145.20 rows=1 width=16)
                    ->  Seq Scan on log  (cost=0.00..33145.19 rows=1
width=16)

EXPLAIN
monitoring=#