Views, indexes and date stamps

From: Todd Lewis <lewis-todd(at)sbcglobal(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Views, indexes and date stamps
Date: 2005-09-16 06:38:48
Message-ID: 432A6878.7020805@sbcglobal.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I've recently broken down one of my larger tables into 2 tables for
performance reasons. One table contains current information and the
second table contains older historical information. I've created a view
that unions the two tables together. I created the view with a Select *
from table_1 union select * from table_2. I have the same columns
indexed in each table. Does a query using the view take advantage of the
indexes on a table? I'm currently using 7.4.7.

Also one of the queries I run repeatedly uses one indexed column and a
date stamp column. I retrieve the max date stamp for a particular type
of transaction so that I can retrieve the items last activity. Is there
any benefit from indexing a date stamp column? These tables combined
have about 13 million records. It grows about 1 million records a month.
Will I get better performance with a second view that only contains the
columns that this query needs?

Browse pgsql-novice by date

  From Date Subject
Next Message Greg T. 2005-09-16 13:15:10 Very simple cgi perl connect & select script
Previous Message Peter Cook 2005-09-16 04:54:00 Application access to postgreSQL