Question about query optimization

From: Matthias(dot)Pitzl(at)izb(dot)de
To: pgsql-general(at)postgresql(dot)org
Subject: Question about query optimization
Date: 2006-11-15 13:44:46
Message-ID: 11EC9A592C31034C88965C87AF18C2A70CFCEA@m0000s61
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello!

I have to tables, component with unchanging component data and a
component_history table containing the history of some other values that can
change in time.
The table component_history holds a foreign key to the component_id column
in the component table. The table component_history has a primary key over
the columns component_id and history_timestamp.

Now, we often need to get the situation at a given time out of these tables
and at moment we use following query:

--------------------------------------------------------
SELECT * FROM component JOIN component_history AS c_h USING(component_id)
WHERE history_timestamp = (
SELECT history_timestamp FROM component_history
WHERE c_h.component_id = component_history.component_id AND
history_timestamp <= '2006-10-01'
ORDER BY history_timestamp DESC LIMIT 1
)
--------------------------------------------------------

The query gets executed like this:
--------------------------------------------------------
Hash Join (cost=32540.55..32665.07 rows=32 width=78) (actual
time=118.958..136.416 rows=4160 loops=1)
Hash Cond: ("outer".component_id = "inner".component_id)
-> Seq Scan on component (cost=0.00..71.31 rows=4231 width=19) (actual
time=0.004..3.685 rows=4231 loops=1)
-> Hash (cost=32540.47..32540.47 rows=32 width=63) (actual
time=118.165..118.165 rows=0 loops=1)
-> Seq Scan on component_history c_h (cost=0.00..32540.47 rows=32
width=63) (actual time=0.092..111.985 rows=4160 loops=1)
Filter: (history_timestamp = (subplan))
SubPlan
-> Limit (cost=6.27..6.28 rows=1 width=8) (actual
time=0.016..0.017 rows=1 loops=5165)
-> Sort (cost=6.27..6.28 rows=2 width=8) (actual
time=0.014..0.014 rows=1 loops=5165)
Sort Key: history_timestamp
-> Index Scan using component_history_pkey on
component_history (cost=0.00..6.26 rows=2 width=8) (actual
time=0.007..0.009 rows=1 loops=5165)
Index Cond: (($0 = component_id) AND
(history_timestamp <= '01.10.2006 00:00:00'::timestamp without time zone))
Total runtime: 139.044 ms
--------------------------------------------------------

Is there any other, and more performat way, to get the last history entry
for a given date than this query?
Queries of this kind are often used in our application and getting a more
performant solution would speed up things a lot.

Thank's for your suggestions!

Greetings,
Matthias

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alban Hertroys 2006-11-15 13:47:00 Re: SQL subquery question
Previous Message Alvaro Herrera 2006-11-15 13:30:56 Re: autovac hung/blocked