Question about query optimization - Mailing list pgsql-general

From Matthias.Pitzl@izb.de
Subject Question about query optimization
Date
Msg-id 11EC9A592C31034C88965C87AF18C2A70CFCEA@m0000s61
Whole thread Raw
Responses Re: Question about query optimization
List 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

pgsql-general by date:

Previous
From: Alban Hertroys
Date:
Subject: Re: SQL subquery question
Next
From: Rick Schumeyer
Date:
Subject: Re: SQL subquery question