Search Postgresql Archives

Re: speeding up big query lookup

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On fös, 2006-08-25 at 18:34 -0400, Silvela, Jaime (Exchange) wrote:
> This is a question on speeding up some type of queries.
> 
> I have a very big table that catalogs measurements of some objects over
> time. Measurements can be of several (~10) types. It keeps the
> observation date in a field, and indicates the type of measurement in
> another field.
> 
> I often need to get the latest measurement of type A for object X.
> The table is indexed by object_id.

one popular way is to create a composite index:
CREATE INDEX object_val_id_type_date
       ON object_val(object_id,
                     object_val_type_id,
                     observation_date);

then you could

SELECT * FROM object_val
         WHERE object_id=?
               AND object_val_type_id=?
         ORDER BY observation_date DESC
         LIMIT 1;


Hope this helps
gnari





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux