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

Fran Fabrizio <ffabrizio@exchange.webmd.net> writes:
> Creating an index on a view would seem counterintuitive, but I tried
> anyway.

The system should not have let you do this (and 7.1 doesn't).

> View definition: SELECT count(*) AS count, log.site_id, log.host_id FROM
> log WHERE (((log.status = 'CRIT'::"varchar") OR (log.status =
> 'EMERG'::"varchar")) AND
> (log.tstamp > (now() - '1 00:00'::"interval"))) GROUP BY log.site_id,
> log.host_id;

Hm.  An index on log.tstamp might help.

BTW, I would recommend an update to 7.1 before you go much further.
Older releases have lots of problems with grouped views.

            regards, tom lane