Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a Postgres table Prices with the columns

  • price (Decimal)
  • product_id (Int)

Prices get updated regularly and I keep old prices in the table. For a given product, the last price in the table is the current price.

What is the most efficient way to get the last price for a specific product:

  • Index product_id and query for the last record
  • Add a third column active (Boolean) to mark the latest price and create a composite index (product_id and active)
  • Or something else?
share|improve this question
3  
Using a partial index with the condition where active would probably help even more to retrieve the latest product. –  a_horse_with_no_name Feb 16 at 7:59
    
Do you have a date/datetime/timestamp column? –  ypercube Feb 16 at 14:42
    
Yes, created_at and updated_at –  Mike81 Feb 16 at 22:21
add comment

2 Answers 2

up vote 4 down vote accepted

You'll need an index on product_id regardless of solution.

Provided you have an index on the updated_at column, and all you need is to fetch "a specific product" as you stated, then I would do:

select *
from Prices
where product_id = ?
order by updated_at desc 
limit 1

But if I did not get the results I wanted or if I needed to get the current price for many products, then I would try the option of adding a active column, and setting it to N for all prices other than the new one when doing updates of the prices and then I would create a partial index where active as suggested by a_horse_with_no_name. I would go there only if I needed to as it adds a layer of complexity of updating previous price rows to not be active, etc.

share|improve this answer
add comment

Without knowledge of the reste of your database, you can afford a bit of non normal form to speed up products price fetching (assuming there is one price for each item).

Just create a new column named last_priceof type price in your product table and create a trigger AFTER INSERT ON EACH ROWon your price table. Every time a new price is created, it updates the related product with the latest price. This way, everytime you fetch a product, you also fetch its last price.

share|improve this answer
    
This is not 'non formal' but a form of denormalization. Sometimes this is the only viable solution, but when one pushes it (I mean denormalizing by triggers) too far, it can be a maintenance and bugtracking nightmare. –  dezso Feb 26 at 15:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.