Re: Question about query optimization

On 11/15/06, Matthias(dot)Pitzl(at)izb(dot)de <Matthias(dot)Pitzl(at)izb(dot)de> wrote:
>
> Is there any other, and more performat way, to get the last history entry
> for a given date than this query?

Create an (independent) index on history_timestamp column and use a min/max
in the subquery.

More specifically, your query should look like this:
SELECT *
FROM component
JOIN component_history AS c_h
USING(component_id)
WHERE history_timestamp = (SELECT max(history_timestamp)
FROM component_history
WHERE c_h.component_id =
component_history.component_id
)

Here's a session snippet for an example of how drastically that can reduce
the cost and the run-time:

postgres=# drop table t;
DROP TABLE

postgres=# create table t ( a int, b int );
CREATE TABLE

postgres=# insert into t select s, 99999-s from generate_series(0,99999) as
s;
INSERT 0 100000

postgres=# analyze t;
ANALYZE

postgres=# explain select count(*) from t o where a = (select max(a) from t
i wh
ere i.b = o.b );
QUERY PLAN
--------------------------------------------------------------------------
Aggregate (cost=179103292.25..179103292.26 rows=1 width=0)
-> Seq Scan on t o (cost=0.00..179103291.00 rows=500 width=0)
Filter: (a = (subplan))
SubPlan
-> Aggregate (cost=1791.01..1791.02 rows=1 width=4)
-> Seq Scan on t i (cost=0.00..1791.00 rows=1 width=4)
Filter: (b = $0)
(7 rows)
Time: 0.000 ms

postgres=# create index ind_t_a on t(a) ;
CREATE INDEX
Time: 719.000 ms

postgres=# create index ind_t_b on t(b);
CREATE INDEX
Time: 750.000 ms

postgres=# explain select count(*) from t o where a = (select max(a) from t
i wh
ere i.b = o.b );
QUERY PLAN

--------------------------------------------------------------------------------
-------
Aggregate (cost=806146.25..806146.26 rows=1 width=0)
-> Seq Scan on t o (cost=0.00..806145.00 rows=500 width=0)
Filter: (a = (subplan))
SubPlan
-> Aggregate (cost=8.03..8.04 rows=1 width=4)
-> Index Scan using ind_t_b on t i (cost=0.00..8.03rows=1 wi
dth=4)
Index Cond: (b = $0)
(7 rows)
Time: 15.000 ms

/* and now the execution times */

postgres=# drop index ind_t_a, ind_t_b;
DROP INDEX
Time: 0.000 ms

postgres=# select count(*) from t o where a = (select max(a) from t i where
i.b
= o.b );
Cancel request sent (had to cancel after 1 minute)
ERROR: canceling statement due to user request

postgres=# create index ind_t_a on t(a) ;
CREATE INDEX
Time: 687.000 ms

postgres=# create index ind_t_b on t(b);
CREATE INDEX
Time: 765.000 ms

postgres=# select count(*) from t o where a = (select max(a) from t i where
i.b
= o.b );
count
--------
100000
(1 row)
Time: 2704.000 ms

postgres=#

--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | yahoo }.com